Last Updated on
There are a few ways to combine columns within Microsoft Excel, you can do this either with the ampersand symbol or with the concatenate formula, both are fairly easy.
Sometimes you will find you have bits of data split across multiple columns within a spreadsheet, and you want to merge them together rather than manually going through it yourself.
For instance, you may have a column for ‘First Name’ and ‘Last Name’ columns, you may want to combine them for an easy ‘Full Name’ column in order to save time rather than doing it manually.
Here’s how to use these functions.
How To Use The Ampersand Function
This ampersand function should be really simple and easy and uses Excel’s basic coding language which can be entered in the text box above your spreadsheet that is underneath the toolbar.
Step
Click The Cell
Simply click the cell where you want this new column to be made.
Step
Equals Symbol ‘=’
Then, go into the text box to enter code in, and type the equals symbol ‘=’
Step
Ampersand Symbol ‘&’
Click the first cell you want to combine, type the ampersand symbol ‘&’.
Step
Combine
Then, click the second cell you want to combine the first with to create the new column.
Step
Enter
Then press enter.
If you wanted to combine A2 and B2, this would look like =A2&B2.
How To Use The CONCAT Function
The CONCAT function is often known more technically as concatenation, this is a word that simply indicates the action of linking things together in a series.
It’s quite similar to the ampersand function, and relies on a string of code in the same way, entered in the same text box.
Here’s how:
Step
Click On The Cell
Click on the cell where you want this new column to appear.
Step
CONCAT
Type =CONCAT(
Step
First Cell
Click the first cell you want to combine and type a comma afterwards ‘,’.
Step
Second Cell
Click the second cell you want to combine and simply close the bracket afterwards by typing the symbol.
Step
Enter
Then press enter.
How To Combine More Than Two Columns
With this simple string of code down, whether you use the ampersand or the CONCAT function, you should easily be able to get a column which combines two sets of data.
If you want to combine more than two columns, this is easily achievable with the same piece of code.
Step
Add Another Ampersand ‘&’
With ampersand, simply add another ampersand ‘&’ after the cell you have already entered and you can concatenate multiple columns to create a new column that combines all the columns listed.
It would look a bit like this =A2&B24&C2&D2 … ad infinitum
Step
CONCAT Function
Almost the same thing can be applied with the CONCAT function, simply in a different way.
Simply, once you have added your second cell to the list, enter another comma ‘,’ ,and then click the cell of the next column you want to concatenate.
It should look a bit like this =CONCAT(A2,B2,C2,D2 … ad infinitum, simply remember to close the brackets once you have added your last column.
Final Thoughts
As you can see, it’s pretty easy to combine two columns to create a newly combined column.
The two most common ways are the ampersand function and the CONCAT function, both are pretty easy but choose whichever one you prefer to do, CONCAT makes more logical sense to us, simply using commas, but there’s no reason why the ampersand function wouldn’t work for different people.
You can also combine more than one column pretty easily, no extra steps are required. You simply continue to add the columns ad finitum until you have added the columns you want to concatenate.
We hope this guide has helped you with Excel coding!
