Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 9th 21, 03:44 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 11
Default 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.

  #2   Report Post  
Old February 9th 21, 05:01 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 96
Default 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...

--


  #3   Report Post  
Old February 9th 21, 05:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default 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
  #4   Report Post  
Old February 9th 21, 11:09 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 96
Default 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...

--
  #5   Report Post  
Old February 10th 21, 12:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 11
Default 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.


  #6   Report Post  
Old February 10th 21, 01:42 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,848
Default 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
  #7   Report Post  
Old February 10th 21, 08:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 11
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract value from a text string Dinesh Excel Worksheet Functions 11 April 30th 09 04:29 AM
Need to extract certain text from text string Trista @ Pacific Excel Worksheet Functions 4 November 21st 07 08:07 PM
Extract text from a string ellebelle Excel Worksheet Functions 4 June 13th 07 04:25 PM
Extract text string using MID Turk Excel Worksheet Functions 5 October 11th 06 06:39 PM
EXTRACT TEXT FROM TEXT STRING carricka Excel Worksheet Functions 4 July 8th 05 11:00 AM


All times are GMT +1. The time now is 02:55 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017