Excel's CONCAT Function

The Concat function can accept a range of cells (as well as single cells or values) as arguments, whereas the Concatenate function only accepts single cells or unique values.

Excel's CONCAT function merges a provided text string into a concatenated text string.

Function syntax:

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

Where the text argument represents one or more text strings (or an array of text strings) that you want to combine.

Note:
- The CONCAT function can handle up to 254 text arguments.
- The result of the CONCAT function must not exceed 32,767 characters.
- Each text argument provided can be an array of text strings/values or a single text string/value.

The CONCAT function was first introduced in Excel 2016. Hence, in earlier Excel versions, the CONCAT function was not available. Similarly, the function is not available in Excel 2016 for Mac. If you are using earlier Excel versions or Mac, you can use the Concatenate function as a substitute for the CONCAT function.

Readers can refer to the article 'Excel - Concatenate function, concatenating double quotation marks' by Mytour for a better understanding of how to use the Concatenate function.

Example of CONCAT function

Example 1: Combining simple text

The result in column D in the spreadsheet below is the combination of 2 simple texts, achieved using the Concat function.

Formula:

Result:

Example 2: Concatenating dates

When combining dates or times in Excel, it's important to note that dates and times are stored as simple numbers in Excel. If you directly input a date or time into the Concat function, it will display as a text string in the resulting text string, rather than as an actual date or time value.

So, if you want to concatenate dates or times, the first step is to use the Text function in Excel to convert the date or time value into a text string. This is demonstrated in the example below:

Formula:

Result:

Important note:
- Additional spaces and the character string ', DOB: ' are used in the Concat function to separate the values in cells A2-C2.
- The Text function is used to convert the date value in cell C2 into a string, using the date format 'mm/dd/yy' (month/day/year).

You can refer to the Microsoft website to find more examples of using the Concat function in Excel.

Some common errors when using Concat function

If you encounter any errors while using the Concat function, the cause of the error might be:

#VALUE! - This error occurs if the result of the Concat function exceeds 32,767 characters.
#NAME? - This error occurs if you are using a version of Excel prior to Excel 2016, as these versions do not support the Concat function.

What are your thoughts on the CONCAT function in Excel? Share your opinions and ratings with Mytour.