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!

Copy your columns
Paste - Special - Transpose

"Archangel" wrote:

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)


"Archangel" wrote:

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!

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

"Archangel" wrote:

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!

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

"john" wrote:

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)


"Archangel" wrote:

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!

=INDEX($A$1:$E$1,ROWS($1:1))

Copy down as far as required


"Archangel" wrote:

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!

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)


"Archangel" wrote:

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

"john" wrote:

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)


"Archangel" wrote:

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 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

"Archangel" wrote:

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!