VBA provides some file operation statements that can be used to read from or write to a text file. Most text files are easily imported into Excel using File > Open and the Text Import Wizard. Sometimes the file contains so much junk to clean up that it’s easier to write your own procedure.
In this example, I have a carat (^) delimited text file with some extraneous tabs and linefeeds.
I want to read this file in one line at a time, strip out the characters I don’t want, then write the remaining text to cells.
Dim sFile As String
Dim sInput As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant
Const sDELIM = “^” ‘Set the delimeter
lFNum = FreeFile
sFile = “C:CaratDelim.txt”
vaStrip = Array(vbLf, vbTab) ‘list the text to strip
‘Open the file
Open sFile For Input As lFNum
‘Loop through the file until the end
Do While Not EOF(lFNum)
Line Input #lFNum, sInput ‘input the current line
‘remove the unwanted text
For i = LBound(vaStrip) To UBound(vaStrip)
sInput = Replace(sInput, vaStrip(i), “”)
‘split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1
‘Write to the worksheet
For i = 0 To UBound(vaFields)
Sheet1.Cells(lRow, i + 1).Value = vaFields(i)
The result is a clean table in Excel.
Of course, if the real life file was as simple as this example, you would just import it normally and do some finding and replacing. But the bigger the file and the more complex the text you need to remove, the more attractive a technique like this becomes.