How do I change multi-line entries to single line entries in Exce.
 

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.

I believe you want to use CONCATENATE.

It should be something like:

=Concatenate(A1, A2)

to join cells A1 and A2

Hope this helps,
Gary

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.

In column A you have:

name
street
city
state
zip


in cell b1 enter =a2
c1 =a3
d1 =a4
e1 =a5

select b1:e5
fill down
select columns b:e
copy
paste special values
sort by any column other than A
delete rows where b:e are blank

--Carlos

"CPOWEREQUIP" wrote in message
...
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.