Here, I’ll show you how you can concatenate columns with or without a separator.
We are going to use the following example.
Combine multiple columns into one
In order to concatenate multiple columns into a single column, you have to use the CONCATENATE function.
1 |
=CONCATENATE(A2,B2,C2,D2) |
Alternatively, you can use this simple formula, where every cell is merged via the & operator.
1 |
=A2&B2&C2&D2 |
Both formulas are going to return the same result.
The Concatenated column is not the most readable one.
Combine multiple columns into one (with separator)
In order to make the example cleaner, we are going to add a separator (comma + space) between cells values.
1 |
=CONCATENATE(A2,", ",B2,", ",C2,", ",D2) |
This time we can also drop the CONCATENATE function and write it as the following formula.
1 |
=A2&", "&B2&", "&C2&", "&D2 |
Combine multiple columns with date
You can also combine columns with the date. But it’s become more tricky here. Date in Excel is just a number formatted as a date.
If you use the following formula, the result won’t be what we expected.
1 |
=CONCATENATE(A2," ",B2,".") |
And the same situation occurs with the second method.
1 |
=A2&" "&B2&"." |
In order to achieve the desired result, we have to first format number as date and then combine the cells.
The first method.
1 |
=CONCATENATE(A2," ",TEXT(B2,"mm/dd/yyyy"),".") |
The second method.
1 |
=A3&" "&TEXT(B2,"mm/dd/yyyy")&"." |
The result is what we expected.