Extract A String Between Two Characters

[theaudioguy]

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.

 

[6StringJazzer]

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.

 

[Ron Coderre]

With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):

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?

 

[Michael M]

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

 

[6StringJazzer]

With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):

Oh crap, ignore mine!

 

[theaudioguy]

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

 

[lndsy129]

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!

 

[theaudioguy]

Yours would be a little different.

You could try this:

=MID(YourTextHere,6,LEN(YourTextHere)-13)

 

[BenMiller]

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

 

[Roscoe_11]

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.