Is the list consistent for size?

i.e in consistent sets like below.

name
address
city
state
zip

If so, you can use a formulaic method to get them in a single row in
individual cells.

Assuming name is cell A1 enter in B1 and drag across to F1 and down until you
get zeros.

=OFFSET($A$1,(ROW()-1)*5+COLUMN()-2,0)

When happy,select Columns B:F then copypaste special(in place)valuesOK

Sets must be consistent. If 4 to a set adjust *5 to *4 and drag across to E1


VBA Macro.........again with consistent sets.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Final Number of Columns Desired")
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
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP


On Wed, 13 Apr 2005 14:04:04 -0700, "CPOWEREQUIP"
wrote:

Hi. I have just pasted an address list from Word to Excel. I want to change
the multi line entries to single line entries so I can merge it with another
document in Excel.

I hope this makes sense.