Re: advanced data stripping

From: Max (demechanik_at_yahoo.com)
Date: 02/04/04 

Date: Wed, 4 Feb 2004 22:31:06 +0800

You're on the right track ... Ok, now it's clearer and ...
there's also spaces as "markers" in the string to help us

With your letters X, Y, Z, B in B1:E1

and the data-strings in col A, row2 down

Put in B2:

=IF(ISERROR(MID($A2,SEARCH(B$1,$A2)+1,SEARCH("
",$A2,SEARCH(B$1,$A2))-SEARCH(B$1,$A2)-1)),"",VALUE(MID($A2,SEARCH(B$1,$A2)+
1,SEARCH(" ",$A2,SEARCH(B$1,$A2))-SEARCH(B$1,$A2)-1)))

Copy B2 across to E2, then down to last row of data in col A

Using the spaces as "markers", the number of characters to be returned
will now be given by this part of the formula:

SEARCH(" ",$A2,SEARCH(B$1,$A2))-SEARCH(B$1,$A2)-1

--------------------
For the specific "row251" example you mentioned in your response

Put in D251:

=IF(ISERROR(MID($A251,SEARCH(D$1,$A251)+1,SEARCH("
",$A251,SEARCH(D$1,$A251))-SEARCH(D$1,$A251)-1)),"",VALUE(MID($A251,SEARCH(D
$1,$A251)+1,SEARCH(" ",$A251,SEARCH(D$1,$A251))-SEARCH(D$1,$A251)-1)))

[It's the same formula as above] 

--
Rgds
Max
xl 97
----------------------------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------------------
"rstevens5l >" <<rstevens5l.113ue2@excelforum-nospam.com> wrote in message
news:rstevens5l.113ue2@excelforum-nospam.com...
> Thankyou for getting me started on this. i have never used any of those
> functions before.
>
> I only have a couple small problems so far.
>
> the numbers following the x y z or b in the program vary in length.
> the numbers may be in the form 1 , 12 , 123 , 123.4 , 123.45 , 123.456.
> so now im trying to output from the space after y until the next space.
> i came up with this formula which i thought would work...
>
> VALUE(MID($A251,SEARCH(D$1,$A251)+1,SEARCH(" ",$A251,SEARCH(D$1,$A251)
>
> cell A251:
>
> G00 X-96.361 Y-304. B150.0 M11
>
> cell D1:
> y
>
> problem is for this i get #value. from this i can tell the only problem
> with my formula is that the value statement is not working how i would
> like. what im trying to do is find the "y" and output from the
> character next to "y" to the space after the number.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>