Extract a single line of data from numerous text files and import into Excel

I didn't want to hijack a thread, but I am working off of this OP's application: Extract a single line of data from numerous text files and import into Excel

The solution there is amazing and works for his application:

    Sub ExtractGPS()
    Dim filename As String, nextrow As Long, MyFolder As String
    Dim MyFile As String, text As String, textline As String, posGPS As String

    MyFolder = "C:\Users\Desktop\Test\"
    MyFile = Dir(MyFolder & "*.txt")

    Do While MyFile <> ""
        Open (MyFolder & MyFile) For Input As #1
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop

        Close #1
        MyFile = Dir()
        posGPS = InStr(text, "GPS Position")
        nextrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).row   1
        Sheet1.Cells(nextrow, "A").Value = Mid(text, posGPS   33, 37)
    Loop
End Sub

So I am also in need to extract text from multiple text files, however with my application I will have different sized string lengths that will not always equal 37 characters in length.

Sheet1.Cells(nextrow, "A").Value = Mid(text, posGPS   33, 37)

...

    for example:
GPS Position                    : 50 deg 7' 33.40" N, 5 deg 30' 4.06" W
GPS Position                    : 20 deg 7' 22.0" N, 9 deg 2' 3.5" W
GPS Position                    : 50 deg 7' 29.401" N, 5 deg 20' 9.095" W

I am a noob to VBA but know enough to make things happen. To me it seems like there has to be a way to specify until the end of line. Or I need to make a variable that counts the number of characters until if finds a carriage or something in that manner.

Obviously in the OP's original application the the string length wouldn't vary and the above GPS Positions would never print out like I have above. If someone could help me out with my application I would greatly appreciate it.

Mike

CodePudding user response：

You can use InStr to return the index of the next linebreak and use that, with some math, as the length argument of Mid. Like:

Mid(Text, posGPS   33, InStr(posGPS, Text, vbCr) - posGPS - 33)

Note: If vbCr isn't working, you can try vbLf as well.