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.
    Combine columns in Excel with Merge Cells add-in

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 data from 2 columns goes into 1

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

Use CONCATENATE function to merge columns

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.
Choose the Values option in Paste Special settings

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.
    How to indicate delimiters in Notepad

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.

Ukraine flag War in Ukraine! To support Ukraine and save lives please visit this page.