Merge and Combine Columns without Losing Data in Excel

If you merge multiple columns of data in Excel (no matter which Excel version you are using), only the left column of data will be kept, and the data of other columns will be deleted, if you apply the "Merge and Center" command from Home tab on the Alignment panel. This tutorial is talking about how to merge or combine different columns of data into one column.

doc-merge-losing-data


Solutions

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Merge columns of data into one column with formula

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Supposing you have columns of data and you need to combine the data into one column, as shown in the following screenshots, you can deal with it with some formulas:

doc-merge-columns-data1doc-merge-multiple-workbooks-arrow2doc-merge-columns-data2

Merge columns of data without spaces between the data: =A1&B1&C1&…

Merge columns of data with spaces between the data: =A1&" "&B1&" "&C1&…

1. In cell E1, please enter the formula “=A1&B1&C1” or “=A1&" "&B1&" "&C1”.

doc-merge-columns-data3

2. Then press Enter key and select the cell E1, drag the fill handle over the range of cells that you want to apply this formula.

3. And the columns of data have been merged into one column. As they are formulas, you need to copy and paste them as values when you want to paste them to other places. See screenshots:

doc-merge-columns-data4doc-merge-multiple-workbooks-arrow2doc-merge-columns-data5

arrow blue right bubble Quickly merge columns of data without losing data with Kutools for Excel

You can use the third party add-in Kutools for Excel to quickly merge multiple columns.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

After installing Kutools for Excel, please click Kutools > Combine. See screenshot:

doc-merge-columns-data6

1. Select the columns of data that you want to merge.

2. In the Combine Columns & Rows dialog box, specify the options that you need. See screenshot:

(1.)Select Based on row under Merge selected cells.

(2.)Specify a separator for the combined data.

(3.)Specify how you want to deal with the combined cells. You can keep or delete contents from those combined cells, and you can also merge those combined cells.

doc-merge-columns-data7

3. Then click OK to combine multiple columns into one column without losing data. You will get the following results:

doc-merge-columns-data8
Keep contents of combined cellsDelete contents of combined cellsMerge the combined cells
doc-merge-columns-data9doc-merge-columns-data10doc-merge-columns-data11

Tip: The Combine feature of Kutools for Excel can be much more helpful. Know more about the Combine feature...

Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+6#p4u2013-11-21 16:25
Thanks a lot sir l love u
Reply | Reply with quote | Quote
0#Angela2013-12-06 01:16
Wish I could share like tweet etc. This was super helpful. Thanks!
Reply | Reply with quote | Quote
-2#MommaMary2013-12-08 17:27
Now that the cells are merged, how can I copy and paste them to another group that I want to alphabetize? I do a lot of genealogy and I want to put all of the residents of a county's cemeteries in a large spread sheet. There is always one person that lists each name in a separate column. I did the fantastic merge of columns but it copies the formula not the text to the page I want.
Reply | Reply with quote | Quote
0#Asadullah2014-06-23 04:38
Quoting MommaMary:
Now that the cells are merged, how can I copy and paste them to another group that I want to alphabetize? I do a lot of genealogy and I want to put all of the residents of a county's cemeteries in a large spread sheet. There is always one person that lists each name in a separate column. I did the fantastic merge of columns but it copies the formula not the text to the page I want.

Just Paste as Values only! :D Works well!
Reply | Reply with quote | Quote
-2#CLaudia2013-12-11 11:09
I have the same question as MommaMary.

And a thankyou!
Reply | Reply with quote | Quote
+3#MommaMary2013-12-22 22:18
After you copy, use the Values paste option. Mine has a 1-2-3 on the picture of the paste choice. This will paste only the info and none of the formula.
Reply | Reply with quote | Quote
+2#Louisa2014-01-13 22:25
Wow! Works like a charm. Thank you!
Reply | Reply with quote | Quote
+1#colleen2014-01-16 06:18
yup thanks a ton!! :-)
Reply | Reply with quote | Quote
+1#Irina2014-01-23 11:06
Excellent tip!!! thanks a lot! saves me so much hassle in future :)
Reply | Reply with quote | Quote
+1#Sandra2014-02-02 20:17
Fantastic, easy and just what I needed. Thank you
Reply | Reply with quote | Quote
0#SaravananS2014-02-10 09:41
Excellent tips... Thanks
Reply | Reply with quote | Quote
-1#Stephanie2014-02-13 15:02
Thank god to finding this place :) I have the paste special feature or paste, not the numbers 1,2,3 How do I paste the final text product without taking the formual? the content without the formula
Reply | Reply with quote | Quote
-2#yippy2014-02-18 11:21
Hi,
My formula is A&B&C, Eg: MyNameIs.
After I paste new data replacing A, B and C, the result in the formula became My NameIs. How can I merge it as MyNameIs? I have dechecked all delimiters but it doesnt work. :sad:
Reply | Reply with quote | Quote
0#Morenina2014-03-01 00:41
Quoting Medhesh:
I tried with this and it is really helpful. Thanks a lot.

Quoting yippy:
Hi,
My formula is A&B&C, Eg: MyNameIs.
After I paste new data replacing A, B and C, the result in the formula became My NameIs. How can I merge it as MyNameIs? I have dechecked all delimiters but it doesnt work. :sad:



Merge columns of data with spaces between the data: =A1&" "&B1&" "&C1&…
:-)
Reply | Reply with quote | Quote
+2#Medhesh2014-02-20 07:33
I tried with this and it is really helpful. Thanks a lot.
Reply | Reply with quote | Quote
0#Markvdb2014-03-09 17:15
Thanks for the help!
Reply | Reply with quote | Quote
0#qrcca2014-03-10 20:18
Working in excel. I have columns a-bk and rows 1-133.
I need to make another worksheet that has all the same info in each cell but have it in a single column, I am at a loss as how this might be done without cutting and pasting each column. How do i combine multiple columns into one column?
(I should end up with one column and 4921 rows )
Reply | Reply with quote | Quote
0#Admin-chivo2014-03-11 02:10
Quoting qrcca:
Working in excel. I have columns a-bk and rows 1-133.
I need to make another worksheet that has all the same info in each cell but have it in a single column, I am at a loss as how this might be done without cutting and pasting each column. How do i combine multiple columns into one column?
(I should end up with one column and 4921 rows )

:D Hello, you can get it done according to:http://www.extendoffice.com/product/kutools-for-excel/excel-transform-column-to-row.html##4
Reply | Reply with quote | Quote
0#Ray2014-03-11 19:26
Fantastic bit of info. One question. One of my cells that is being combined has a date in it. How do you you transfer that as a date format into the combined cell? It just comes up as a number.

Many thanks.
Reply | Reply with quote | Quote
0#Subhasis Dutta2014-03-17 06:59
Thanks for tipses, It is very helpful.
Reply | Reply with quote | Quote
0#Niamatullah Faizi2014-03-20 05:04
Thanks a lot, very fantastic
Reply | Reply with quote | Quote
0#shashi2014-03-20 05:48
thanks a lot... :-) & i want 1 more thing if i do 3 cell merge & after that i have to select from that merge cell selected column or row through CTRL+Space or Shift+space on that time in merge case i m unable to select merge column or row. i can do it mouse but i required shortcut key for that.. ????
Reply | Reply with quote | Quote
0#Nitin2014-04-11 07:42
Thanks a lot...its very usefull
Reply | Reply with quote | Quote
0#Carl2014-05-01 07:41
How about Microsoft adds this is a feature instead of adding stupidity and just changing their programmers rather than making them more intuitive. Isn't the purpose of a computer program to do the work FOR you? Having to jump thru hoops to do what a program should do for users seems a bit pathetic.
Reply | Reply with quote | Quote
0#pappu2014-05-24 11:38
thanks a lot,didnt know this for years
Reply | Reply with quote | Quote
0#pili2014-05-26 17:28
Thak you very much! This was very helful
Reply | Reply with quote | Quote
0#jonas2014-05-29 16:06
I have a follow up question: some of my cells are empty (in some rows, not in all). Using the above system, my final cell may look something like: "text, , ,text, text, text, ,). This is logic, as it copied the empty space of the empty cells. is there a way that excel only copies the text IF there is text, but leaves it blank otherwise?

Much appreciate your help!!!
Reply | Reply with quote | Quote
0#Vishal2014-06-13 12:29
Really, jabardust tool kutools is.. thnks god kutools is here. (y)
Reply | Reply with quote | Quote
0#alcorp2014-06-25 15:44
How would I merge with leading zeros and keep the zeros?
Such as 2007 + 001 + 024 to get 2007001024 but when use the formula I get 2007124. Thanks. (The columns merging are number fields)
Reply | Reply with quote | Quote
0#jen2014-07-09 21:42
format columns to text
Reply | Reply with quote | Quote
0#jen2014-07-09 19:42
I would think you could format your cells to text or use ' before each 0.

I tried it with the columns formatted to text then use the =A1&B1&C1 worked just fine--did not drop the 0s
Reply | Reply with quote | Quote
0#guest2014-07-11 10:58
how do you merge two columns in sort/merge sense; eliminating repeats?
Reply | Reply with quote | Quote

Add comment


Security code
Refresh