seperate G-Code

[Dr.Evil925]

I need to seperate out a G-Code file into multiple cells. Any sugestions?
This is my start file.

N3G54G90G0X6.2Y.185S10000M3 <colgroup><col width="64"></colgroup><tbody> </tbody>

<tbody>
</tbody>

I want my result to be

N3

G54

G90

G0

X6.2

Y.185

S10000

M3

<tbody>
</tbody>

 

[sergioMabres]

You might like to try Text to Columns, fix length, parse for 2, 3, 3, 2, 4, 5, 6 and 2
Sergio

 

[Ruddles]

What's G-code? Is the file plain text? Can you give us a representative sample of the data - more than a single line?

 

[Dr.Evil925]

It is a .txt file. G-code is used on computerized lathes/mills. It is the code to control multiple servos to make a finished product. I need each cell to have a letter with however many number come after it. Here is a sample.

M300
G54
G0Z1.0
N3G54G90G0X6.2Y.185S10000M3
M7
N4G43Z.25H1M8
N5Z0.
N6G1Z-.375F50.
N7G42Y.5351F100.D51
N8G3X6.0413Y.7047I-.17
N9G1X6.033Y.705
N10G3X5.863Y.535J-.17
N11G1Y.337
N12G2X5.738Y.212I-.125
N13G1X4.7209
N14G2X4.7059Y.227J.015
N15G1Y.535
N16G3X4.5359Y.705I-.17
N17G1X4.5276Y.7047
N18G3X4.3689Y.5351I.0113J-.1696

 

[sergioMabres]

I was wrong it is not fix length, there are letter to mark the start of a field, i.e.: N??, G?? (more than one group), X?????, Y?????, Z?????, S?????, M???, D???, F???, J????
You can try to find a formula like =IF(ISERROR(FIND("G",A2)),A2,MID(A2,1,FIND("G",A2)-1)
But first letter can be M, N, or G and second letter G or Z so I would recommend to write a function to parse a G-Code line
Sergio

 

[Peter_SSs]

Here is one way. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy across and down.

Function GCode(s As String, Num As Long) As String
    Static RX As Object
    
    If RX Is Nothing Then
        Set RX = CreateObject("VBScript.RegExp")
        RX.Global = True
        RX.Pattern = "[A-Z]([^A-Z])*"
    End If
    On Error Resume Next
    GCode = RX.Execute(s)(Num - 1)
    On Error GoTo 0
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2	M300	M300
3	G54	G54
4	G0Z1.0	G0	Z1.0
5	N3G54G90G0X6.2Y.185S10000M3	N3	G54	G90	G0	X6.2	Y.185	S10000	M3
6	M7	M7
7	N4G43Z.25H1M8	N4	G43	Z.25	H1	M8
8	N5Z0.	N5	Z0.
9	N6G1Z-.375F50.	N6	G1	Z-.375	F50.
10	N7G42Y.5351F100.D51	N7	G42	Y.5351	F100.	D51
11	N8G3X6.0413Y.7047I-.17	N8	G3	X6.0413	Y.7047	I-.17
12	N9G1X6.033Y.705	N9	G1	X6.033	Y.705
13	N10G3X5.863Y.535J-.17	N10	G3	X5.863	Y.535	J-.17
14	N11G1Y.337	N11	G1	Y.337
15	N12G2X5.738Y.212I-.125	N12	G2	X5.738	Y.212	I-.125
16	N13G1X4.7209	N13	G1	X4.7209
17	N14G2X4.7059Y.227J.015	N14	G2	X4.7059	Y.227	J.015
18	N15G1Y.535	N15	G1	Y.535
19	N16G3X4.5359Y.705I-.17	N16	G3	X4.5359	Y.705	I-.17
20	N17G1X4.5276Y.7047	N17	G1	X4.5276	Y.7047
21	N18G3X4.3689Y.5351I.0113J-.1696	N18	G3	X4.3689	Y.5351	I.0113	J-.1696
G Codes

 

[Rick Rothstein]

Peter gave you a UDF (user defined function) that makes use of Regular Expressions which you can distribute throughout the cells in your worksheet. Here is a non-Regular Expression macro that you can run and which will fill in the cell on your worksheet automatically so you do not have to place formulas on the sheet in case that is the direction you want to go...

Sub SplitGCodesApart()
  Dim X As Long, Z As Long, LastRow As Long, S As String
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  For X = StartRow To LastRow
    S = Cells(X, "A").Value
    For Z = Len(S) - 1 To 1 Step -1
      If Mid(S, Z + 1, 1) Like "[A-Za-z]" Then
        S = Application.Replace(S, Z + 1, 0, " ")
      End If
    Next
    Cells(X, "B").Value = S
    Cells(X, "B").TextToColumns Cells(X, "B"), xlDelimited, , True, False, False, False, True, False
  Next
  Application.ScreenUpdating = True
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (SplitGCodesApart) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

 

[Ruddles]

And here's a version which claims to do the same thing but prompts you for an input file first:-

Option Explicit
Public Sub ParseTestToExcel()
  
  Dim ws As Worksheet
  Dim sFileName As String
  Dim dtStart As Date
  Dim intFH As Integer
  Dim iRow As Long
  Dim sRec As String
  Dim iPtr As Integer
  Dim sData As Variant
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  ws.Columns("A:Z").ClearContents
  
  sFileName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt, All Files (*.*), *.*")
  If sFileName = "False" Then Exit Sub
  
  dtStart = Now()
  
  Close
  intFH = FreeFile()
  Open sFileName For Input As #intFH
  
  iRow = 0
  
  Do Until EOF(intFH)
    Line Input #intFH, sRec
    For iPtr = Len(sRec) To 2 Step -1
      Select Case Mid(sRec, iPtr, 1)
        Case "A" To "Z"
          sRec = Left(sRec, iPtr - 1) & "," & Mid(sRec, iPtr)
      End Select
    Next iPtr
    sData = Split(sRec, ",")
    iRow = iRow + 1
    ws.Cells(iRow, "A").Resize(1, UBound(sData) + 1) = sData
  Loop
  
  Close #intFH
  
  MsgBox vbCrLf _
       & CStr(iRow) & " record" & IIf(iRow = 1, "", "s") & " read from " & sFileName _
       & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dtStart, "hh:nn:ss"), _
       vbOKOnly + vbInformation
       
End Sub

 

[shg]

Nice bunch of code in this thread, Peter, Rick, Ruddles.

I wrote it, so I'm posting it.

Sub SplitGCode()
    Dim r           As Range
    Dim cell        As Range
    Dim i           As Long

    Set r = Intersect(Columns("A"), ActiveSheet.UsedRange)
    r.Offset(, 1).Resize(, r.Worksheet.Columns.Count - r.Column).ClearContents

    With CreateObject("VBSCript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z][^A-Z]*)"

        For Each cell In r
            With .Execute(cell.Value2)
                For i = 0 To .Count - 1
                    cell.Offset(, i + 1).Value = .Item(i).Value
                Next i
            End With
        Next cell
    End With
End Sub

Splits the input from col A:

       ---------------A--------------- -B-- -C-- ---D--- --E--- --F--- ---G--- --H--- I-
   1   M300                            M300                                             
   2   G54                             G54                                              
   3   G0Z1.0                          G0   Z1.0                                        
   4   N3G54G90G0X6.2Y.185S10000M3     N3   G54  G90     G0     X6.2   Y.185   S10000 M3
   5   M7                              M7                                               
   6   N4G43Z.25H1M8                   N4   G43  Z.25    H1     M8                      
   7   N5Z0.                           N5   Z0.                                         
   8   N6G1Z-.375F50.                  N6   G1   Z-.375  F50.                           
   9   N7G42Y.5351F100.D51             N7   G42  Y.5351  F100.  D51                     
  10   N8G3X6.0413Y.7047I-.17          N8   G3   X6.0413 Y.7047 I-.17                   
  11   N9G1X6.033Y.705                 N9   G1   X6.033  Y.705                          
  12   N10G3X5.863Y.535J-.17           N10  G3   X5.863  Y.535  J-.17                   
  13   N11G1Y.337                      N11  G1   Y.337                                  
  14   N12G2X5.738Y.212I-.125          N12  G2   X5.738  Y.212  I-.125                  
  15   N13G1X4.7209                    N13  G1   X4.7209                                
  16   N14G2X4.7059Y.227J.015          N14  G2   X4.7059 Y.227  J.015                   
  17   N15G1Y.535                      N15  G1   Y.535                                  
  18   N16G3X4.5359Y.705I-.17          N16  G3   X4.5359 Y.705  I-.17                   
  19   N17G1X4.5276Y.7047              N17  G1   X4.5276 Y.7047                         
  20   N18G3X4.3689Y.5351I.0113J-.1696 N18  G3   X4.3689 Y.5351 I.0113 J-.1696

 

[sergioMabres]

wow you guys rock!