If you’re using Excel and have data split across multiple columns that you want to combine, you don’t need to manually do this. Instead, you can use a quick and easy formula to combine columns.
We’re going to show you how to combine columns in Excel using the ampersand symbol or the CONCAT function. We’ll also offer some tips on how to format the data so that it looks exactly how you want it.
How to Combine Columns in Excel
There are two methods to combine columns in Excel: the ampersand method and the concatenate formula. In many cases, using the ampersand method is quicker and easier than the concatenate formula. That said, use whichever you feel most comfortable with.
1. Combine Columns with the Ampersand Symbol
- Click the cell where you want the combined data to go.
- Type =
- Click the first cell you want to combine.
- Type &
- Click the second cell you want to combine.
- Press the Enter key.
For example, if you wanted to combine cell A2 and B2, the formula would be: =A2&B2
2. Combine Columns with the CONCAT Function
- Click the cell where you want the combined data to go.
- Type =CONCAT(
- Click the first cell you want to combine.
- Type ,
- Click the second cell you want to combine.
- Type )
- Press the Enter key.
For example, if you wanted to combine cell A2 and B2, the formula would be: =CONCAT(A2,B2)
This formula used to be CONCATENATE, rather than CONCAT. While the former still works, it is being depreciated and you should use the latter to ensure compatibility with current and future versions of Excel.
More Than Two Cells
You can combine as many cells as you want using either method. Simply repeat the formatting like so:
- =A2&B2&C2&D2 … etc.
- =CONCAT(A2,B2,C2,D2) … etc.
Combine the Entire Column
Once you have placed the formula in one cell, you can use this to automatically populate the rest of the column. You don’t need to manually type in each cell name that you want to combine.
To do this, double click the bottom right corner of the filled cell. Alternatively, left click and drag the bottom right corner of the filled cell down the column.
Tips on How to Format Combined Columns
Your combined Excel columns could contain text, numbers, dates, and more. As such, it isn’t always suitable to leave the cells combined without formatting them.
To help you out, here are various tips on how to format combined cells. In our examples, we’ll refer to the ampersand method, but the logic is the same for the CONCAT formula.
1. How to Put a Space Between Combined Cells
If you had a “First name” column and a “Last name” column, you would want a space between the two cells.
To do this, the formula would be: =A2&” “&B2
This formula says to add the contents of A2, then add a space, then add the contents of B2.
It doesn’t have to be a space. You can put whatever you want between the speech marks, like a comma, a dash, or any other symbol or text.
2. How to Add Additional Text Within Combined Cells
The combined cells don’t just have to contain their original text. You can add whatever additional information you want.
Let’s say cell A2 contains someone’s name (e.g. Marge Simpson) and cell B2 contains their age (e.g. 36). We can build this into a sentence that reads “The character Marge Simpson is 36 years old”.
To do this, the formula would be: =”The character “&A2&” is “&B2&” years old”
The additional text is wrapped in speech marks and followed by an &. When referencing a cell, these don’t need speech marks. Note that you also need to include where you want the spaces to be—hence “The character ” with a space at the end, rather than “The character”.
3. How to Correctly Display Numbers in Combined Cells
If your original cells contain formatted numbers like dates or currency, you’ll notice that the combined cell strips the formatting.
You can solve this with the TEXT function, which you can use to define the required format.
Let’s say cell A2 contains someone’s name (e.g. Marge Simpson) and cell B2 contains their date of birth (e.g. 01/02/1980).
To combine them, you might think to use this formula: =A2&” was born on “&B2
However, that’ll output: Marge Simpson was born on 29252. That’s because Excel converts the correctly formatted date of birth into a plain number.
By applying the TEXT function, you can tell Excel how you want the merged cell to be formatted. Like so: =A2&” was born on “&TEXT(B2,”dd/mm/yyyy”)
That’s slightly more complicated than the other formulas, so let’s break it down:
- =A2 – merge cell A2.
- &” was born on “ – add the text “was born on” with a space on both sides.
- &TEXT – add something with the text function.
- (B2,”dd/mm/yyyy”) – merge cell B2, and apply the format of dd/mm/yyyy to the contents of that field.
You can switch out the format for whatever the number requires. For example, $ #,##0.00 would show currency with a thousands separator and two decimals, # ?/? would turn a decimal into a fraction, H:MM AM/PM would show the time, and so on.
More examples and information can be found on the Microsoft Office TEXT function support page.
How to Remove the Formula From Combined Columns
If you click a cell within the combined column, you’ll notice that it still contains the formula (e.g. =A2&” “&B2) rather than the plain text (e.g. Marge Simpson).
This isn’t a bad thing. It means that whenever the original cells (e.g. A2 and B2) are updated, the combined cell will automatically update to reflect those changes.
However, it does mean that if you delete the original cells or columns then it will break your combined cells. As such, you might want to remove the formula from the combined column and make it plain text.
To do this, right click the header of the combined column to highlight it, then click Copy.
Next, right click the header of the combined column again. This time, beneath Paste Options, select Values. Now the formula is gone and you’re left with plain text cells that you can edit directly.
How to Merge Columns in Excel
Instead of combining columns, you can also merge them. This will turn multiple cells across into one cell. Merging cells only keeps the values from the upper-left cell and discards the rest.
To do this, select the cells or columns that you want to merge. In the Ribbon, on the Home tab, click the Merge & Center button (or use the dropdown arrow next to it).
For more information on this, read our article on how to merge and unmerge cells in Excel.
How to Save Time When Using Excel
Now you know how to combine columns in Excel, you can save yourself lots of time—you don’t need to combine them by hand.
If you want some more tips, here are more ways to save time in Microsoft Excel.
Read the full article: How to Combine Two Columns in Excel Easily and Quickly
