In this article, I will guide you on how to concatenate multiple cells quickly using different methods with examples. You’ll also learn why concatenation is often preferred over merging cells.
Why Use Concatenation Instead of Merging Cells?
Merging cells is a way to combine the contents of two or more cells into a single cell. However, merging cells can make your worksheet difficult to read and work with. For example, if you merge cells that contain different data types, the merged cell will only display the first data type.
Concatenation, on the other hand, does not merge cells. Instead, it combines the contents of two or more cells into a single string. This means that you can still see the original data types in the concatenated cell.
How to Use the CONCATENATE Function
The CONCATENATE function is a built-in function in Excel that you can use to concatenate cells. The syntax for the CONCATENATE function is as follows:
=CONCATENATE(text1, [text2], …)
Where:
- text1 is the first text string that you want to concatenate.
- text2 is the second text string that you want to concatenate.
- … is an optional list of additional text strings that you want to concatenate.
For example, the following formula would concatenate the contents of cells A1 and B1:
=CONCATENATE(A1,B1)
The results explain this.
We can also use strings in the formula itself as explained in the following example:
=CONCATENATE(A1,B1, “I am 40 years old”)
We can also use formula calculated values like dates as explained in the following example:
=CONCATENATE(A1,B1, ” Today is “,TEXT(TODAY(), “dd-mmm-yy”))
Which gives the following result:
As you can see cells are concatenated now.
An alternative to CONCATENATE is the & operator, which can make formulas shorter and easier to read:
- =A1 & ” ” & B1 & “, I am 40 years old”
- =A1 & B1 & ” Today is ” & TEXT(TODAY(), “dd-mmm-yy”)
