Excel formula to get string between 2 characters

Question

I'm trying to dynamically extract the string between 2 characters in Excel using a formula (no text to columns). The sample data is:

US - Blue Widgets - Net
UK - Green - Grass
UAE - Red - Apples

* Note that the data doesn't have fixed length

I tried using a formula, but I think I'm missing something because that also returns the string after the last -.

Formula:

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",1))+1,LEN(A2)),"_",REPT(" ",LEN(A2))),LEN(A2)))

what this returns is:

Blue Widgets - Net
Green - Grass
Red - Apples

here's what I'd like it to return:

Blue Widgets
Green
Red

Mitja Bezenšek · Accepted Answer · 2014-07-21 18:23:24Z

6

Try this:

=TRIM(MID(A2, 6, FIND("-",A2,6) - FIND("-",A2) - 2))

If hard coded 6 is not ok, you can replace it with FIND("-", A2) + 1.

edited Jul 21, 2014 at 18:23

answered Jul 21, 2014 at 18:14

Mitja Bezenšek

2,5331 gold badge16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xyzxyz · Accepted Answer · 2014-07-21 18:00:38Z

0

You can get help from the following link, its pretty useful,,

http://www.mrexcel.com/forum/excel-questions/444266-extract-string-between-two-characters.html

answered Jul 21, 2014 at 18:00

xyzxyz

31 silver badge5 bronze badges

Comments

Michael Benjamin · Accepted Answer · 2016-08-23 01:25:00Z

0

I used this and worked.

=IF(ISERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(MID(A3,FIND("|",SUBSTITUTE(A3,"-","|",1))+1,LEN(A3)),"-",REPT(" ",LEN(A3))),LEN(A3)))," ","")),"",SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(MID(A3,FIND("|",SUBSTITUTE(A3,"-","|",1))+1,LEN(A3)),"-",REPT(" ",LEN(A3))),LEN(A3)))," ",""))

you will get:

Blue Widgets
Green
Red

is you want to get the last part, I mean the string after the second "-" use this

=IF(ISERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(MID(A3,FIND("|",SUBSTITUTE(A3,"-","|",2))+1,LEN(A3)),"-",REPT(" ",LEN(A3))),LEN(A3)))," ","")),"",SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(MID(A3,FIND("|",SUBSTITUTE(A3,"-","|",2))+1,LEN(A3)),"-",REPT(" ",LEN(A3))),LEN(A3)))," ",""))

you will get:

Net
Grass
Apples

Best regads.

edited Aug 23, 2016 at 1:25

Michael Benjamin

376k110 gold badges616 silver badges737 bronze badges

answered Aug 23, 2016 at 1:02

Angel R. Bejarano

1

Collectives™ on Stack Overflow

Excel formula to get string between 2 characters

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related