CONCAT function

The CONCAT function in Excel is used to combine multiple text strings into one. It replaces the older CONCATENATE function (which is still available for compatibility). The CONCAT function is simpler and more flexible as it allows you to concatenate ranges of text values without needing to specify each cell individually.

Syntax

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

Parameters

• text1 (required): The first text item to concatenate. This can be a text string, a number, a cell reference, or even a range of cells.
• text2, … (optional): Additional text items to concatenate. You can provide multiple text arguments, and Excel will join them into a single string. You can provide up to 253 arguments in total.

Key Points

• The CONCAT function does not add any delimiters between the concatenated values. If you want to add spaces, commas, or other characters between the values, you need to include them manually (for example, " ", ", ", etc.).
• It works with both text values and numerical values (which are converted to text).
• Newer Excel versions (Excel 2016 and later) support the CONCAT function, but earlier versions of Excel use CONCATENATE.

Example Usage

1. Basic Concatenation

If you want to concatenate the text “Hello” and “World”, you can use the following formula:

=CONCAT("Hello", " ", "World")

This will return:

Hello World

The formula combines the text “Hello”, a space (" "), and “World”.

2. Concatenating Cell References

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

=CONCAT(A1, " ", B1)

This will return:

Good Morning

3. Concatenating Ranges

You can also concatenate an entire range of text values. For example, if you have text in cells A1:A3 (“John”, “Jane”, “Joe”), you can concatenate them using:

=CONCAT(A1:A3)

This will return:

JohnJaneJoe

Note that no spaces are added by default.

4. Adding Delimiters

If you want to add a comma or any other delimiter between values, you can include it in the formula. For example:

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

If A1 is “John”, B1 is “Jane”, and C1 is “Joe”, this will return:

John, Jane, Joe

5. Concatenating Numbers

If you want to concatenate numbers with text, you can do so easily. For example:

=CONCAT("The total is ", 150)

This will return:

The total is 150

Notes

• No Automatic Delimiter: Unlike some other functions (like TEXTJOIN), CONCAT does not automatically insert delimiters. You have to specify any separators yourself.
• Use in Newer Excel Versions: CONCAT is available in Excel 2016 and later. For compatibility with earlier versions, you can use the older CONCATENATE function, but CONCAT is the recommended function moving forward.
• Limitations: The function can handle a large number of arguments (up to 253), but be mindful of practical limits depending on the length of the text.

Related Functions

• TEXTJOIN: If you need to concatenate values with a delimiter (such as spaces, commas, etc.), the TEXTJOIN function is a more flexible option. It allows you to specify a delimiter between each text item and ignore empty values.

  Example:

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

  This would return:

  John, Jane, Joe
  

  If any cells in A1:A3 were empty, they would be ignored.

• CONCATENATE: The older version of CONCAT. While still available in Excel for compatibility, it’s recommended to use CONCAT for new formulas.
• & (Ampersand): The ampersand (&) operator can also be used for concatenation. For example, instead of using CONCAT, you can use:

  =A1 & " " & B1
  

  This will yield the same result as CONCAT(A1, " ", B1).

The CONCAT function is a powerful tool in Excel for combining text values, especially when working with data from multiple sources or cells. It is more flexible than the older CONCATENATE function and is the preferred method in modern versions of Excel.