Solved: Importing space delimited text files

[Carl A]

I have text files with the zip codes of various cities. These files are delimited with spaces.

I use johnske text import from the KB
http://vbaexpress.com/kb/getarticle.php?kb_id=497

examples:
Zip City Area Code

62820 Burnt Prairie 618
60527 Burr Ridge 630

60102 Lake in the Hills 847

The cities with two names I can join together with
=IF(ISTEXT(Import!C:C),Import!B:B&" "&Import!C:C,Import!B:B)

But I'm having trouble with cities with 3 names or more.

A better formula would be appreciated or better a VBA routine that can automate the process.

I have included sample text file in the zip.

Any assistance will be appreciated TIA!

[Mavyak]

[VBA]Sub format_zip_city_area_code_data()
Dim c As Range
Dim x As Integer
Dim zip As String, city As String, ac As String

For Each c In ActiveSheet.Range("A1:A" & ActiveSheet.Range("A65536").End(xlUp).Row)
myCount = c.End(xlToRight).Column - 1
For x = 0 To myCount
Select Case x
Case Is = 0
zip = c.Offset(0, x).Value
Case Is = myCount
ac = c.Offset(0, x).Value
Case Else
city = city & " " & c.Offset(0, x).Value
End Select
Next x
c.EntireRow.Clear
c.Value = zip: zip = ""
c.Next = Trim(city): city = ""
c.Next.Next = ac: ac = ""
Next c

Set c = Nothing
End Sub[/VBA]

Probably an easier way to do this but in a picnh, the above code works.

[Mavyak]

Note: Be careful copying/pasting zip codes in Excel. It has a tendency to lop off leading zeroes resulting in the nort-eastern values being only 4 digits long.

[Cleaner007]

Does anybody know about Clone Remover? It's said to be a good duplicate file removal tool. These guys here - moleskinsoft.com

[Carl A]

Note: Be careful copying/pasting zip codes in Excel. It has a tendency to lop off leading zeroes resulting in the nort-eastern values being only 4 digits long.

Thanks for the code works really nice.

For the time being I don't have to worry about east coast zips but eventually I will.

[Carl A]

Does anybody know about Clone Remover? It's said to be a good duplicate file removal tool. These guys here - moleskinsoft.com

This has been reported as spam on other sites. Even uses same name.

[Mavyak]

This has been reported as spam on other sites. Even uses same name.

It's spam here too:

http://vbaexpress.com/forum/search.php?searchid=294315