regsamp
asked on
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.
04-23-6RPT.txt
04-23-6RPT.txt
Do you want 78292.159 to appear that way or do you want 78292.16?
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 SubASKER
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.
ASKER
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Thank you. I will try it now.
ASKER
Excellent. Thank you again.
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 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.
ASKER
Okay, thanks for that tip. I appreciate it.
ASKER
Excellent help
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
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
ASKER
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.
10668 6014 0-00-00 6014 579.184 0-00-00 80.330 NAVD88 1323/67