Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
CPOWEREQUIP
 
Posts: n/a
Default 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.
  #2   Report Post  
gbeard
 
Posts: n/a
Default

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


  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

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.


  #4   Report Post  
Carlos Antenna
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In mixed bar and line chart. I cannot change one of my source da. bluetractorboy Charts and Charting in Excel 1 January 21st 05 04:17 PM
In mixed bar and line chart. I cannot change one of my source da. bluetractorboy Charts and Charting in Excel 0 January 21st 05 03:57 PM
How do I change the colour of/remove the Freeze Panes line in Exce katya_1978 Excel Worksheet Functions 1 January 20th 05 07:17 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM
How to change a single cell's width TemporarilyConfused Excel Worksheet Functions 2 November 10th 04 08:36 PM


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"