CONCAT Excel function concatenates or joins up to 253 values that are supplied as references, ranges, or constants and returns the result as text. For example, If Cells A1 and B1 contains the values “House” and “Stark” respectively, then the formula “=CONCAT(A1,B1)” joins the cells A1 and B1 returning the output as “HouseStark”. If you want to have a space between the two, the formula should be =CONCAT(A1,” “,B1). Unlike the CONCATENATE function (which CONCAT replaces), CONCAT will accept a range of cells to join, in addition to individual cell references. Here we shall look into the Concat Excel function, Formula, its syntax, the different arguments, Errors and other details.
CONCAT Excel Function
The Excel CONCAT function concatenates or joins the values passed to it and returns the result as text. It accepts multiple arguments called text1, text2, text3, etc. Only the first argument is required, and values are concatenated in the order they appear. The values supplied may be cell references, ranges, or hard-coded text strings, and the values are concatenated in the order they appear. The CONCAT function automatically ignores empty cell references.
Concat Formula Syntax
=CONCAT(text1, [text2], …)
Arguments
text1 – The text item to be joined. A string, or array of strings, such as a range of cells.
text2 – [optional] The second text item to join.
Output or Result of Concat Function
All valid arguments passed to it are returned as a concatenated text.
Number Formatting will be Lost
When numeric values (dates, times, percentages, etc.) are passed as arguments to Concat function, their number formatting will be lost.
#NAME Error in Excel Concat Function
When a text is provided as an argument to concat function, such text should always be enclosed in double quotes. When the text is not enclosed in double quotes, Concat function returns a #NAME Error.
#VALUE! Error
If the output is greater than 32767 characters, CONCAT function returns #VALUE! Error.
CONCAT Function will handle ranges
=CONCAT(A1:Z1) // Will work
To concatenate many values with a common delimiter, see the TEXTJOIN function. TEXTJOIN can do everything CONCAT can do, but can also accept a delimiter and optionally ignore empty values.
Summary
- CONCAT Function will accept range as input argument in addition to cell references, and text strings.
- CONCAT function concatenates or joins up to 253 values together and returns the result as text.
- If the Output is greater than 32767 characters, CONCAT returns #VALUE! Error.
- Number formatting of Numeric values will be lost when passed as arguments to concatenate excel function.
- The ampersand character (&) is an alternative to CONCAT. The result is the same.