Extracting a SubString

B

Brian

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
 
R

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
Click to expand...

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
 
B

Brian

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

Ron Rosenfeld

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


Glad to help. Thanks for the feedback
--ron
 
Top