Mid,Find,Len

[Rachel]

Hi
A1= abcd_XXXXXyyyyyyy_AB
B1 = yyyyyyy
I am trying to extract the XXXXX portion of A1 with this formula....

C1=MID(A1,FIND("_",A1)+1,FIND("_",A1,FIND("_",A1)+1)-FIND("_",A1)-1-LEN(B1))

Which works fine for this example however if len(b1) changes then the result
changes in the same manner. What am I doing worng?
Thanks

 

[JulieD]

Hi Rachel

there might be an easier way but this seems to work for me

=MID(A1,SEARCH("_",A1)+1,LEN(A1)-(SEARCH("_",A1)+1)-(LEN(A1)-SEARCH("_",A1,SEARCH("_",A1)+1))-LEN(B1))

Cheers
JulieD

 

[Rachel]

Hey Julie
Thanks for the reply----Im still having the same problem----If len(b1) is
increased by 2 the formula result is truncated by 2 places and conversely if
len(b1) decreases then the formula result grows by that same amount.

Rachel

 

[JulieD]

Hi Rachel

but isn't this what you want - or doesn't the yyyyyy in B1 relate to the
yyyyyy in A1?
if the number of XXXXX are always constant there is another approach - let
me know?

Cheers
JulieD

 

[Dave R.]

Could you explain more clearly what you expect to happen when the length of
B1 changes?

 

[Rachel]

Yes your presumption is correct as far as the relation. However the string
length are not always the same. Another example
A2= abcd_TTTTTTTTTrrrrrrrr_AB
B2 = rrrrrrrr (8 characters)
C2 = TTTTTTTTT

HTH
Rachel

 

[JulieD]

Hi Rachel

when i test the formula i gave you in my original answer it works on the
example below ... does it work for you?

Cheers
JulieD

 

[hrlngrv - ExcelForums.com]

Rachel wrote..

. . . However the string length are not always the same. Anothe

A2= abcd_TTTTTTTTTrrrrrrrr_A
B2 = rrrrrrrr (8 characters
C2 = TTTTTTTT

..

The formul

=MID(A2,FIND("_",A2)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2)-1

returns the substring of A2 between underscores (excluding th
underscores). If the r..r string in B2 is the same length as the r..
substring in A2, then the formul

=MID(A2,FIND("_",A2)+1,FIND("_",A2,FIND("_",A2)+1)-FIND("_",A2)-
-LEN(B2)

returns the T..T substring from A2. However, if the r..r string in B
were the same as the r..r substring in A2 and A2 contained only th
two delimiting underscores, then it'd be a lot easier to us

=MID(LEFT(A2,FIND(B2&"_",A2)-1),FIND("_",A2)+1,1024