Video: Three ways to merge columns in Excel without losing any data

This video will show you how to merge two columns in Excel using formulas, special add-in, or an unusual workaround.

We often need to combine columns in Excel to get names, addresses, and similar records in one cell. As there are no built-in tools for merging values from multiple columns, see three ways you can go in this video.

3 ways to combine columns in Excel without losing data: video transcript

In this video you'll see three ways of merging multiple columns in Excel without losing any data.

Let's look at a common example: here I have name parts in different columns. When you need to bring values like this together, you won't find a standard tool for this task in Excel. If you try the most obvious way and click the button "Merge and center", you will see a warning that it's going to merge the cells and delete all your data except for what you have in the top-left corner.
There are several ways you can avoid this and combine the records.

The easiest and quickest solution would be using a special add-in developed for this task. It takes a minute to install and you'll find it on the Ablebits Data tab. Let's start it by clicking on the Merge Cells icon. It offers just three simple settings:

  1. First you pick the way you want to join the selected values. As we want to combine records in each row, we choose "row by row". If you doubt it, you can check this schematic hint that shows how exactly it joins the data.
  2. Next you need to enter the delimiter for the values. I'm going to choose the standard space for the names, but you can enter any custom characters in this field.
  3. Then you define where to place the results: into the left or to the right column.

All the check-boxes below are optional, but let me show you the flexibility they provide:

  • If you plan to delete the original values after combining them, you can use this option to clear the merged columns.
  • You can use the second box if you want to join the cells in addition to the values. I'm going to show you how this is going to look: each cell takes two columns.
  • If you have some blanks in the range, like here there are some authors with just the last name, you can avoid getting extra delimiters one after the other by ticking off this option.
  • Choose to wrap text if your destination cells are too small to show the resulting values.
  • Finally, as you can't cancel the changes made by add-ins, it's always a good idea to create a backup copy of your sheet.

That's it, click Merge and get full names from different columns.

The second way you can go is use the concatenate function. First you will need to create an additional column for the formula.

After adding a header, start typing the equals sign in the next cell, enter CONCATENATE, open the brackets, and you will see a hint at this point to enter the text you want to join. Of course for our universal solution this means we'll enter references to the cells we want to combine, so we write
=CONCATENATE(B2," ",C2)

The formula allows you to add as many values as you need. It also gives you the flexibility to use different delimiters, and combine data even from non-adjacent cells. Say, you can
=CONCATENATE(B2," ",D2,":",F2)

You can drag this formula down the column, or if you have a large table, then point to the lower right corner of the cell until you see a black cross instead of the mouse pointer, and double-click on it. This will copy the formula to all cells of the column.

One more formula you can use is very simple: enter ampersand between the values and delimiters. For example:
=B2&" "&C2&": "&E2

You can leave the formulas to update the results as the records change, or you can turn them into values and delete the original columns from your table. To do this, select your resulting column, you can use the shortcuts: press Ctrl+Shift and arrow down at the same time. Then press Ctrl+C or right-click on any cell in the column to copy the cells, then right-click again and select the option "Paste Special", choose Values, and click OK. Now you can remove the original columns without affecting the results.

Now, there is one more way to merge your data.

If the columns are next to one another, and if you can use the same delimiter for the values, you can use the Notepad workaround. First you need to select the records and copy them to Notepad. Open Notepad and paste the copied values, you can press Ctrl +V to do that.

Here you can use the standard Replace dialog to enter a different delimiter:

  • Select the character between the pasted values and copy it, paste the tab character into the Find what field,
  • Enter the delimiter you prefer to have into Replace with, click Replace and close the box.

Now you only need to paste the records back to Excel: select and copy them in Notepad, you can press Ctrl+A to select them all and Ctrl+C to copy them, switch to your worksheet and paste the results.

That's it! To sum it up: you can use three ways to combine records from multiple columns without losing any data. You can use the formulas and then convert the results to values, you can copy your data to Notepad, replace the delimiter, and paste the results back to your table, or you can run the Merge Cells add-in. No matter what option you choose, you will get joined values.

You can find the link to the add-in in the description to this video. You can get Merge Cells as a separate add-in, or as a part of Ultimate Suite with a 15% discount if you use the coupon code Preview.

Feel free to share your questions and comments if there is anything you'd like to clarify, and subscribe to our channel for more tips and tricks on Microsoft Excel.

2 Responses to "Video: Three ways to merge columns in Excel without losing any data"

  1. Markandan says:

    How do i get results show the following values:
    A B C
    10 10 =CONCATENATE(A1,",",B1,",",C1) RESULTS 10,,10
    10 20 30 =CONCATENATE(A1,",",B1,",",C1) RESULTS 10,20,10
    10 30 =CONCATENATE(A1,",",B1,",",C1) RESULTS 10,30,

    ABOVE IT IS SHOWING COMMA IN BETWEEN TWO VALUES OR AFTER. I DONT WANT THE COMMA WHERE THE VALUE IS NOT THERE

    • Hello Markandan,

      You can ignore blanks by including the IF condition into your CONCATENATE formula:
      =CONCATENATE(A1,IF(B1<>"",","&B1,""),IF(C1<>"",","&C1,""))

      You can also use the following formula:
      =A1&IF(B1<>"",","&B1,"")&IF(C1<>"",","&C1,"")

      If you use the Merge Cells add-in, then simply select the check-box "Skip empty cells".

      If you have the latest version of Excel 2016, you can also try the new TEXTJOIN function, its second parameter allows you to ignore blanks:
      =TEXTJOIN(",",TRUE,A14,B14,C14)

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!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals