Excel Function: CONCAT

Purpose

The CONCAT function generates a text value by combining values passed to it through one or many of its textn arguments. Values passed to any textn argument can take the form of a literal or be encapsulated by either a cell range or an array constant. CONCAT moves sequentially through each textn argument and adds values to its output in the order they are encountered. Cell ranges and array constants are read from top to bottom, and from left to right.

Invocations of the CONCAT function take the form:

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

Example

Consider a cell containing the formula;

=CONCAT(A1:B2,"d",{"e","f","g"},DATE(2023,1,1))

The text1, text2, text3 and text4 arguments were passed the cell range A1:B2, the literal value “d” and an array constant containing the values “e”, “f” and “g” and the date Jan 1, 2023 respectively. Because CONCAT works sequentially through each textn argument, it first reads the values in the cell range A1:B2 and the values “a”, “b” and “c” are added to its output. Cell B2 is ignored since it is empty. Then, “d”, “e”, “f”, and “g” are added. Finally, 44927 is added, which is the value used by Excel to represent the date Jan 1, 2023. The process is illustrated below:

The text1 argument is passed the cell range A1:B2. Cell A1 contains “a”, B1 contains “b”, A2 contains “c” and B2 is empty. the text2 argument is passed the literal value “d”, text3 is passed an array constant containing the value “e”, “f” and “g” and text4 is passed a date value representing Jan 1 2023. Step 1: Add the values referred to by the text1 argument. The 1st cell is A1 and "a" is added to the output. Step 2: Add the values referred to by the text1 argument. The 2nd cell is B1 and "b" is added to the output. Step 3: Add the values referred to by the text1 argument. The 3rd cell is A2 and "c" is added to the output. Step 4: Add the values referred to by the text1 argument. The 4th cell is B2 which is empty and has no effect on the output. Step 5: Add the value referred to by the text2 argument. "d" is added to the output. Step 6: Add the values referred to by the text3 argument. The 1st value is "e" and is added to the output. The output is “ab|cde”. Step 7: Add the values referred to by the text3 argument. The 2nd value is "f" and is added to the output. The output is “abcdef”. Step 8: Add the values referred to by the text3 argument. The 3rd value is "g" and is added to the output. The output is “abcdefg”. Step 9: Add the value referred to by the text4 argument. The value is of type date, and is converted to a text value by Excel prior to concatenating it into the result. Excel converts it to the text value "44297" (the number Excel uses to represent this date) and adds it to the output. The final output is "abcdefg44297".

Arguments

Argument	Argument type	Description
text1	Mandatory	A value or a set of values added to CONCAT’s output. The text1 argument can be passed either a literal value, a cell range or an array constant. The latter two facilitate the provision of more than one value via the text1 argument.
text2 … text253	Optional	252 arguments each capable of receiving one or many values that will be included in the output produced by CONCAT. Each argument can be passed either a literal value, a cell range or an array constant. The latter two facilitate the provision of more than one value to CONCAT via a single argument.

Related Functions

Function	Example	Purpose
TEXTJOIN	`TEXTJOIN(delimiter,ignore_empty,text1)`	Its purpose is identical to that of the CONCAT function: the TEXTJOIN function joins values together and produces one text value as output. TEXTJOIN differs from CONCAT in that: It is capable of placing delimiters between the values it joins together. Its ability to handle missing values is more sophisticated than CONCAT’s.

Error Conditions

Error value	Condition type	Condition
#VALUE!	Runtime	The length of CONCAT’s output exceeds 32,767 characters in length.

Examples

Example	Formula	Result	Description
1	`=CONCAT(,)`		Invoke CONCAT and omit all arguments. This has the effect of supplying the empty text value to both text1 and text2. Concatenating one empty text value with another yields the empty text value.
2	`=CONCAT({"a","b","","d"})`	`abd`	Join multiple values, including the empty text value. The empty text value will be ignored. The values “a”, “b”, “” and “d” will be joined together.
3	`=CONCAT( "a", TEXT( DATE(2023,1,1), "Mmm dd yyyy" ) )`	`aJan 01 2023`	This example demonstrates how to concatenate a date with another value. The TEXT function is applied to the date value Jan 1, 2023. This yields the text value “Jan 01 2023”. The value “a” is joined to “Jan 01 2023” to yield the result.

Examples: Errors

Example	Formula	Result	Description
1	`=CONCAT(REPT("a",32768))`		Generate a value > 32767 characters in length. The CONCAT function is limited to generating values ≤ 32,767 characters in length. Because this limit would be exceeded, #VALUE! is returned.