Extract Substring, then Ignore that Substring, while collecting data from Other substrings

The simple Data TextToColumns functionality seems to provide all you need.
Just copy the file into column A in a worksheet and specify the comma as a delimiter.

Hi Sameki

I think you have three options:-
1) A macro that reads the file line by line and parses the data (or ignores it) as appropriate.
2) A macro that pre-processes the file, prior to import; or
3) Read all the file in to a spreadsheet then delete the rows you don't want.

I reckon Option 3 is probably the most reliable. The following code will clean out any rows with GPS in column A, but doesn't change Row 1, so leaves the headings intact.

Sub CleanData()
Dim CurRow As Single
Application.ScreenUpdating = False
For CurRow = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
 If InStr(Cells(CurRow, 1), "GPS") > 0 Then
  Rows(CurRow).Delete
  End If
 Next CurRow
Application.ScreenUpdating = True

End Sub

Best regards, Rob.

Hello Sameki121,

This macro may be what you want. It lets you choose the text file to open, the worksheet you want the output on, and the range where the output is to start. The header is copied only once.

Sub ImportGPSData()

    Dim Data As Variant
    Dim Filename As String
    Dim FSO As Object
    Dim Line As Long
    Dim N As Long
    Dim R As Long
    Dim Rng As Range
    Dim TextFile As Object
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet1")
        Set Rng = Wks.Range("A1:H1")
        
        Filename = Application.GetOpenFilename("Text Files (*.txt),*.txt")
        If Filename = "False" Then Exit Sub
        
            Set FSO = CreateObject("Scripting.FileSystemObject")
            Set TextFile = FSO.OpenTextFile(Filename, 1, False, 0)
            
            Do While Not TextFile.AtEndOfStream
                Data = TextFile.ReadLine
                Line = Line + 1
                If Line = 1 Then Data = Mid(Data, 7, Len(Data) - 7)
                If Line > 2 And (Line And 1) Then
                    ' If line number is odd then don't copy it.
                Else
                    Rng.Offset(R, 0).Value = Split(Data, ",")
                    R = R + 1
                End If
            Loop
            
            TextFile.Close
            
End Sub

Hello Sameki121,

You'e welcome. Glad to help.