Oct
13

How to merge two columns in Excel without losing data

From this short article you will learn how to merge multiple Excel columns into one without losing data.

You have a table in Excel and what you want is to combine two columns, row-by-row, into one. For example, you want to merge the First Name & Last Name columns into one, or join several columns such as Street, City, Zip, State into a single "Address" column, separating the values with a comma so that you can print the addresses on envelops later.
Merge Excel columns into one without losing data

Regrettably, Excel does not provide any built-in tool to achieve this. Of course, there is the Merge button ("Merge & Center" etc.), but if you select 2 adjacent cells in order to combine them, as shown in the screenshot:
The Merge button in Excel

You will get the error message "Merging cells only keeps the upper-left cell value, and discards the other values." (Excel 2013) or "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." (Excel 2010, 2007)

The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.

Further in this article, you will find 3 ways that will let you merge data from several columns into one without losing data, and without using VBA macro. If you are looking for the fastest way, skip the first two, and head over to the 3rd straight away.

Merge two columns using Excel formulas

Say, you have a table with your clients' information and you want to combine two columns (First & Last names) into one (Full Name).

1. Insert a new column into your table. Place the mouse pointer in the column header (it is column D in our case), right click the mouse and choose "Insert" from the context menu. Let's name the newly added column "Full Name".
Insert a new column into your table

2. In cell D2, write the following formula: =CONCATENATE(B2," ",C2)

B2 and C2 are the addresses of First Name and Last Name, respectively. Note that there is a space between the quotation marks " " in the formula. It is a separator that will be inserted between the merged names, you can use any other symbol as a separator, e.g. a comma.
Excel formula to merge two columns with spaces

In a similar fashion, you can join data from several cells into one, using any separator of your choice. For instance, you can combine addresses from 3 columns (Street, City, Zip) into one.
Formula to merge address from multiple columns in to 1

3. Copy the formula to all other cells of the Full Name column. For step-by-step instructions please see how to enter the same formula into all selected cells at a time.

4. Well, we have combined the names from 2 columns in to one, but this is still the formula. If we delete the First name and /or the Last name, the corresponding data in the Full Name column will also be gone.
Combined names from 2 columns in to 1

5. Now we need to convert the formula to a value so that we can remove unneeded columns form our Excel worksheet. Select all cells with data in the merged column (select the first cell in the "Full Name" column, and then press
Ctrl+Shift+ArrowDown).

Copy the contents of the column to clipboard (Ctrl + C or Ctrl + Ins, whichever you prefer), then right click on any cell in the same column ("Full Name" ) and select "Paste Special" from the context menu. Select the "Values" radio button and click OK.
Convert the formula to a value

6. Remove the "First Name" & "Last Name" columns, which are not needed any longer. Click the column B header, press and hold Ctrl and click the column C header (an alternative way is to select any cell in column B, press Ctrl + Space to select the entire column B, then press Ctrl + Shift + ArrowRight to select the whole column C).

After that right click on any of the selected columns and choose Delete from the context menu:
Delete two Excel columns

Fine, we have merged the names from 2 columns into one! Though, it required quite a lot of effort and time :(
Excel: Merged data from two columns

Combine columns data via Notepad

This way is faster than the previous one, it doesn't require formulas, but it is suitable only for combining adjacent columns and using the same delimiter for all of them.

Here is an example: we want to combine 2 columns with the First Names and Last Names into one

1. Select both columns we want to merge: click on B1, press Shift + ArrrowRight to select C1, then press Ctrl + Shift + ArrowDown to select all the cells with data in two columns.
Select 2 columns in Excel that we want to merge

2. Copy data to clipboard (press Ctrl + C or Ctrl + Ins, whichever you prefer).

3. Open Notepad: Start-> All Programs -> Accessories -> Notepad.

4. Insert data from the clipboard to the Notepad (Press Ctrl + V or Shift + Ins)
Insert data from Excel columns to the Notepad

5. Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + LeftArrow, then press Ctrl + X.

6. Replace Tab characters in Notepad with the separator you need.

Press Ctrl + H to open the "Replace" dialog box, paste the Tab character from the clipboard in the "Find what" field, type your separator, eg. Space, comma etc. in the "Replace with" field. Press the "Replace All" button; then press "Cancel" to close the dialog box.
Replace the Tab character with Space

7. Press Ctr + A to select all the text in Notepad, then press Ctrl + C to copy it to Clipboard.

8. Switch back to your Excel worksheet (press Alt + Tab), select just B1 cell and paste text from the Clipboard to your table.
Paste merged data back to the Excel column

9. Rename column B to "Full Name" and delete the "Last name" column.

There are more steps than in the previous option, but believe me or try it yourself, this way is faster. The next way is even faster and easier :)

Join columns using the Merge Cells add-in for Excel

The quickest and easiest way to combine data from several Excel columns into one is to use Merge Cells add-in for Excel.

With the Merge Cells add-in you can combine data from several cells using any separator you like (e.g.: carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.

How to combine two columns in 3 simple steps

0. Download and install Merge Cells for Excel.

1. Select all cells from 2 columns that you want to merge, and go to the "Ablebits.com Data" tab. Click the "Merge cells" button to run the add-in.
Select 2 columns to merge and run the Merge Cells add-in

2. Select the following options on the Merge Cells dialog box:

  • Merge selected cells: row by row.
  • Separate values with: [Space].
  • Place the results to: Left cell.
  • Tick the "Clear the contents of selected cells" checkbox.

Now just click the "Merge" button.
Select the following options in the Merge Cells dialog box

3. Several simple clicks and we've got two columns merged without entering any formulas or copy/pasting.

To finish up, rename column B to "Full Name" and delete column "C", which is not needed any longer.

Much easier than the two previous ways, isn't it? :)

You may also be interested in:

How to merge rows in Excel 2010 and 2013 without losing data

Want to find more inventive tips, tricks and tutorials?

Please follow us on Google+:

23 Responses to "How to merge two columns in Excel without losing data"

  1. ANIL says:

    WONDERFUL, THANK YOU

  2. David Bola says:

    This is brilliant!

  3. Liz says:

    I typed the formula =CONCATENATE(B2," ",C2) into the cell but what do I do next. When I hit enter nothing happens and when I select columns B and C nothing.

    • Alexander says:

      In what cell do you enter the formula? Most likely, the format of that cell is set to text rather than General. Press Ctrl+1 to check this. If the format is correct (i.e. set to General), then you can send us your worksheet to support@ablebits.com and we will try to figure this out.

  4. Jack says:

    This is a great program and was able to use it on my Excel version 2007 at work.
    However at home I have the 2010 Excel Starter version that is not supported by this add on.
    Is there a chance to create an add on for this program which I would be more than happy to pay for?
    Thank You
    Jack

  5. Ashok Nillay says:

    Sir I am Having data in Excel as shown Below
    1> ASHOK KUMAR BLOCK C/9 KOLKATA
    2> RAMESH SINGH PLOT NO 123 DELHI
    3> RAM KHANA BLD NO 435 MUMBAI

    I want data which should look like
    ASHOK KUMAR
    1 BLOCK C/O
    KOLKATA

    RAMESH SINGH
    2 PLOT NO 123
    DELHI

    RAM KHANA
    3 BLD NO 435
    MUMBAI

    And so on

    These data should be merged in a single column (Each Address)

  6. n0s says:

    Incredibly intrusive advertisement. This website added to firewall blacklist

  7. waz says:

    I have various rows with numerous columns of information, I want to copy all these rows into one row, while maintaining the original format. Is that possible?

  8. Seyed MJ says:

    Very useful!
    I do not know if there is a guide or manual for novice like me!

  9. Jay says:

    Excellent !!!

  10. Abdul says:

    Hello Dear,

    I really faced such problems but could not able to solve and tried a lot to do it but not able to merge both cells with not losing the Data.
    As I Google and found this web site which helped me a lot.

    Once again thank you so much with such useful and important information its really appreciated Dear.

    Best Regards

  11. Mabula says:

    Thank you very much. The formula works very well

  12. JDvideo says:

    Great formula! However, when I merge the cells (text) it keeps the formula in the cell. When I delete the former individual cells the merged cells also are also deleted.

  13. Vasilis says:

    Thank you!!!!

  14. Asif says:

    Super Solution........

  15. Anonymous says:

    correct!!!

  16. Andy Lo says:

    I need help on collecting data down one column (F1) IF A1 has a number (scale Valve) and once C1 goes true a value of 1 it moves A1 to column F1 but also move previous value down.
    C1 switch between O (off) and 1 (on)

    Application: Excel receive signal from a PLC into a cell. Tag to a cell block and its live. So production is pulling product into a scale Weight 750 lbs. once its collected a valve is open (on command) excel records the weight drop or use.

    Yes a inventory control and if all possible time stamp with the weight entered.
    thanks

  17. Haseebullah says:

    Great!!!

    now I know how to combine multiple cells or columns together,

    thank you so much

  18. Jonna says:

    Alex,

    Quick Questions. i was trying to combine the data from two adjucent columns using your 2nd option(Combine columns data via Notepad). But i stuck up at step 5(5. Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + LeftArrow, then press Ctrl + X.).

    I do not understand what character and from where do you want me to copy to clipboard?

    My problem is i was combining user and domain. I have the data like this.
    Column A has name and Column B has domain name. i want to merge both columns adding @ symbol so, i can make it as an email address.
    Please help me. Appreciate your quick response.

    Thanks
    Jonna

  19. Anupam says:

    very nice,thanks

  20. DD says:

    I have two columns of names with some and different accounts, how can I make it in one. Can you please help me.

    They are last month patients and this month as well in two different spreadsheets. I need to copy the account numbers to the new month for the new arrival patients , but without deleting the old accounts already for the last month and I am tired of copy and pasted because the list in getting bigger and bigger about 300 names combined the old and the new arrivals. How can I "merge" the new names without losing the old one. Please help

  21. Chris says:

    Thanks!!!!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard