TEXTJOIN in Excel: what it does and how to use it

TEXTJOIN glues several pieces of text together with a separator of your choosing between them, and can skip the empty ones.

Syntax

TEXTJOIN(delimiter, ignore_empty, text1, ...)

When to use it

Building a full name out of first and last, an address out of its parts, or a comma separated list out of a column.

What trips people up

The second argument decides whether blanks are skipped. Leave it as TRUE and you avoid the double commas that appear wherever a cell was empty.

Watch it built, step by step

Put two columns together — Building a full name or a key

Questions

What is the difference between TEXTJOIN and CONCAT?

TEXTJOIN puts a separator between each item and can skip empty cells. CONCAT runs the items together with nothing in between.

What does the TRUE in TEXTJOIN do?

It skips empty cells, so a blank in the range does not leave two separators side by side.

Can TEXTJOIN join only the rows that match a condition?

Yes, with IF inside it: =TEXTJOIN(", ",TRUE,IF(B2:B20="North",A2:A20,"")). In Excel 2019, confirm it with Ctrl+Shift+Enter.

Related functions

TEXTBEFORE — TEXTBEFORE returns everything that comes before a marker you name, such as a space, a dash or an at sign.
UNIQUE — UNIQUE takes a list and returns it with the repeats removed, one of each value.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.