Importing Data Delimited by 2 Spaces

[dubvie]

I am having a problem importing a text file into excel. The data in the file is delimited by 2 spaces instead of 1. Any ideas on how to quickly Import the file?

I tried doing a find and replace on the 2 spaces, but it is taking FOREVER.

 

[Norie]

How are you importing it?

Isn't there an option in the Text Import wizard to 'Treat consecutive delimiters as one'?

 

[Andrew Poulsom]

At Step 2 of the Text Import Wizard, check "Treat consecutive delimiters as one".

 

[dubvie]

I cannot use the consecutive delimiters option b/c of the fact that the delimiter is 2 spaces and some of the data between 2 delimiters (say an address) contains 1 space.

Thanks for the help though... any other ideas?

 

[Andrew Poulsom]

Import you text file into column A without any parsing and run this macro:

Sub Test()
    With ActiveSheet.Columns(1)
        .Replace "  ", "@"
        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="@"
    End With
End Sub

Use a character other than @ if your text file already contains it.

 

[dubvie]

Thanks Andrew, that worked great!

A friend of mine also pointed me to his website http://www.nandeshwar.info/projects/xlblog/

where he had the following solution:

------------------------------------------------------
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

--------------------------------------------------------------

Your's was much faster however, so I'll use that. Thanks again!