Merge columns in excel

If you have ever worked with MS Excel, you have seen that the data is split into multiple columns in an Excel sheet. The user may sometimes need to combine the multiple columns of data into one. These columns can be blank or with data. Excel allows the users to merge the columns and the data that resides inside them.

This chapter has different ways to merge the columns in an Excel sheet. Learn the methods to merge the columns by following this chapter. Several ways are there in Excel to merge the columns of an Excel sheet.

How to merge columns?

When you want to combine the multiple cells data, you can do it by row-by-row or column-by-column. In this chapter, we are working on the concept of merging the Excel columns. The Excel columns can be merged to combine the data stored inside them. You can do it manually or using Excel functions and other methods.

If you want to combine the columns, you do not need to do it manually. You can do it using simple and quick Excel formula. Concatenation function and ampersand (&) symbol are the ways to combine the columns data.

Need to merge column

When the Excel users need to merge a cell with next to another cell, they can merge it one by one. But when the user wants to merge the multiple cells of a column to another column cells, they can merge them by column rather than cell one by one.

Merging the multiple cells one by one takes too long to merge them. Thus, merge the cells by column so that the multiple cells can merge at once.

There might be many situations where you need to merge the columns, such as first name and last name, into one. Another is combining the city, state, country, and Postal code into one Address column.

Excel provides the Merge cells option to merge the cells data. But it only keeps the upper left value between the selected value and discards other. So, the data will lose by using this method.

Example 1: Combine cells using Merge & Center

This is a simple example of merging two columns of data into one column. This method is good, but the data is lost if you merge the cells with data. It is useful for merging the columns before inserting the data. Following is a set of data split into multiple columns.

Step 1: See the following data having two columns named First name and last name. We will merge these columns data into one, i.e., Name.

Step 2: Select the A2 and B2 cells to merge and click the Merge & Center, then choose to Merge Cells inside the Home tab.

Step 3: A error message will appear that says only the upper left value will keep and other values will be discarded. If you still want to merge the cells, click OK.

Step 4: See that the selected cell has been merged, but the data has been lost. Only first name is kept and the last name has been discarded.

Example 2: Combine columns using &

Another method to merge the two or more columns is using & (ampersand symbol). Excel users can use it to merge the two column's data. The resultant value of merged columns is stored in a new column. The data does not lose when you combine the cells data using this method.

This method's main drawback is - it cannot combine multiple cells of two columns at once. It merges cells only one-by-one means that the entire column does not merge at once. You have to use this method multiple times to combine the entire columns data.

With the help of the below steps, you will learn & operator can be used to combine columns.

Step 1: We have the first name and last name in column A and column B. We will merge these columns of data into a new column.

Step 2: In a new column C, write the below formula in C2 cell.

=A2&B2

Step 3: Now, when you hit the Enter key, you will get the data of cell A2 and cell B2 data combined in C2 cell.

Step 4: By applying this formula to the entire column one by one, you will get the merge result for column A and column B in column C.

It will not be calculated at once. The Excel users need to explicitly apply this formula for all cells to get the entire columns merge.

You can also put the blank space between the combined data so that it becomes readable. Then you have to use this formula as =A2& " " &B2. This will add the space between the merged column text.

Step 5: Now, if you delete the First name (A) and Last name (B) columns. You will get the following #REF! Error like in the below screenshot.

This error occurs because the combined data is obtained from column A and column B references.

Step 6: Before deleting the First name and Last name column, select the resultant column and copy the data using Ctrl+C command.

Step 7: On the same column, right-click on column C and select Paste Values (V) under the Paste Options.

Step 8: Now, you can delete the reference columns from which you get the merged data in column C. See the result after deleting.

Example 3: Combine columns using CONCAT function

In the above example, we had used & operator to combine the two cells data to merge the columns. Excel also offers CONCATENATE() function using which the user can merge the cells data.

CONCATENATE() function works in a similar way as ampersand (&) operator. You can not only combine only two cells but more than two cells into one. Let's see how this function works to combine the columns data.

Perform the given steps below on your data:

Step 1: We have the first name and last name in column A and column B. We will merge these columns of data into a new column.

Step 2: In a new column C, write the below formula in C2 cell.

=CONCATENATE(A2, B2)

Step 3: Now, when you hit the Enter key, you will get the data of cell A2 and cell B2 data combined in C2 cell.

The formula is placed in one cell. You can auto-populate it by dragging this formula in the below cells.

Step 4: By applying this formula to the entire column one by one, you will get the merge result for column A and column B in column C.

Besides all the above methods, there are also online tools like Kutools, using which you can merge the columns.

Why prefer CONCAT or & to merge columns?

• CONCATENATE() and & operator allow the users to combine the cells data.
• The merge result receives into a new and separate column.
• The data of the targeted cells do not lose after merging as it lost while using Merge & Center.
• Use them if you do not want to lose the data after merge.
• More than one column can be combined.