Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to merge data from multiple columns to one column | Excel Discussion (Misc queries) | |||
data in multiple columns moved to one column | Excel Discussion (Misc queries) | |||
Multiple columns of data into one long column | Excel Discussion (Misc queries) | |||
how to merge data from multiple columns to one column | Excel Discussion (Misc queries) | |||
splitting 1 column of data into multiple columns | Setting up and Configuration of Excel |