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

9 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?

Post a comment



Please note that we are on holiday until Sep-16, so we won’t be able to reply to your comments for a while, sorry.