ADVANCED data strippping
 

I am currently working with a CNC program. I am trying to extract all
the X Y Z and B position information from it. the data is just lines
of 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 labeled
X Y Z B. In those columns i want the numbers that are called out by
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 it
outputted to the cell. the numbers only go to 3 decimal places. I
imagine I would need one formula for each column, one to look for each
letter. I am pretty good in excel but i have no idea where to begin.

Attachment filename: op10.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=430138
---
Message posted from http://www.ExcelForum.com/

ADVANCED data strippping
 

The VBA InStr function in conjunction with the Split function is the way to
go.
To many if's for a worksheet formula for my taste.
If you have a X value but no Y value how do you know where the X value ends?
You can test for the end of the string, but then you also have to test for
Y, Z & B and blanks.
Not to mention all those comments.

Now if the code had spaces between the values and letters you could just do
a text to columns...

EditReplace X with X[space]
Y with Y[space]
Z with Z[space]
B with B[space]
Hummmm..?


--
John
johnf 202 at hotmail dot com


"rstevens5l " wrote in message
...
| I am currently working with a CNC program. I am trying to extract all
| the X Y Z and B position information from it. the data is just lines
| of 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 labeled
| X Y Z B. In those columns i want the numbers that are called out by
| 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 it
| outputted to the cell. the numbers only go to 3 decimal places. I
| imagine I would need one formula for each column, one to look for each
| letter. I am pretty good in excel but i have no idea where to begin.
|
| Attachment filename: op10.xls
| Download attachment:
http://www.excelforum.com/attachment.php?postid=430138
| ---
| Message posted from http://www.ExcelForum.com/
|