Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Extract text from text string
Hello,
I would like to extract the Xcoordinate 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 Xcoord 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
Posted to microsoft.public.excel.programming




Extract text from text string
On 2/9/2021 8:44 AM, john taiariol wrote:
Hello, I would like to extract the Xcoordinate 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 Xcoord 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
Posted to microsoft.public.excel.programming




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 Xcoord 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
Posted to microsoft.public.excel.programming




Extract text from text string
On 2/9/2021 8:44 AM, john taiariol wrote:
Hello, I would like to extract the Xcoordinate 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 Xcoord 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 variablewidth fields, unfortunately, because then the last "7" for number of characters is also a variable. Unfortunately, the Excel VALUE() function isn't smartenough 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
Posted to microsoft.public.excel.programming




Extract text from text string
On Tuesday, February 9, 2021 at 5:09:40 PM UTC5, dpb wrote:
On 2/9/2021 8:44 AM, john taiariol wrote: Hello, I would like to extract the Xcoordinate 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 Xcoord 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 variablewidth fields, unfortunately, because then the last "7" for number of characters is also a variable. Unfortunately, the Excel VALUE() function isn't smartenough 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
Posted to microsoft.public.excel.programming




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
Posted to microsoft.public.excel.programming




Extract text from text string
On Wednesday, February 10, 2021 at 7:42:51 AM UTC5, 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 
Display Modes  


Similar Threads  
Thread  Forum  
Extract value from a text string  Excel Worksheet Functions  
Need to extract certain text from text string  Excel Worksheet Functions  
Extract text from a string  Excel Worksheet Functions  
Extract text string using MID  Excel Worksheet Functions  
EXTRACT TEXT FROM TEXT STRING  Excel Worksheet Functions 