advanced data stripping

[rstevens5l]

I am currently working with a CNC program. I am trying to extract al
the X Y Z and B position information from it. the data is just lines o
code like so contained in the first column.


G01 Z-106. F1000.
X120.021 Y-144.343 F50.0
G03 X119.179 Y-142.257 I-0.421 J1.043

what i want to do is make 4 columns to the right of the program labele
X Y Z B. In those columns i want the numbers that are called out b
these letters. for instance the 3 lines of code above would output....

X Y Z
B
-106
120.021 -144.343
119.179 -142.257


If there is no value i would like to leave the cell blank.
If there is a X Y Z or B in the cell I want the number after i
outputted to the cell. the numbers only go to 3 decimal places.
imagine I would need one formula for each column, one to look for eac
letter. I am pretty good in excel but i have no idea where to begin.
copy of the file is on here..

Attachment filename: op10.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=43014

 

[Max]

Try this:

Assume your sample data below is in A2:A4

G01 Z-106. F1000.
X120.021 Y-144.343 F50.0
G03 X119.179 Y-142.257 I-0.421 J1.043

Put across in B11, the letters X, Y, Z
(There's no "B" in your sample data, btw)

Put in B2:

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

Format B2 as number to 3 d.p.

Copy B2 down to B4, then across to C4

This extracts the numerics for X and Y

Put in D2:

=IF(ISERROR(MID($A2,SEARCH(D$1,$A2)+1,5)),"",VALUE(MID($A2,SEARCH(D$1,$A2)+1
,5)))

Format D2 as number to 0 d.p.

Copy D2 down to D4

This extracts the numerics for Z

If the letters are not found in the string, "blanks" will be returned

 

[rstevens5l]

Thankyou for getting me started on this. i have never used any of thos
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 proble
with my formula is that the value statement is not working how i woul
like. what im trying to do is find the "y" and output from th
character next to "y" to the space after the number

 

[Max]

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]