advanced data stripping

  • Thread starter Thread starter rstevens5l
  • Start date Start date
R

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
 
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
Click to expand...

Put across in B1:D1, 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
 
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
 
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]
 
Back
Top