How to use the Excel TEXTJOIN function

Summary

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.

Purpose

Join text values with a delimiter

Return value

Concatenated text

Syntax

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

Arguments

delimiter - Separator between each text.
ignore_empty - Whether to ignore empty cells or not.
text1 - First text value or range.
text2 - [optional] Second text value or range.

Version

Excel 2019

Usage notes

TEXTJOIN is a new function, available in Office 365 and Excel 2019.

The TEXTJOIN function concatenates values from one or more text strings or ranges that contain text. Unlike the CONCATENATE function, TEXTJOIN lets you specify a range of cells and a delimiter to use when joining text values.

Example #1

To join cells in the range A1:A3 with a comma and space, you can use TEXTJOIN like this:

=TEXTJOIN(", ",TRUE,A1:A3)

TEXTJOIN basic example

The second argument, ignore_empty, controls behavior for empty cells and text values. If set TRUE, empty values are skipped so that the delimiter is not repeated in the final result. If set to FALSE, TEXTJOIN will include empty values in the output.

Example #2

To join cells in the range A1:A3 with no delimiter, including empty values, you can use:

=TEXTJOIN("",FALSE,A1:A3)

Notes

To concatenate manually, use the concatenation operator (&)
The CONCAT function also provides basic concatenation, but provides no options for delimiters or empty values.
Numbers provided to TEXTJOIN will be converted to text values during concatenation.

Related functions

Excel CONCATENATE Function

The Excel CONCATENATE function concatenates (joins) join up to 30 text items together and returns the result as text. The CONCAT function replaces CONCATENATE in newer versions of Excel.

Excel CONCAT Function

The Excel CONCAT function concatenates (joins) values supplied as references or constants. Unlike the CONCATENATE function (which CONCAT replaces), CONCAT allows you to supply a range of cells to join, in addition to individual cell references.