VB script/macro for excel to extract numbers from txt file

We are trying to find/create a batch file that will take certain sections of a .txt file and put them into rows 1, 2, 3, ect in cells, A, B, C and D in Excel. The sample file has been included and we want to extract the values for example of the screenshot included but all the way down: Any assistance offered would be appreciated.
untitled.JPG04-23-6RPT.txt
regsampAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Will the "0-00-00" in lines like this one always be the same?
10668  6014   0-00-00     6014 579.184   0-00-00       80.330 NAVD88 1323/67
Martin LissOlder than dirtCommented:
Do you want 78292.159 to appear that way or do you want 78292.16?
Martin LissOlder than dirtCommented:
Try this macro. You'll probably want me to add code so that you can choose the input file but first let me know if there are any problems. You'll need to change the path to the file.

Sub GetData()

Dim FF As Integer
Dim strLine As String
Dim intPos As Integer
Dim lngRow As Long

FF = FreeFile

Open "c:\safari downloads\04-23-6RPT.txt" For Input As #FF

Do While Not EOF(FF)
    Line Input #FF, strLine
     ' The first line in a set is blank so get the next line
    Line Input #FF, strLine
     
    ' Find the first dash
    intPos = InStr(1, strLine, "-")
    lngRow = lngRow + 1
    Sheets("Sheet1").Cells(lngRow, 1) = Mid$(strLine, intPos + 11, 12)
    
    ' Get to the last line in the set
    Line Input #FF, strLine
    Line Input #FF, strLine
    Line Input #FF, strLine
    ' Find "N:"
    intPos = InStr(1, strLine, "N:")
    Sheets("Sheet1").Cells(lngRow, 2) = Mid$(strLine, intPos + 2, 10)
    ' Find "E:"
    intPos = InStr(intPos, strLine, "E:")
    Sheets("Sheet1").Cells(lngRow, 3) = Mid$(strLine, intPos + 2, 10)
    ' Find "El:"
    intPos = InStr(intPos, strLine, "El:")
    Sheets("Sheet1").Cells(lngRow, 4) = Mid$(strLine, intPos + 3, 6)
Loop
Close
End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

regsampAuthor Commented:
The lines in "0-00-00 will be different. See how on the second one it is 0-00-00     5249 251.130 and we would like 78292.159 because this is for a Survey study.
regsampAuthor Commented:
I guess we just need the first four numbers after the 0-00-00 so it would just be 5249 and not 5259 251.130. Okay, let me try the Macro.
regsampAuthor Commented:
That is perfect. How can I just shorten the first column to just four numbers like 6014 instead of 6014 579.184 and I am all set?
Martin LissOlder than dirtCommented:
OK here's an updated macro that lets you choose the file. Change line 18 if you want to use a different initial folder. Note that one peculiarity of VBA is that if you do change the code to reflect a different folder that you may have to run it twice before you see the change.
Sub GetData()

Dim FF As Integer
Dim strLine As String
Dim intPos As Integer
Dim lngRow As Long
Dim dlgFile As FileDialog
Dim FileChosen As Integer

FF = FreeFile

' Choose the file to open
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
FileChosen = dlgFile.Show
dlgFile.Title = "Please choose file to import"
' Set the initial folder selection
dlgFile.InitialFileName = ThisWorkbook.Path
dlgFile.InitialView = msoFileDialogViewList
dlgFile.Filters.Clear
dlgFile.Filters.Add "Text Files", "*.txt"
dlgFile.Filters.Add "All files", "*.*"
dlgFile.FilterIndex = 1
' Set the ButtonName property to control the text on
' the OK button (the ampersand means the following
' letter is underlined and choosable with the ALT key)
dlgFile.ButtonName = "&Select file"
If FileChosen <> -1 Then
    MsgBox "You chose cancel"
    Exit Sub
End If

Open dlgFile.SelectedItems(1) For Input As #FF
 
Do While Not EOF(FF)
    Line Input #FF, strLine
     ' The first line in a set is blank so get the next line
    Line Input #FF, strLine
     
    ' Find the first dash
    intPos = InStr(1, strLine, "-")
    lngRow = lngRow + 1
    Sheets("Sheet1").Cells(lngRow, 1) = Mid$(strLine, intPos + 11, 4)
    
    ' Get to the last line in the set
    Line Input #FF, strLine
    Line Input #FF, strLine
    Line Input #FF, strLine
    ' Find "N:"
    intPos = InStr(1, strLine, "N:")
    Sheets("Sheet1").Cells(lngRow, 2) = Mid$(strLine, intPos + 2, 10)
    ' Find "E:"
    intPos = InStr(intPos, strLine, "E:")
    Sheets("Sheet1").Cells(lngRow, 3) = Mid$(strLine, intPos + 2, 10)
    ' Find "El:"
    intPos = InStr(intPos, strLine, "El:")
    Sheets("Sheet1").Cells(lngRow, 4) = Mid$(strLine, intPos + 3, 6)
Loop
Close
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
regsampAuthor Commented:
Perfect. Thank you. I will try it now.
regsampAuthor Commented:
Excellent. Thank you again.
Martin LissOlder than dirtCommented:
I assume now that you'll be closing the question. One thing you might want to do is to add these lines

' Format the columns
Columns("A:D").Select
Selection.NumberFormat = "@"

at line 11. If you do the trailing zeros will show up so that it looks like this.
Format changed
regsampAuthor Commented:
Okay, thanks for that tip. I appreciate it.
regsampAuthor Commented:
Excellent help
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help. It's also nice to have you on the team:)

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
regsampAuthor Commented:
You did help a lot and thank you again. I will take a look and you would be a contact I would definitely look for help from again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.