Merge Columns In Excel

Why Would You Want To Merge Columns In Excel?

All you have to do is have a quick look in the Microsoft Office related forums on the internet to know that many people don't know how to merge columns in Excel. Often people receive advice that just doesn't work. Worry no longer; here is the definitive guide to the excel merge columns mystery. But first, why would you want to merge columns?

Imagine that you had a column of first names and then another column containing second names of people who had taken a test. You are now required to format a column that contains the full name of each candidate. You know how to merge two cells so that the result contains the full name for just one person, but you need to apply that formula to the names in all rows. This is where merging columns in Excel comes in.

How Do You Want To Merge Columns?

The first question you have to ask yourself is what kind of column merge you want to perform? The example given above is called concatenation: if cell A1 contains "Joe" and cell B1 contains "Bloggs", then the concatenation of the two is "JoeBloggs". Note that you would have to take care to insert a space between the two names yourself, but this is easy to do.

However, when some people say "merge", they mean "add". It may be that you are required simply required to add the contents of two columns together. Confirm the requirements first!

Merging Columns In Excel

Now that we've clarified what merging columns actually means, we can explore how to do it. The first step is to perform the merge for the first cells. Let's go back to our first example and suppose that we are merging column A that contains first names with column B that contains second names. We'll put the merged columns into column C. To merge cell A1 with cell B1 we woul type the following into cell C1:

=A1&" "&B1

Remember to insert the space as shown. When you press enter, cell C1 will contain the full name for that first row. Believe it or not, that was the hardest bit. We now need to apply that formula to the remaining cells in those columns. To do this, select the first merged cell and then hover the mouse over the bottom right corner of the cell until you see the plus sign. Drag the cursor downwards until the selection includes all the remaining cells in the merged column. When you release the mouse, the merged column should contain the dat in the first and second columns merged.

Using The Concatenate Function

You can also use the CONCATENATE function in Excel to merge two piece of data together. The syntax is as follows:

=CONCATENATE(A1," ",B1)

This formula would be useful for merging first and last names as it also inserts a space between the two. With these two methods of cancatenating covered, let's move on to merging multiple columns.

Merging Multiple Columns In Excel

Merging mutiple columns in Excel is a simple extension of merging two columns. Suppose you need to merge columns A, B, C, D and E and put the result in column F. First of all you would need to type one of the following in cell F1:

• =A1&B1&C1&E1
• =CONCATENATE(A1,B1,C1,D1,E1)

Pressing enter merges the data into cell F1. As we did before, make cell F1 active, hover over the bottom right corner and then drag the cursor downwards.