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 group. This tutorial is talking about how to merge or combine different columns of data into one column.

doc-merge-losing-data


Solutions


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


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-data1 doc-merge-multiple-workbooks-arrow2 doc-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-data4 doc-merge-multiple-workbooks-arrow2 doc-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-column-data-01

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

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

(1.)Select Select Combine columns under To combine selected cells according to following options;

(2.)Specify a separator for the combined data, here I select Space;

(3.))Specify the cell you want to place your combined result;

(4.)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-column-data-02

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 cells Delete contents of combined cells Merge the combined cells
doc-merge-columns-data9 doc-merge-columns-data10 doc-merge-columns-data11

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

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink +19 p4u
Thanks a lot sir l love u
2013-11-21 16:25 Reply Reply with quote Quote
Permalink +4 Angela
Wish I could share like tweet etc. This was super helpful. Thanks!
2013-12-06 01:16 Reply Reply with quote Quote
Permalink +1 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.
2013-12-08 17:27 Reply Reply with quote Quote
Permalink 0 Asadullah
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!
2014-06-23 04:38 Reply Reply with quote Quote
Permalink -2 CLaudia
I have the same question as MommaMary.

And a thankyou!
2013-12-11 11:09 Reply Reply with quote Quote
Permalink +5 MommaMary
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.
2013-12-22 22:18 Reply Reply with quote Quote
Permalink +4 Louisa
Wow! Works like a charm. Thank you!
2014-01-13 22:25 Reply Reply with quote Quote
Permalink 0 colleen
yup thanks a ton!! :-)
2014-01-16 06:18 Reply Reply with quote Quote
Permalink +1 Irina
Excellent tip!!! thanks a lot! saves me so much hassle in future :)
2014-01-23 11:06 Reply Reply with quote Quote
Permalink +1 Sandra
Fantastic, easy and just what I needed. Thank you
2014-02-02 20:17 Reply Reply with quote Quote
Permalink -5 SaravananS
Excellent tips... Thanks
2014-02-10 09:41 Reply Reply with quote Quote
Permalink -4 Stephanie
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
2014-02-13 15:02 Reply Reply with quote Quote
Permalink -5 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:
2014-02-18 11:21 Reply Reply with quote Quote
Permalink -2 Morenina
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&…
:-)
2014-03-01 00:41 Reply Reply with quote Quote
Permalink 0 Medhesh
I tried with this and it is really helpful. Thanks a lot.
2014-02-20 07:33 Reply Reply with quote Quote
Permalink 0 Markvdb
Thanks for the help!
2014-03-09 17:15 Reply Reply with quote Quote
Permalink 0 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 )
2014-03-10 20:18 Reply Reply with quote Quote
Permalink 0 Admin-chivo
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.e xtendoffice.com /product/kutool s-for-excel/exc el-transform-co lumn-to-row.htm l##4
2014-03-11 02:10 Reply Reply with quote Quote
Permalink 0 Ray
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.
2014-03-11 19:26 Reply Reply with quote Quote
Permalink +1 Subhasis Dutta
Thanks for tipses, It is very helpful.
2014-03-17 06:59 Reply Reply with quote Quote
Permalink 0 Niamatullah Faizi
Thanks a lot, very fantastic
2014-03-20 05:04 Reply Reply with quote Quote
Permalink 0 shashi
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.. ????
2014-03-20 05:48 Reply Reply with quote Quote
Permalink 0 Nitin
Thanks a lot...its very usefull
2014-04-11 07:42 Reply Reply with quote Quote
Permalink 0 Carl
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.
2014-05-01 07:41 Reply Reply with quote Quote
Permalink 0 pappu
thanks a lot,didnt know this for years
2014-05-24 11:38 Reply Reply with quote Quote
Permalink 0 pili
Thak you very much! This was very helful
2014-05-26 17:28 Reply Reply with quote Quote
Permalink 0 jonas
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!!!
2014-05-29 16:06 Reply Reply with quote Quote
Permalink 0 Max
Thanks for the formula.

I have the same follow-up question as JONAS.

Anyone out there that can help?
2016-05-17 15:57 Reply Reply with quote Quote
Permalink 0 Vishal
Really, jabardust tool kutools is.. thnks god kutools is here. (y)
2014-06-13 12:29 Reply Reply with quote Quote
Permalink 0 alcorp
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)
2014-06-25 15:44 Reply Reply with quote Quote
Permalink 0 jen
format columns to text
2014-07-09 21:42 Reply Reply with quote Quote
Permalink 0 jen
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
2014-07-09 19:42 Reply Reply with quote Quote
Permalink +1 guest
how do you merge two columns in sort/merge sense; eliminating repeats?
2014-07-11 10:58 Reply Reply with quote Quote
Permalink 0 sanjay das
it s helpfull... thanks
:-)
2014-08-18 13:55 Reply Reply with quote Quote
Permalink 0 Marcy
Thank you very much!

If I am merging two columns that have LNAME and FNAME, is there a way to add a comma between so as to have LNAME, FNAME?

Thanks again!

Marcy :D
2014-08-18 19:11 Reply Reply with quote Quote
Permalink 0 Marcy
Hello, please disregard my question. I figured out the answer :-)

Marcy
2014-08-18 19:14 Reply Reply with quote Quote
Permalink 0 Sara
Thank you so much. This saved me!!
2014-09-09 19:24 Reply Reply with quote Quote
Permalink 0 Vlada
Thank you very much! You saved me so much time.
2014-09-23 18:23 Reply Reply with quote Quote
Permalink 0 Andrew
Awesome. But when I merge the two cells there is no space in between the text.
2014-10-02 18:46 Reply Reply with quote Quote
Permalink 0 Asadullah
Quoting Andrew:
Awesome. But when I merge the two cells there is no space in between the text.

Just try:

= A2 & " " & B2

Under the quotes is what you wanna get between them! :)
2014-10-03 12:42 Reply Reply with quote Quote
Permalink +1 Graham
This was so useful I nearly fell off my chair at how simplistic I found the procedure. Many thanks!
2014-10-07 08:22 Reply Reply with quote Quote
Permalink +2 Cathy
I am trying to merge and combine 4 columns, with the data from each column being on a separate line.
For Example, I want it to look like this when I have finished:

Data from Column 1
Data from Column 2
Data from Column 3
Data from Column 4

How do I do that?
2014-11-07 19:57 Reply Reply with quote Quote
Permalink 0 Thomas L
You need to use the transpose function in "Insert special".
2014-11-27 08:04 Reply Reply with quote Quote
Permalink 0 Cathy
Thomas L
I tried using the transpose function in "Paste Special," but I am still getting 4 separate cells (1 for each of the columns I'm trying to merge). How do I get the 4 columns of data merged into 1 cell, with 4 lines
2015-01-27 02:06 Reply Reply with quote Quote
Permalink -1 Thomas L
Thank you, that worked like a charm. Also it is possible to add information between or separate with comma.
2014-11-27 08:03 Reply Reply with quote Quote
Permalink 0 appugee
thanks a lot .the tip helped me very much
2015-01-13 11:12 Reply Reply with quote Quote
Permalink 0 Cathy
Thank you. I haven't had a chance to try this yet. I had surgery recently, and have had other things going on.
2015-01-18 17:00 Reply Reply with quote Quote
Permalink 0 Cathy
To clarify my earlier request:

I am trying to merge and combine 4 columns, with the data from each column being on a separate line.
For Example, I want it to look like this when I have finished:

Data from Cell C2 (new line)
Data from Cell D2 (new line)
Data from Cell E2 (new line)
Data from Cell F2

What do insert between the fields (=C2&" "&D2&" "&E2&" "&F2)to get the new line?

I do not have Kutools.
2015-01-27 18:25 Reply Reply with quote Quote
Permalink -2 jj
:-x :oops: :cry: okayyyyyyy
2015-01-30 13:38 Reply Reply with quote Quote
Permalink +1 Cathy
I have still not received an answer to my question. I think what I want to do is concatenate columns c, D, E, and F, with a line break between the data from each cell. What is the correct formula to do this? Is it:
=CONCATENATE(",C2,",D2,"CHAR (10),E2,"CHAR (10),F2)?
If not, what is the correct formula?
PLEASE HELP!
2015-02-18 23:01 Reply Reply with quote Quote
Permalink +1 Cathy
What is the correct formula to concatenate data from columns C,D, E, and F? I would like a line break between each column's data. Is the correct formula for this:
=CONCATENATE(",C2,",D2,"CHAR (10),E2,"CHAR (10),F2)?

If not, what is the correct formula?

PLEASE HELP!
2015-02-18 23:04 Reply Reply with quote Quote
Permalink 0 Momen
Thamk you very much , helped me a lot .
2015-04-26 12:51 Reply Reply with quote Quote
Permalink +1 carol
thanks you so much this really helped me .
2015-05-14 09:22 Reply Reply with quote Quote
Permalink 0 Sagar Soni
Thanks a lot, Sir! This is very helpful.
2015-05-15 18:05 Reply Reply with quote Quote
Permalink 0 Praveen kumar Samikk
Thank you so much. Worked like magic!
:-)
2015-08-03 13:38 Reply Reply with quote Quote
Permalink 0 ShalikRam Panth
Thank you very much! This was very helpful.
2015-09-14 12:42 Reply Reply with quote Quote
Permalink +1 Wazeem
Thank you very much :-*
2015-10-26 08:14 Reply Reply with quote Quote
Permalink 0 Ramann
Thanks a million.

God Bless You.
2015-11-19 15:05 Reply Reply with quote Quote
Permalink 0 Rasheed
Thank you very much. I really appreciate you people worked so hard to make our job easy. Thanks again.
2015-12-16 17:53 Reply Reply with quote Quote
Permalink 0 jyothi babu
Thanks for information and it is very use full for me.
2016-01-22 15:25 Reply Reply with quote Quote
Permalink 0 Kumaresan raina
thank for so much. great
2016-03-05 10:18 Reply Reply with quote Quote
Permalink 0 Abhi
Great work, Thank you
2016-03-29 14:10 Reply Reply with quote Quote
Permalink 0 Warom Hillary
Grate work......Thank s Big...
2016-06-25 13:08 Reply Reply with quote Quote
Permalink 0 Vinay
I am trying to merge and combine 4 columns, with the data from each column being on a separate line.
For Example, I want it to look like this when I have finished:

Data from Column 1
Data from Column 2
Data from Column 3
Data from Column 4

How do I do that?

I tried using the transpose function in "Paste Special," but I am still getting 4 separate cells (1 for each of the columns I'm trying to merge). How do I get the 4 columns of data merged into 1 cell, with 4 lines
2016-07-27 22:55 Reply Reply with quote Quote
Permalink 0 Barry
I cannot get the space to appear. This is my formula =G4&" "&I4. I am using Excel 2013.
2016-08-17 15:54 Reply Reply with quote Quote
Permalink 0 Adam Evans
Thank you!!! That worked perfectly!
2016-08-19 16:25 Reply Reply with quote Quote
Permalink 0 jim
Ineed to convert a string of numbers (06191948) in a column to read 06/19/1948 can anyone help me?
2016-09-05 01:45 Reply Reply with quote Quote
Permalink 0 Maryam
It was Perfecttttt, Thanks
2017-01-28 09:14 Reply Reply with quote Quote

Add comment


Security code
Refresh