I have a flat text file that looks like this
Mr and Mrs Smith
19 Elm Street
Boston, Ma 01231

Mark Hanson
Kathy Elmstead
13 sycamore drive
Bolston Mt 91320

Mark and Linda Everett
342 Pine Street
Austin, Tx 53221

it goes on for about 500 entries

I'd love to get it into this format

Mr and Mrs Smith 19 Elm Street Boston Ma 01676
etc

any clever ideas

thanks
Lowell

Lowell

To get into 3 or 4 columns per address.

Remember!! Always experiment on a copy of the sheet or book.

Public Sub AddressSort()
last = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
With Worksheets(1)
For x = last To 2 Step -1
If .Cells(x, 1).Value < "" And _
Cells(x - 1, 1).Value < "" Then
Range(.Cells(x, 1), .Cells(x, 10)).Copy _
Destination:=.Range(.Cells(x - 1, 2), .Cells(x - 1, 2))
Rows(x).Delete
End If
Next x
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End With
End Sub

Copied to a general module in your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

Gord Dibben Excel MVP


On 29 Nov 2004 16:01:44 -0800, (lucky_lowell) wrote:

I have a flat text file that looks like this
Mr and Mrs Smith
19 Elm Street
Boston, Ma 01231

Mark Hanson
Kathy Elmstead
13 sycamore drive
Bolston Mt 91320

Mark and Linda Everett
342 Pine Street
Austin, Tx 53221

it goes on for about 500 entries

I'd love to get it into this format

Mr and Mrs Smith 19 Elm Street Boston Ma 01676
etc

any clever ideas

thanks
Lowell

Gord,

Your in my prayers!
Fast Effecient Thoughtful
and able to solve the problem!!!!!!

Gord for President!

Regards
Lowell

Gord Dibben <gorddibbATshawDOTca wrote in message . ..
Lowell

To get into 3 or 4 columns per address.

Remember!! Always experiment on a copy of the sheet or book.

Public Sub AddressSort()
last = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
With Worksheets(1)
For x = last To 2 Step -1
If .Cells(x, 1).Value < "" And _
Cells(x - 1, 1).Value < "" Then
Range(.Cells(x, 1), .Cells(x, 10)).Copy _
Destination:=.Range(.Cells(x - 1, 2), .Cells(x - 1, 2))
Rows(x).Delete
End If
Next x
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End With
End Sub

Copied to a general module in your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

Gord Dibben Excel MVP


On 29 Nov 2004 16:01:44 -0800, (lucky_lowell) wrote:

I have a flat text file that looks like this
Mr and Mrs Smith
19 Elm Street
Boston, Ma 01231

Mark Hanson
Kathy Elmstead
13 sycamore drive
Bolston Mt 91320

Mark and Linda Everett
342 Pine Street
Austin, Tx 53221

it goes on for about 500 entries

I'd love to get it into this format

Mr and Mrs Smith 19 Elm Street Boston Ma 01676
etc

any clever ideas

thanks
Lowell