How to merge two columns in Excel without losing data

In this short article, you will learn an effective method to combine multiple columns in Excel so that all the necessary data is preserved.

Let’s say you have a spreadsheet that contains two columns stacked on top of each other. For example, you need to merge the columns with the first and last name into one or combine several columns with the captions “street”, “city”, “zip code” into one – “address of residence”, in order to separate the values ​​with a comma. How can this be done?

Unfortunately, Excel does not have a built-in function that allows you to do what we said above. Of course, there is a “Merge Cells” button and others like it, but the values ​​uXNUMXbuXNUMXbare lost.

The following warning will be shown:

1. Excel 2013 will say that only the value in the top left cell of the range will be stored in the merged cell. All other data will be deleted. 
2. Excel 2010 and below will display a warning that has the same meaning but slightly different wording.

This imposes serious restrictions on the use of the program and makes it impossible to effectively perform the tasks.

Next, you will learn 3 ways to combine data from multiple columns into one so as not to lose data (without using macros). If you want the easiest way, you can skip the first two methods and learn only the third.

Combining multiple columns using a formula

Let’s say you have a table with information about customers, and the boss set the task to merge columns «First Name» и «Last Name» in one «Full name». To do this, you must perform the following sequence of actions:

1. Insert an extra column into the table. To do this, place the cursor on the column heading (in our case, it is column D) and right-click on it. A context menu will appear in which you need to select the item “Insert”. Let’s call the resulting column «Full name», which translates as “Full name”.
2. Next, in cell D2, you need to write the following formula: =CONCATENATE(B2;” “;C2) . In our case, B2 is the address of the cell with the first name, and C2 is the address of the cell with the last name. You can also see the space icon between quotes there. At this point, a separator is written, placed between the contents of the first and second cells. If you need to separate the elements with a comma (for example, to specify the full address), you can write it as the second argument to the function. You can combine several columns into one using any other separator.
3. This formula is then copied to all other cells in that column. To understand how to do this, you can use the instruction “How to insert the same formula in all selected cells” (see the article on our website).
4. So the two columns have been merged into one, but it’s still a formula. Therefore, if you delete the first or last name column, the information in the full name column will also be lost.
5. Now we need to change the formula in the cell to a ready-made value so that we can remove extra columns from the document. To do this, select all cells with information of the combined column (select the first cell in column D in our case and press the key combination Ctrl + Shift + Down Arrow; then you need to copy the data from the columns and right-click on any cell in this column and click on “Paste Special”. Select the item on the left side of the window “Values” and press the key “OK”.
6. Now you can delete the original columns. To do this, you need to click on the name of column B, and then press the Ctrl key and do the same with column C. This is necessary to select all these columns. You can also use the key combination Ctrl + Space to select the entire column, and then press Ctrl + Shift + Right Arrow, copy the selection to the adjacent column C. Next, the context menu opens by right-clicking on one of the selected columns, and then you need to click on the item “Delete”.

Now the names from several columns have been merged into one. Although it takes time, the very sequence of actions is clear even to a beginner.

Connecting columns using Notepad

This method will take less time to complete than the previous option, and no formulas need to be used. But it is only suitable for connecting columns that are adjacent, and also if one separator is used (for example, only a comma).

Let’s say we need to join the same columns as in the previous example. To do this, follow these instructions:

1. Select all columns to be connected. To achieve this task, select cell B1 and press the key combination Shift + Right Arrow. Then the selection will also cover the neighboring cell C1. After that, you need to press the combination Ctrl + Shift + Down Arrow to move the selection to the very end of the columns.
2. Transfer data to the clipboard (in other words, copy them). To do this, press the key combination Ctrl + C or use any other method.
3. Launch the Notepad program, which comes standard with Windows. It’s in the Start menu. The exact path may vary slightly depending on the operating system version. But finding a program is not difficult in any case. 
4. Transfer the copied data to Notepad using the key combination Ctrl + V.
5. Press the Tab key and copy this character.
6. Next, replace this character with any other using the dialog box “Replace”.
7. Select all text, copy it.
8. Go back to Excel, select just one cell (B1 in our case) and paste the text into the table.

It remains only to rename the column.

How to merge two columns in 4 easy steps?

To do this:

1. Download special addon. 
2. Select two columns and go to the “Ablebits.com Data” tab. Click on the “Merge Cells” button.
3. Select the options shown in the picture.
4. A few simple steps, and we get an excellent result without additional manipulations.

To finish, simply rename column B to “Full Name” and remove column C, which is no longer needed.