Merging multiple columns of data into one column of data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large spread sheet that I inherited... I need to merge five columns
of text data into one column.
Example:

Before...
A B C D E
The Quick Brown Fox Jumped

After...
A
The
Quick
Brown
Fox
Jumped

Help!
 
try =CONCATENATE(A1,B1,C1,D1,E1)
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
May not be acceptable with your volume but put this in column and copy down
(assumes data starts in A1):

=OFFSET($A$1,INT((ROW()-1)/5),MOD(ROW()-1,5))

Then copy, paste special=> values

HTH
 
John - That worked great the data is all in one cell now. Is there a way to
put each one of those columns on a 'new' line in the concatenated column?
Mike
 
I think all you can do is set the column width and format the cells to word
wrap. You will need spaces between words so if there is not a space at the
end of each word you will need to use =CONCATENATE(A1," ",B1," ",C1," ",D1,"
",E1)
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
Try this - output is in column F

Sub newline()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For irow = 1 To lastrow
mystr = ""
For icol = 1 To 5
mystr = mystr + Cells(irow, icol) + Chr(10)
Next icol
Cells(irow, "F") = mystr
Next irow
End Sub
 
Back
Top