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. 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 one 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 CONCATENATE formula:

    =CONCATENATE(B2," ",C2)

    In Excel 2016 - Excel 365, you can also use the CONCAT function for the same purpose:

    =CONCAT(B2," ",C2)

    Where 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. Or see how to enter the same formula into multiple cells at once.
  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 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 did require some effort :) 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 you want to merge: click on B1, press Shift + Right Arrrow to select C1, then press Ctrl + Shift + Down Arrow 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 + Home, 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 included with our Ultimate Suite for Excel.

With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, 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

  1. Download and install the Ultimate Suite.
  2. Select all cells from 2 or more columns that you want to merge, go to the Ablebits.com Data tab > Merge group, and click Merge Cells > Merge Columns into One. Select 2 columns to merge and run the Merge Cells add-in
  3. In the Merge Cells dialog box, select the following options:
    • How to merge: columns into one (preselected)
    • Separate values with: choose the desired delimiter (space in our case)
    • Place the results to: left column
  4. Make sure the Clear the contents of selected cells option is ticked and click Merge. Select the following options in the Merge Cells dialog box

That's it! A few simple clicks and we've got two columns merged without using any formulas or copy/pasting. Columns merged using excel addin

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? :)

258 comments

  1. Hi thank you for the initiative, im still stuck, assuming the name in column B to appear below each name in Column A , how can i be guided on this

  2. Thank you for this article, the CONCATENATE formula was excellent, I used - for my delimiter

  3. Hi there, When I merge two columns using the above Ablebit steps it copies the first two combined cells down the whole chart. Could you assist?

  4. This was a life saver! Thank you for the clearly written and great information.

  5. How to merge cells horizontally one column of the cells is in Bold..how do I keep the Bold? i"m using formula =F1: F6 & G1:G6 which works but all of colum F is in bold and once merged I loose the Bold.How can I merge and keep the bold?

  6. You had me until these steps, can you clarify what these mean? I realize this is enabling the data in the two columns to come together into one and then we are copying/pasting back into the worksheet. But I don't even really know what tab character means.

    Copy tab character to clipboard. Press Tab right in Notepad, press Ctrl + Shift + Home, then press Ctrl + X.
    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.

    1. Hi! Just follow all the steps in these instructions. You first type a tab character in Notepad, then use Ctrl + X to place it on the clipboard.

  7. I am trying to format time cells to display yyyy/mm/dd hh:MM:ss from a dd/mm/yyyy hh:MM:ss format, and excel and the function is functionless??? I have spent about 4 hours trying this. Can anyone help?

  8. I am trying to merge a tab that is a in date format (e.g. 5/11/2020) with a column that is a numeric value. It automatically converts the date format into a numeric value and doesn't preserve the format. Is there a way to do so with it keeping the date format? Thank you.

    1. Hello!
      When you concatenate date and number, you get a text string. Use the TEXT function to store the date format in text.
      For example,

      =TEXT(A1, "dddd d mmm, yyyy") & " " & B1

  9. This is great, thanks. Is there a way to insert a comma between the contents of the two merged cells?

    1. Hello!
      If you merged cells using a formula, use a comma instead of a space.

      =CONCATENATE(B2,",",C2)

      Also use a comma instead of a space in the other methods that are suggested in the article above.

      1. I have been trying to use ‘’ sign but it’s not working. Please which sign is that exactly I can’t seem to find it in the keyboard.

  10. Hi
    I have followed the instructions for merging 2 columns into one via notepad but cannot seem to get past dot point 5.
    I can's seem to work out how to tab right in notepad.
    Thanks

  11. Thanks very much, It really helped me out.

  12. Help how to I merge Columns in excel to produce a single column

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  13. thank you very much but please low the price for yor ablebits tab

  14. This literally saved the day for me. Thank you so much!

  15. LIFESAVER!!! THANK YOU!

  16. A round of applause for your mind blowing article. Much thanks to you. Fantastic.

  17. Very helpful - Thank you very much

  18. Thank you, I used the CONCATANATE command when I had to merge two or three columns of data together. I haven't had to do that in over three years and I'm not in the office anymore with support. I was going to try a grep command, but forgot that too. Thanks again!

  19. Thank You. This was very helpful information and allowed me to resolve an issue i had with combining two sets of data into one.

  20. THANK YOU,

    This saved me tons of time.

    Just remember to have your column formatted as General and not text.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)