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

Maybe:-

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

Mike

"Brian" wrote:

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

On Tue, 26 Jun 2007 12:24:00 -0700, Brian
wrote:

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

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

"Ron Rosenfeld" wrote:

=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

On Wed, 27 Jun 2007 11:42:06 -0700, Brian
wrote:

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