Merge Two Columns in Excel

When you work with multiple columns of data, which is usually the case, sometimes it may be smart to simplify data.

For example, joining the first and the last name into a single column, or combining the city name with the state and separating it by a comma.

Combining Two Columns With a Formula

These are the steps you have to follow to combine two columns into one:

1. Right-click the column letter C.
2. Click Insert.
3. There will be a new empty column between the Last Name and Address.
4. Enter “Name” into the column header.

5. Enter this formula into cell C2:

It combines the first and the last name into a single string separated by a space. The join operator (&) is used in this case.

6. Autofill the rest of the cells in the column.

Now, you have two columns combined.

If you try to remove columns A (First Name) and B (Last Name), the data inside the Name column will return the reference error (#REF!).

This happens because there are no values inside the Name column, bur formulas with references to columns A and B.

Before you remove these two columns, you have to convert these formulas into values.

To do it, select cells from C2 to C11.

Copy them (Ctrl + C), right-click inside the range, and select paste as values.

Now, if you click one of these cells, you will notice that there is no formula, just text.

You can delete the first and the last name column, without causing the reference error.

Concatenate Two Columns with Delimiter

Similarly, you can combine city names with the state, this time using a delimiter. Most often, you are going to use space, hyphens, dash, and commas. In this example, we are going to use the last option.

Create a new column E and name it “City”.

Insert the following formula into cell E2:

Convert cells in column E into values as you did for names. Delete the old City and State columns.

Using Function to Merge Columns

If you don’t want to use an ampersand (&) to join columns, there is another option.

You can merge two or more columns with the CONCAT function, which is the younger brother of the older CONCATENATE function. This function has been replaced in Excel 2016 but is still present in Excel 365 for backward compatibility.

This function, as the name suggests, merges multiple strings into one.

So, this formula that joins two strings can be replaced with the one using the CONCAT function:

They work the same. The decision of using one or the other is just personal taste. If you like, you can even merge both ways:

Combining Columns with Notepad

Sometimes using additional apps such as Notepad can be very useful thanks to its simplicity. You can also use this program order to merge columns quickly and then paste them into Excel.

Let’s have a look how it looks like in our case.

First, create a new column Name as you did before.

Copy cells from columns A and B (A2:B11).

Paste them into Notepad.

The first and the last names are separated by a tab. Select it and copy it.

Press Ctrl + H to open the Replace window.

Paste the tab, that you copied earlier, into the Find what textbox.

In Replace with, enter space.

Click Replace All, to change all tabs into spaces.

Copy these values and paste them into the column you’ve previously created.

Now, you can remove columns A and B because data is already pasted as values, so there is no need to convert it.