Parse data with double spaces

Home > Uncategorized > Parse data with double spaces

August 24th, 2005 Leave a comment Go to comments

Now, this is very easy to do. But at office spent some good time tackling this problem. The problem was–we had data as a text file from Oracle output, which somehow had double spaces as delimiters, and we got that very late. We had a SAS program, which used to read the file and that program was not working. Then we turned to Excel (of course), and within seconds we got a parsed file.
Here is the code for that.

Public Sub parse_file()
Application.ScreenUpdating = False
Dim i As Integer, j As Long, k As Long, no_of_rows As Long, storeVal, parsedVal
no_of_rows = Range(“A65536″).End(xlUp).Row
For i = 1 To no_of_rows
    storeVal = Range(“A” & i)
    parsedVal = Split(storeVal, “  ”)
    j = UBound(parsedVal)
    Range(Cells(i, 1), Cells(i, j + 1)) = parsedVal
Next i
Application.ScreenUpdating = True
MsgBox “Done”
End Sub

nandeshwar.info