CONCATENATE function

The CONCATENATE function in Excel is used to join two or more text strings together into one string. Although it has been replaced by the newer CONCAT function in newer versions of Excel (Excel 2016 and later), the CONCATENATE function is still available for compatibility with older Excel files.

Syntax

=CONCATENATE(text1, [text2], ...)

Parameters

• text1 (required): The first text string (or cell reference) that you want to concatenate. This can be a single text value, a number, or a cell reference.
• text2, … (optional): Additional text items to concatenate. You can provide multiple text arguments, and Excel will combine them into one string. You can concatenate up to 255 text arguments.

Key Points

• The CONCATENATE function joins the text values together, but does not insert any delimiters (like spaces or commas) between the values unless you specify them yourself.
• It can handle text, numbers, and cell references.
• For newer versions of Excel (2016 and later), it’s recommended to use the CONCAT function instead, as it is more flexible and supports range references.

Example Usage

1. Basic Concatenation

To concatenate the text strings “Hello” and “World” into one string:

=CONCATENATE("Hello", " ", "World")

This will return:

Hello World

The space (" ") is included between “Hello” and “World” to separate the words.

2. Concatenating Cell References

If cell A1 contains “Good” and cell B1 contains “Morning”, you can concatenate the values in those cells:

=CONCATENATE(A1, " ", B1)

This will return:

Good Morning

3. Concatenating Numbers

You can also concatenate numbers along with text. For example:

=CONCATENATE("The total is ", 150)

This will return:

The total is 150

4. Concatenating Multiple Cells

If you want to concatenate the values from multiple cells, such as A1, B1, and C1, you can do so like this:

=CONCATENATE(A1, B1, C1)

If A1 contains “John”, B1 contains “Doe”, and C1 contains “Smith”, this will return:

JohnDoeSmith

You can add spaces or other separators between the concatenated values:

=CONCATENATE(A1, " ", B1, " ", C1)

This will return:

John Doe Smith

Notes

• No Automatic Delimiters: The CONCATENATE function does not automatically insert any separators. If you want spaces, commas, or other separators between the text, you need to include them manually in the formula (e.g., " " for a space, ", " for a comma and space).
• Limitations: CONCATENATE can handle up to 255 text arguments. In most practical cases, you’re unlikely to hit this limit.
• Deprecated in Newer Versions: In newer versions of Excel (Excel 2016 and later), the CONCAT function is preferred because it is more flexible and easier to use, especially with ranges.

Related Functions

• CONCAT: The CONCAT function is the modern version of CONCATENATE and is available in Excel 2016 and later. It has the same purpose but is more flexible and supports concatenating ranges, not just individual cell references.

  Example:

  =CONCAT(A1:A3)
  

  This will concatenate all values in the range A1:A3 into a single string.

• TEXTJOIN: If you need to concatenate values with a delimiter (e.g., spaces, commas, etc.), the TEXTJOIN function is more flexible than CONCATENATE. It allows you to specify a delimiter between the values and even ignore empty cells.

  Example:

  =TEXTJOIN(", ", TRUE, A1:A3)
  

  This would concatenate the values in A1:A3, separating them with a comma and a space.

• & (Ampersand): The ampersand operator (&) is a simple and quick alternative for concatenation. It can be used in place of CONCATENATE.

  Example:

  =A1 & " " & B1
  

  This will concatenate the values in A1 and B1 with a space between them, just like CONCATENATE(A1, " ", B1).

The CONCATENATE function is useful for joining text strings in Excel, though newer versions of Excel offer more flexible alternatives like CONCAT and TEXTJOIN. It is still helpful for legacy systems and older Excel versions.