How to Concatenate Cells in Excel

In this article I will guide you how to concatenate multiple cells quickly using different methods using examples. You will also see that you shouldn’t merge 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.

difference between methods

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)

concatenate quick formula

The results explain this.

We can also use strings in the formula itself as explained in the following example

concatenate formula strings

=CONCATENATE(A1,B1, “I am 40 years old”)

See also  How to Multiply in Excel

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:

concatenate results

As you can see cells are concatenated now.

How to Use the “&” Operator

The “&” operator is a logical operator that you can use to concatenate cells. The syntax for the “&” operator is as follows:

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:

=A1 & B1

Concatenation is a useful way to combine the contents of two or more cells into a single string. You can use the CONCATENATE function or the “&” operator to concatenate cells in Excel.

Key Takeaways

Concatenation is the process of joining two or more values together.

You can concatenate cells in Excel using the CONCATENATE function or the “&” operator.

Merging cells is a way to combine the contents of two or more cells into a single cell, but it can make your worksheet difficult to read and work with.

Concatenation, on the other hand, does not merge cells. Instead, it combines the contents of two or more cells into a single string.