Extract text from text string

john taiariol

unread,

Feb 9, 2021, 9:44:18 AM2/9/21

to

Hello,
I would like to extract the X-coordinate from a string of NC programming code for a CNC machine. A typical line of code might look like this:

(M72)
G0G54X202.819Z54.5
#590=2(RECOVER_FACE TURN)
G1X167.751F#101M26
G0Z61.398
X163.682
G1 X 153.873 Z 62
X147.873
G0X162.375Z62.394

My preference would be a formula. I would like the adjacent cell to contain only the value for "X" ie. in the first line "G0G54X202.819Z54.5", the X-coord is 202.819. Please note sometimes the text may have a space after the X or after the coordinate. It is not consistent. I have tried "find" and "mid" with limited success. I was wondering if it's possible to analyze each character in the string after the "X" and verify if it's a number or a period. Thank you in advance for any help.

dpb

unread,

Feb 9, 2021, 11:01:10 AM2/9/21

to

That's a job for regular expressions. Alas, I'm not a guru in writing
them...

--

Claus Busch

unread,

Feb 9, 2021, 11:50:40 AM2/9/21

to

Hi John,

Am Tue, 9 Feb 2021 06:44:13 -0800 (PST) schrieb john taiariol:

> (M72)
> G0G54X202.819Z54.5
> #590=2(RECOVER_FACE TURN)
> G1X167.751F#101M26
> G0Z61.398
> X163.682
> G1 X 153.873 Z 62
> X147.873
> G0X162.375Z62.394
>
> My preference would be a formula. I would like the adjacent cell to contain only the value for "X" ie. in the first line "G0G54X202.819Z54.5", the X-coord is 202.819. Please note sometimes the text may have a space after the X or after the coordinate. It is not consistent. I have tried "find" and "mid" with limited success. I was wondering if it's possible to analyze each character in the string after the "X" and verify if it's a number or a period. Thank you in advance for any help.

try:
=IF(COUNT(FIND({"X","Z"},A1))=2,TRIM(MID(LEFT(A1,FIND("Z",A1)-1),FIND("X",LEFT(A1,FIND("Z",A1)-1))+1,99)),IF(COUNT(FIND({"X","F"},A1))=2,TRIM(MID(LEFT(A1,FIND("F",A1)-1),FIND("X",LEFT(A1,FIND("F",A1)-1))+1,99)),IF(COUNT(FIND({"F","Z"},A1))=0,TRIM(SUBSTITUTE(A1,"X",)),"")))

Regards
Claus B.
--
Windows10
Microsoft 365 for business

dpb

unread,

Feb 9, 2021, 5:09:40 PM2/9/21

to

On 2/9/2021 8:44 AM, john taiariol wrote:

With the presumption of always being F7.3 format, the following seems to
work

=VALUE(MID(SUBSTITUTE(A7," ",""),FIND("X",SUBSTITUTE(A7," ",""))+1,7))

It's more work for variable-width fields, unfortunately, because then
the last "7" for number of characters is also a variable.

Unfortunately, the Excel VALUE() function isn't smart-enough to just
convert from the starting point until it runs out of digits as will C
|fscanf|

G0G54X202.819Z54.5 202.819
#590=2(RECOVER_FACE TURN) #VALUE!
G1X167.751F#101M26 167.751
G0Z61.398 #VALUE!
X163.682 163.682
G1 X 153.873 Z 62 153.873
X147.873 147.873
G0X162.375Z62.394 162.375

is result of above...

--

john taiariol

unread,

Feb 10, 2021, 6:48:09 AM2/10/21

to

the coordinates are not always 7 characters.

Claus Busch

unread,

Feb 10, 2021, 7:42:51 AM2/10/21

to

Hi John,

Am Wed, 10 Feb 2021 03:48:04 -0800 (PST) schrieb john taiariol:

> the coordinates are not always 7 characters.

try:
=IFERROR(IF(LEFT(A1,1)="X",TRIM(SUBSTITUTE(A1,"X",)),TRIM(MID(LEFT(SUBSTITUTE(A1,"F","Z"),FIND("Z",SUBSTITUTE(A1,"F","Z"))-1),FIND("X",LEFT(SUBSTITUTE(A1,"F","Z"),FIND("Z",SUBSTITUTE(A1,"F","Z"))-1))+1,99))),"")

If that doesn't work use an UDF.
Copy following code into a module and call the function in the sheet
with =Coord(A1)

Function Coord(myRange As Range) As String
Set re = CreateObject("vbscript.regexp")
mystring = Replace(myRange.Text, " ", "")
ptrn = "X\d{1,3}\.\d{1,3}"
re.Pattern = ptrn
re.Global = True
re.ignoreCase = False
Set matches = re.Execute(mystring)
If matches.Count > 0 Then
Coord = Mid(matches(0), 2)
End If
End Function

john taiariol

unread,

Feb 10, 2021, 2:50:05 PM2/10/21

to

I was able to get it working thank you all for your help