Extracting a SubString

[Guest]

I have a set of data that has leading spaces before each substring, and i
want to extract one particular string that has somehow eluded me. I checked
out Michelle Valeriano's samples and that allowed me to figure out all but
one. So here is a couple of the data samples:

Cell A3: tall_3 = 0003.00 -21.00 0.00
Cell A4: tall_4 = 5240.00 26.54 1.50

If I wanted to extract just the "-21.00" from A3 or the "26.54" from A4, how
would I go about doing so using the SEARCH function?

TIA

 

[Guest]

Maybe:-

=TRIM(MID(TRIM(A1),17,7))

Mike

 

[Ron Rosenfeld]

I have a set of data that has leading spaces before each substring, and i
want to extract one particular string that has somehow eluded me. I checked
out Michelle Valeriano's samples and that allowed me to figure out all but
one. So here is a couple of the data samples:

Cell A3: tall_3 = 0003.00 -21.00 0.00
Cell A4: tall_4 = 5240.00 26.54 1.50

If I wanted to extract just the "-21.00" from A3 or the "26.54" from A4, how
would I go about doing so using the SEARCH function?

TIA

Assuming your format is as above, then the values to be extracted are between
the third and fourth spaces, you can use this:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))+1,
FIND(" ",A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))+1)-
FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))-1)


--ron

 

[Guest]

Thanks Ron - yours worked dynamically, whereas Mike's would be fine for data
that is the same number of characters everytime.

 

[Ron Rosenfeld]

Thanks Ron - yours worked dynamically, whereas Mike's would be fine for data
that is the same number of characters everytime.

Glad to help. Thanks for the feedback
--ron