Excel TEXTJOIN Function — Combine Text with a Separator (with Examples)

What does it do?

TEXTJOIN combines multiple text values or ranges into one string with a separator between each item. It replaces the old method of chaining & operators and handles empty cells cleanly — optionally skipping them rather than leaving extra separators.

Syntax

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

Argument	Description
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
delimiter required	The separator to put between each item — e.g. ", " or " " or "-".
ignore_empty required	TRUE to skip empty cells (usually what you want). FALSE to include empty items.
text1 ... required	The text values or ranges to join. Can include entire columns.

Real examples

Example 1

Join names with comma and space

=TEXTJOIN(", ", TRUE, A2:A10)

Produces "Alice, Bob, Carol" skipping any empty cells.

Example 2

Build a full address

=TEXTJOIN(", ", TRUE, A2, B2, C2, D2)

Joins house number, street, city, postcode — skips whichever parts are blank.

Example 3

Create a delimited list for reporting

=TEXTJOIN(" | ", TRUE, A2:A20)

Joins all values with pipe separators — useful for dashboard labels.

Example 4

Join first and last name

=TEXTJOIN(" ", TRUE, A2, B2)

Cleaner than =A2&" "&B2 because it handles missing middle names.

Example 5

Concatenate with conditions

=TEXTJOIN(", ", TRUE, IF(B2:B10="Active", A2:A10, ""))

Enter as an array formula (Ctrl+Shift+Enter) to join only Active names.

FAQ

What versions of Excel support TEXTJOIN?

TEXTJOIN requires Excel 2019 or Microsoft 365. In older versions use the & operator or CONCATENATE.

What is the difference between TEXTJOIN and CONCAT?

CONCAT joins values without any separator and does not skip empty cells. TEXTJOIN adds your chosen separator between each item and can skip blanks.

What is the maximum length TEXTJOIN can produce?

32,767 characters — Excel's maximum cell content length.

TEXTJOIN for list building and reporting

Before TEXTJOIN, combining a list of values with a separator required a complex formula or VBA. Now =TEXTJOIN(", ",TRUE,A2:A20) does it in seconds. This is particularly useful for building readable summaries from structured data — listing all products in an order, all team members in a department, or all tags on a record.

TEXTJOIN becomes especially powerful as an array formula. =TEXTJOIN(", ",TRUE,IF(B2:B100="Active",A2:A100,"")) (entered with Ctrl+Shift+Enter in older Excel, or as a normal formula in Excel 365) joins only the names where status is Active — conditional list building in one formula.

For report generation, TEXTJOIN creates the kind of natural-language output that pivot tables cannot: "Revenue for Q1 came from three regions: North, South, and Midlands." Build that sentence dynamically by concatenating TEXTJOIN with surrounding text using &.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

SUBSTITUTE LEFT TRIM TEXT LEN CONCAT

The Excel TEXTJOIN Functionexplained simply