Merging multiple columns of data into one column of data
 

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!

Merging multiple columns of data into one column of data
 

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!

Merging multiple columns of data into one column of data
 

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!

Merging multiple columns of data into one column of data
 

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!

Merging multiple columns of data into one column of data
 

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!

Merging multiple columns of data into one column of data
 

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

Merging multiple columns of data into one column of data
 

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!

Merging multiple columns of data into one column of data
 

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!