Re: advanced data stripping

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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/
>

Relevant Pages

  • Re: Some records missing data after merge
    ... I have to say I don't work on a daily basis with data like this so it's quite possible that you have come up with a limitation in Excel that I'm not aware of. ... averaging 200 characters and spaces per cell and is in sentences in the cells. ... Some of the letters carry data that matches the Excel cell data, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Rotated Text Help Needed
    ... > the shaded "cell" boxes for the letters. ... the characters themselves were printed without a "white rectangle" ... Colour Laser" the I'm afraid I've got to assume that it doesn't work at all! ...
    (microsoft.public.vb.general.discussion)
  • Re: custom format syntax ques
    ... so that I don't need to have a separate adjacent cell telling the reader ... 30 characters, mostly all letters and spaces. ...
    (microsoft.public.excel.programming)
  • Re: custom format syntax ques
    ... I have started to like custom formatting and often use it for things like ... the cell contents is just a text string with a maximum of 30 ... characters, mostly all letters and spaces. ...
    (microsoft.public.excel.programming)
  • custom format syntax ques
    ... I have started to like custom formatting and often use it for things like ... the cell contents is just a text string with a maximum of 30 ... characters, mostly all letters and spaces. ...
    (microsoft.public.excel.programming)