You don’t have to manually combine data if you are using Excel. You can instead use an easy formula to combine columns.
This article will show you how to combine multiple columns in Excel with the CONCAT function or the ampersand symbol. We will also give tips on formatting the data to make it look exactly as you want.
Excel: How to combine columns
There are two ways to combine columns in Excel. The ampersand symbol or The concatenate formula. The ampersand method is faster and more efficient than the concatenate. However, you can choose the one that is most comfortable for you.
1. How to combine Excel columns with the Ampersand Symbol
- Click on the cell you wish to send the combined data.
- Type =
- Click on the first cell that you wish to combine.
- Type and
- Click on the second cell that you wish to combine.
- Use the Enter button.
If you want to combine cells A2 & B2, for example, the formula would be =A2&B2
2. How to combine Excel columns with the CONCAT Function
- Click on the cell you wish to send the combined data.
- Type =CONCAT (
- Click on the first cell that you wish to combine.
- Type ,
- Click on the second cell that you wish to combine.
- Type
- Use the Enter button.
If you want to combine cells A2 and B2, for example, the formula would be =CONCAT (A2,B2)
This formula was used to be CONCATENATE rather than CONCAT. The former allows you to combine two Excel columns, but it’s depreciating. You should use the latter to ensure compatibility between current and future Excel versions.
How to combine more than two Excel cells
Either method can be used to combine as many cells you wish. Just repeat the format as follows:
- =A2&B2&C2&D2 … etc.
- =CONCAT(A2,B2,C2,D2) … etc.
How to combine the entire Excel column
Once you’ve placed the formula into one cell, it is possible to use this to populate the rest. You don’t have to manually enter each name of the cell you want to combine.
Double-click in the bottom-right corner to do this. Left-click the bottom-right corner and drag it down the column. This Excel AutoFill trick is used to quickly build spreadsheets.
Tips for Formatting Combined Columns in Excel
Excel combined columns can contain text, numbers, and dates. It is not always possible to combine cells without formatting.
Here are some tips to help you format combined cells. We’ll use the ampersand method in our examples. However, the logic for the CONCAT formula is the same.
1. How to Create a Space Between Two Combined Cells
You would need a space between columns “First name” and “Last name”.
This formula is: =A2&&” “&B2
The formula is to add A2’s contents, then add a space and then add B2.
You don’t need to use a space. It doesn’t have to be a space.
2. How to add text to a combined cell
The combined cells do not have to include the original text. You can add any additional information.
Let’s suppose cell A2 holds someone’s name (e.g. Marge Simpson), and cell B2 contains their age. This can be made into a sentence such as “Marge Simpson is 36 years of age.”
To accomplish this, the following formula would be used:
Additional text is wrapped with speech marks, followed by an and. When referencing a cell, speech marks are not necessary. Be sure to place the spaces correctly. For example, “The character” with a space at its end.
3. How to correctly display numbers in combined cells
You’ll notice that the combined cells strip any formatting in your original cells containing formatted numbers such as dates or currency.
This can be solved with the TEXT function. You can use it to specify the format you need.
Let’s suppose cell A2 has the name of someone (e.g. Marge Simpson) while cell B2 has their birth date (e.g. 01/02/1980).
You might combine them by using this formula: =A2&” was created on “&B2
But, it will output: Marge Simpson was conceived on 29252. Excel converts the correct formatted date of your birth into a simple number.
Excel can be told how it wants the merged cells to look by using the TEXT function. Like so: =A2&” was born on “&TEXT(B2,”dd/mm/yyyy”)
This is slightly more complex than the other formulas so let’s break this down:
- =A2– Merge cell A2.
- and” was born on. Add the text “was birthed on” to both sides.
- and &TEXT: Add something to the text function.
- (B2,”dd/mm/yyyy”) merge cell B2 and apply the format dd/mm/yyyyyyyyyyyyyyyyyyyyyyy to that field.
The format can be changed to suit the number. $#,##0.00 will show currency with a thousand separators and two decimals. #? will turn a decimal to a fraction. H? MM AM/PM will show the time.
More examples and information can be found on the Microsoft Office Text function support page.
How to remove the formula from Combined Columns
Clicking on a cell in the combined column will reveal that it still contains the formula, e.g. =A2&” &B2, rather than plain text (e.g. Marge Simpson).
This is not a bad thing. This means that the combined cell will update automatically to reflect changes in the original cells (e.g. A2 and B2).
It does however mean that if you delete any of the original columns or cells, your combined cells will be broken. You might consider removing the formula from the combined column to make it plain text.
To do this, click Copy on the header of the combined columns to highlight it.
Next, click the header of the combined columns again. This time, under Paste Options, select Valu. The formula is now gone and you have plain text cells you can edit.
How to merge columns in Excel
You can merge columns in Excel instead of merging them. This will combine multiple horizontal cells into one. The values of the upper-left cell are retained and the rest are discarded by merging cells.
Select the columns or cells you wish to merge. Click the Merge & Center link in the Ribbon.
This article explains how to merge and unmerge Excel cells. You can merge Excel sheets and files.
Excel Saves Time
You now know how to combine columns within Excel. This will save you a lot of time and reduce the amount of work required to combine columns manually. This is just a one-way formula that can be used to speed up Excel tasks.
