How CONCAT works in Excel? Best CONCAT examples

How CONCAT works in Excel? Best CONCAT examples

If you’ve ever needed to combine text from multiple cells in Excel, you’ve probably used a method like the ampersand (&) operator or the CONCATENATE function. However, since Excel 2016, Microsoft has introduced a more efficient and modern solution: the CONCAT function.

What is the CONCAT function?

The CONCAT function in Excel is designed to join values from multiple cells together into a single string. It replaces the older CONCATENATE function, which is now considered obsolete but still supported for backward compatibility.

Basic syntax of CONCAT

Here’s the syntax for CONCAT:

=CONCAT(text1, text2, ...)

Where:

  • text1, text2, ... – The text values or cell references to join together.

The function can take multiple arguments, including text strings, numbers, or cell ranges.

Best CONCAT examples in Excel

1. Concatenating simple values

The simplest use case is combining text directly:

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

This formula returns: Hello World

2. Combining cell values

You can also concatenate values from different cells:

=CONCAT(A1, B1, C1)

If:

A1 B1 C1
John Doe USA

The result will be: JohnDoeUSA

3. Adding spaces or separators

To make results more readable, you might want to insert spaces between words:

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

Now the output will be: John Doe – USA

4. Concatenating a range of cells

Unlike the old CONCATENATE function, the CONCAT function allows you to use entire ranges:

=CONCAT(A1:A3)

If:

A1
Excel
is
awesome

The result will be: Excelisawesome

If you need spaces between the words, you’ll have to modify the formula or use the TEXTJOIN function:

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

Output: Excel is awesome

Limitations and important notes

While the CONCAT function is very useful, there are some limitations to keep in mind:

  • No delimiter support: Unlike TEXTJOIN, CONCAT does not automatically insert separators between values.
  • No support for logical operations: Numbers, dates, and text are treated as simple string values.
  • Only available in recent versions: CONCAT is only available in Excel 2016 and later. If you’re using an older version, you can use CONCATENATE or & instead.

Conclusion

The CONCAT function in Excel is a powerful tool for merging cell values into a single text string. It’s a definite upgrade from CONCATENATE and offers the convenience of handling cell ranges directly. While it lacks delimiter support, it’s still an excellent choice when you need to combine text efficiently.

 

Other interesting article:

How CONCATENATE works in Excel? Best CONCATENATE examples