The Excel TEXTJOIN Function

Related Function:
CONCAT
CONCATENATE

Function Description

The Excel Textjoin function joins together a series of supplied text strings into one combined text string. The user can specify a delimiter to add between the individual text items, if required.

The syntax of the Textjoin function is:

TEXTJOIN( [delimiter], [ignore_empty], text1, [text2], ... )

where the arguments are as follows:

[delimiter]-

An optional delimiter, to be inserted between each text string.

If omitted, no delimiter is used.
[ignore_empty]-

An optional logical value, that specifies whether empty cells should be ignored.

If omitted, the function uses the default value TRUE (ignore empty cells).
text1, [text2], ...-One or more text strings (or arrays of text strings), that you want to join together.

Notes:


Textjoin Function Examples

Example 1 - Joining Simple Text

 Formula:
 ABCDEF
1Path 1Path 2Path 3Path 4File NameCombined Path & Filename
2C:UsersJimDocumentsnotes.doc=TEXTJOIN( "\", TRUE, A2:E2 )
 Result:
 ABCDEF
1Path 1Path 2Path 3Path 4File NameCombined Path & Filename
2C:UsersJimDocumentsnotes.docC:\Users\Jim\Documents\notes.doc

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:
 ABCD
1ForenameSurnameDate of BirthCombined Name & DOB
2JimSmith01/02/1990=TEXTJOIN( " ", TRUE, A2:B2, TEXT(C2, "mm/dd/yyyy") )
 Result:
 ABCD
1ForenameSurnameDate of BirthCombined Name & DOB
2JimSmith01/02/1990Jim Smith 01/02/1990

Note that, in the example above:

See the Microsoft Office website for further examples of the Excel Textjoin function.


Textjoin Function Errors

If you get an error from the Excel Textjoin function, this is likely to be one of the following:

Possible Error
#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-2016), that does not support the Textjoin function.