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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

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


"Rachel" wrote in message
...
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" wrote:

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" wrote in message
...
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

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



"Rachel" wrote in message
...
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

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" wrote:

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


"Rachel" wrote in message
...
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" wrote:

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" wrote in message
...
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

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

"Rachel" wrote in message
...
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" wrote:

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


"Rachel" wrote in message
...
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" wrote:

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" wrote in message
...
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

Rachel wrote...
. . . However the string length are not always the same.
Another
example

A2= abcd_TTTTTTTTTrrrrrrrr_AB
B2 = rrrrrrrr (8 characters)
C2 = TTTTTTTTT
...

The formula

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

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

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

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

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

Use your Bodum and give up cold coffee for good!