Extract A String Between Two Characters

This is a discussion on Extract A String Between Two Characters within the Excel Questions forums, part of the Question Forums category; I'm stuck. I need a formula to extract data from between two characters. For Example, In A1 I have this: ...

1. Extract A String Between Two Characters

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

2. Re: Extract A String Between Two Characters

I don't want to scare you but here is the formula, if you insist on doing it in a single formula. This is a mega-formula that I built up from several smaller formulas. It will work for any lengths. It is very difficult to read and maintain.

=LEFT(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))),FIND("_",RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))))-1)

If you put your value in A1, you can build up to it using the following formulas in the cells indicated:

A2 =RIGHT(A1,LEN(A1)-FIND("_",A1))
A3 =RIGHT(A2,LEN(A2)-FIND("_",A2))
A4 =RIGHT(A3,LEN(A3)-FIND("_",A3))
A5 =LEFT(A4,FIND("_",A4)-1)

Note that each formula needs to do a find plus know the length of the result of the previous step. That's why the mega-formula is so big, because each formula replaces the previous formula twice, and you repeat the replacement process 3 times.

There might be a slightly simpler way to do this, if it comes to me I'll post again.

3. Re: Extract A String Between Two Characters

With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):
Code:
```
B1: =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,
LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))```
In the above example, the formula returns: ABCD

Does that help?

4. Re: Extract A String Between Two Characters

Hi and welcome to the Board
Have you given any thought to using TEXT to COLUMNS and using your "_" as the delimiter.

Regards
Michael M

5. Re: Extract A String Between Two Characters

Originally Posted by Ron Coderre
With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):
Oh crap, ignore mine!

6. Re: Extract A String Between Two Characters

Thanks Guys. Ron, yours does exactly what I want it to. Thanks

7. Re: Extract A String Between Two Characters

I've read a bunch of things here on line but can't seem to figure out my own equation. I'm also trying to extract data between two areas of an URL.

Here are some examples of the portions of URLS I'm working with:

CFID=323428940&CFTOKEN
CFID=28769009&CFTOKEN
CFID=8000597&CFTOKEN

I want to extract the numbers between "CFID=" and "&CFTOKEN" but as you can see they can change from 7-9 characters each.

Does anyone have any ideas?

Thanks!

8. Re: Extract A String Between Two Characters

Yours would be a little different.

You could try this:

PHP Code:
``` =MID(YourTextHere,6,LEN(YourTextHere)-13)  ```

9. Re: Extract A String Between Two Characters

You could just use =SUBSTITUTE(SUBSTITUTE(A1,"CFID=",""),"&CFTOKEN","")

10. Re: Extract A String Between Two Characters

I have a similar question.

blahblahtext moretext, Result1: 454654654, Result Number2: ABC, Result Number3: 445344

I have the above string. I don't care about the actual results, but rather the headings. I want what is between ", " and ":" in one cell, what's between the next ", " and ":" in the next cell., etc etc

Results should look like this:
Cell1: Results 1, Cell2: Result Number2, Cell3: Results Number 3

I tried modifying Ron's formula above but just confused myself.

Page 1 of 9 123 ... Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•