importing text file into excel 2000

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have a text file with a list of addresses on multiple lines. Each address
is three lines, name, street address, town/state/zip.
Each record is divided by a blank space. I want to import each of these
into excel 2000, with each line being a field in the record.
It doesn't appear I can do this with the import wizard.

Ultimately I want to use this spreadsheet for a mail merge word function.
Any thoughts on the best way to do this? Thanks!
 
i meant to say each record is divided by a blank LINE, not space.

ie:

Carlson GMAC Real Estate

28 Green St

Newburyport, MA 01950-2650 Phone: (978) 462-8155



Century 21 Heritage Realty Associates

10 Prince Pl

Newburyport, MA 01950-2612 Phone: (978) 463-3100



Dewolfe Companies Inc

61 State St

Newburyport, MA 01950-6612 Phone: (978) 465-1927
 
sigh..... outlook express reformatted my text, there is no blank line
between the name, street address and town address
 
Rob

If data is consistent in groups of three with a blank in between as your
example shows, this macro will do the trick.

When the inputbox comes up, enter 4 to accommodate the blank rows.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub

If unfamiliar with macros, visit David McRitchie's "getting started with VBA"
site at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP
 
Back
Top