The Excel TEXTJOIN function concatenates (joins) values with a given delimiter. Unlike the CONCAT function, TEXTJOIN allows you to supply a range of cells, and has a setting to ignore empty values.
Syntax
The syntax for the TEXTJOIN function in Microsoft Excel is:
=TEXTJOIN( delimiter, ignore_empty, text1, [ text2, ... text_n ] )
Parameters or Arguments
delimiter
A string inserted between each text value in the resulting string. Most commonly, you would use a delimiter such as a comma or space character.
ignore_empty
Determines whether empty values are included in the resulting string. TRUE ignores empty values and FALSE includes empty values in the result.
text1, text2, … text_n
The strings that you wish to join together. There can be up to 252 strings that are joined together.
Textjoin Function Examples
Example 1 – Joining Simple Text
Formula:
Result:
Example 2 – Joining Text and Dates
If you want to join text with dates in Excel, you need to beware that dates and times are actually stored as simple numbers in Excel, and if you enter a date or a time directly into the Textjoin function, the underlying number will appear in the resulting text string, not the formatted date or time.
Therefore, if you want to join a text string with a date or a time, you need to use the Excel Text function to convert the date or time value into a text string first. This is shown in the example below:
Formula:
Result:
Note that, in the example above:
- A space ” ” is supplied as the [delimiter], so this has been used to separate the values in cells A1-C2.
- The Text function has been used to convert the date value in cell C2 into a string, using the date format “mm/dd/yy”.
Textjoin Function Errors
If you get an error from the Excel Textjoin function, this is likely to be one of the following:
#VALUE! – Occurs if the result of the Textjoin function exceeds 32,767 characters.
#NAME? – Occurs if you are using an older version of Excel (pre-2019), that does not support the Textjoin function.
Notes:
- The Textjoin function can handle up to 252 text arguments.
- The result of the Textjoin function must not exceed 32,767 characters.
- Each of the supplied text arguments can be an array of text strings or values or a single text string or value.
- The Textjoin function was first introduced in Excel 2019 and so is not available in earlier versions of Excel.
- The Excel Textjoin function is similar to the Concat function. Both functions join together text strings but the difference is that the Textjoin function can accept a delimiter, whereas the Concat function can not.