TEXTJOIN

What it does

TEXTJOIN joins a list of texts and puts the same separator between each pair: a comma, a line break, a semicolon. It can skip empty cells, so a range with gaps doesn't produce doubled separators.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

delimiter

The separator to put between items, in quotes, such as ", ".

ignore_empty

TRUE to skip empty cells, FALSE to keep them (which leaves two separators in a row).

text

The first text, cell or range to join. Add more arguments for more parts.

Example

Two of three people left an email address. How do you make one recipient list?

The formula in C2:

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

Result: ana@example.com, kai@example.com

A2:A4 holds two addresses and an empty cell. TRUE skips the empty one, so the two addresses are joined with ", " between them and nothing doubled.

Common mistakes

• Setting the second argument to FALSE over a range with gaps, which gives ", , " in the result.
• Forgetting the space after the comma in the separator when the result is meant to be read.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• TEXTJOIN: join with a separatorModule 8 · Changing text

More from this module

• CONCAT
• UPPER
• LOWER
• PROPER
• TRIM
• SUBSTITUTE
• TEXTBEFORE
• TEXTAFTER

All functions