The Excel TEXTJOIN Function

Related Function:

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:

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.

Textjoin Function Examples

Example 1 - Joining Simple Text

Formula:

	A	B	C	D	E	F
1	Path 1	Path 2	Path 3	Path 4	File Name	Combined Path & Filename
2	C:	Users	Jim	Documents	notes.doc	=TEXTJOIN( "\", TRUE, A2:E2 )

Result:

	A	B	C	D	E	F
1	Path 1	Path 2	Path 3	Path 4	File Name	Combined Path & Filename
2	C:	Users	Jim	Documents	notes.doc	C:\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:

	A	B	C	D
1	Forename	Surname	Date of Birth	Combined Name & DOB
2	Jim	Smith	01/02/1990	=TEXTJOIN( " ", TRUE, A2:B2, TEXT(C2, "mm/dd/yyyy") )

Result:

	A	B	C	D
1	Forename	Surname	Date of Birth	Combined Name & DOB
2	Jim	Smith	01/02/1990	Jim Smith 01/02/1990

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".
(Date and time formatting types are explained further in the Excel Custom Number Format) page.

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-2019), that does not support the Textjoin function.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions