Extract text from text string
 

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.

Extract text from text string
 

On 2/9/2021 8:44 AM, john taiariol wrote:
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.

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

--

Extract text from text string
 

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,F IND("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(SUB STITUTE(A1,"X",)),"")))

Regards
Claus B.
--
Windows10
Microsoft 365 for business

Extract text from text string
 

On 2/9/2021 8:44 AM, john taiariol wrote:
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.


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

--

Extract text from text string
 

On Tuesday, February 9, 2021 at 5:09:40 PM UTC-5, dpb wrote:
On 2/9/2021 8:44 AM, john taiariol wrote:
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.

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

--
the coordinates are not always 7 characters.

Extract text from text string
 

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",S UBSTITUTE(A1,"F","Z"))-1),FIND("X",LEFT(SUBSTITUTE(A1,"F","Z"),FIND("Z",S UBSTITUTE(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


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Extract text from text string
 

On Wednesday, February 10, 2021 at 7:42:51 AM UTC-5, Claus Busch wrote:
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",S UBSTITUTE(A1,"F","Z"))-1),FIND("X",LEFT(SUBSTITUTE(A1,"F","Z"),FIND("Z",S UBSTITUTE(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
Regards
Claus B.
--
Windows10
Microsoft 365 for business

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