The TEXTJOIN function generates a text value by combining (1) values passed to it through one or many of its textn arguments and (2) sequences of characters which delimit the boundaries of those values. 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. TEXTJOIN moves sequentially through each textn argument, adds values to its output in the order they are encountered and separates values with delimiters. Cell ranges and array constants are read from top to bottom, and from left to right.
Invocations of the TEXTJOIN function take the form:
The text1, text2 and text3 arguments were passed the cell range A1:B2, the literal value “d” and an array constant containing the values “e”, “f” and “g” respectively. Because TEXTJOIN 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 and the value passed to the ignore_empty argument is TRUE. Then, “d”, “e”, “f”, and “g” are added. TEXTJOIN separates values by placing delimiters between them. The 1st and 2nd values are separated by “//”, the 2nd and 3rd values by “||”, and the 3rd and 4th values by “\\”. This pattern is repeated as TEXTJOIN adds the 5th, 6th and 7th values to its output. The process is illustrated below:
One or more sequences of characters placed between the values from which TEXTJOIN generates its output. More than one delimiter can be specified by passing them to the delimiter argument through either a cell range or an array constant. If more than one delimiter is specified then they will be cycled into TEXTJOIN’s output. For example if “;“, “|” and “%” were defined as delimiters in that order then they will appear in TEXTJOIN’s output in the order “;” → “|” → “%” → “;” → “|” …
ignore_empty
Optional. Defaults to TRUE if omitted.
The ignore_empty argument determines how TEXTJOIN manages situations in which elements of a cell range or array constant passed to one of TEXTJOIN’s textn arguments are empty. It can assume one of two values:
Value
Description
TRUE
TEXTJOIN provides no indication in its output that it was in receipt of an empty value. For example, if the delimiter were defined as “|“, the array constant{“a”,”b”,””,”d”} were provided to the text1 argument and ignore_empty were TRUE, the result would be “a|b|d“.
FALSE
TEXTJOIN adds two delimiters with nothing between them to indicate it was in receipt of an empty value. For example, if the delimiter were defined as “|“, the array constant{“a”,”b”,””,”d”} were provided to the text1 argument and ignore_empty were FALSE, the result would be “a|b||d“.
text1
Optional. Defaults to “” if omitted.
A value or a set of values added to TEXTJOIN’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 … text251
Optional
251 arguments each capable of receiving one or many values that will be included in the output produced by TEXTJOIN. 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 TEXTJOIN via a single argument.
Its purpose is identical to that of the TEXTJOIN function: the CONCAT function joins values together and produces one text value as output. CONCAT differs from TEXTJOIN in that:
It is incapable of placing delimiters between the values it joins together.
Its ability to handle missing values is less sophisticated than TEXTJOIN’s.
