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.

 

[István Hirsch]

Give this a try:

=MID(A2,FIND("weight",A2)+9,FIND(" ",A2,FIND("weight",A2)+9)-FIND("weight",A2)-9)

 

[Rick Rothstein]

Give this formula a try...

=LOOKUP(9E+99,--LEFT(MID(A2,SEARCH("kvp__value qa-pd-weight"">",A2)+25,99),ROW(INDEX(A:A,1):INDEX(A:A,16))))

 

[pityuse]

Hello,

I have read through the entire thread but don't think I've seen anything that covered this problem:

I need the strings between the second "=" and before the second "&"

<tbody>
[TD="align: left"]http://web1.parts-cat.com/default.aspx?14=1&32=16637&1230=9997&10=6A47A456188C420187D8EE6E3303296E018001&12=110[/TD]

[TD="align: left"]http://web1.parts-cat.com/default.aspx?14=1&32=193&1230=9997&10=6A47A456188C420187D8EE6E3303296E018001&12=110[/TD]

[TD="align: left"]http://web1.parts-cat.com/default.aspx?14=1&32=3119&1230=9997&10=6A47A456188C420187D8EE6E3303296E018001&12=110[/TD]

</tbody>

16637
193
3119
(length of this string - actually always figures if that makes it easier - can be from 1 to 6)

A normal text search would not work, as the text before and after the special characters can vary.
Thanks a lot for help
Andreas

 

[Rick Rothstein]

Give this formula a try...

=0+MID(LEFT(A1,FIND("&",A1,FIND("&",A1)+1)-1),FIND("=",A1,FIND("=",A1)+1)+1,99)

 

[Martvg]

Pityuse, try this:

With A1 containing the initial string.

=LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"=","~",2),"&","~",2),LEN(A1)-FIND("~",SUBSTITUTE(SUBSTITUTE(A1,"=","~",2),"&","~",2))),FIND("~",RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"=","~",2),"&","~",2),LEN(A1)-FIND("~",SUBSTITUTE(SUBSTITUTE(A1,"=","~",2),"&","~",2))))-1)

 

[pityuse]

Rick, I had a feeling that you would post the first reply and it works as it should and I am not one bit surprised it does. THANKS!!

 

[Peter_SSs]

I need the strings between the second "=" and before the second "&"

Andreas, you could also try

=MID(SUBSTITUTE(SUBSTITUTE(A1,"&","="),"=",REPT(" ",100)),300,100)+0

 

[pityuse]

Hello Peter, thanks for your solution as well. You helped me out with a function ("findcommons" -> common numbers across multiple cells with many different numbers within each cell; about 2 years ago) and that has been very useful for me!! In fact, I might ask for some finetuning of that function Rick also presented a solution for that problem back then. You even wrote a benchmark program to compare the various solutions! Great community here!
Andreas

 

[punkzzone]

Give this a try:

=MID(A2,FIND("weight",A2)+9,FIND(" ",A2,FIND("weight",A2)+9)-FIND("weight",A2)-9)

Thanks this work ...
if you don't mind how can you get number 9 in that formula ?

 

[István Hirsch]

Thanks this work ...
if you don't mind how can you get number 9 in that formula ?

You are welcome.

The formula searches for the location of "weight" in the string (in your example 384 characters from the start of the string) and 9 (characters) are added to get to the number you want to extract. Of course, this formula won't work if this "distance" is not 9 characters.