-
Extract string from text file
Hi Friends,
I need to extract specific numbers from a large text file and copy in excel sheet.
Total rows will come around 6K.
I Need to copy the Number after Text String "Item:" in col A & Number after Text String "Nettable Inventory:" in col B.
Result xl sheet will be like below,
Item Nettable Inventory
C110160003 1200
C117CBB203 0
C117CBS675 0
searched in this Forum for code from previous queries and find some but can't make out for my requirement. Your help will be highly appreciated.
sample .txt file attached.
With Best Regards
-
[vba]Option Explicit
Sub ReadText()
Dim arr, fs, a, arrItm, arrInv
Dim txt As String, Itm As String, Inv As String
Dim x As Long, i As Long
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("c:\aaa\Plan Report.txt")
txt = a.readall
a.Close
arrItm = Split(txt, "Item: ")
arrInv = Split(txt, "Nettable Inventory: ")
x = UBound(arrItm)
Range("A1:B1") = Array("Item", "Nettable Inventory")
For i = 1 To x
Itm = Split(arrItm(i))(0)
Inv = Split(arrInv(i))(0)
Cells(i + 1, 1) = Itm
Cells(i + 1, 2) = Inv
Next
Columns("A:B").Columns.AutoFit
End Sub[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Dear MDMACKILLOP,
Many thanks for your reply. It works perfectly well.
Many thanks once again for your time and reply.
With Regards
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules