Wednesday, August 24, 2005
Parse data with double spaces
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.
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
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
