2005 August | nandeshwar.info

Parse data with double spaces

August 24th, 2005 No 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

Categories: Uncategorized Tags:

nandeshwar.info

Archive

Parse data with double spaces

Recent Comments

Categories

Blogroll

Archives

Meta