Text
TEXTJOIN
Definition
TEXTJOIN returns text combined with a delimiter between the pieces, and can skip empty cells.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Arguments
| Argument | Meaning |
|---|---|
delimiter |
The characters to place between pieces, in double quotes, such as " | ". |
ignore_empty |
1 or TRUE skips empty cells. 0 or FALSE keeps them and still inserts the delimiter. |
text1 |
The first text, cell, or range to join, such as A2:A4. |
text2, ... |
Optional extra text, cells, or ranges to join after the first. |
Worked example
A crew list joins names with a spaced bar and skips a blank row. Names are in A2:A4.
| A: Name |
|---|
| Ivy |
| Pia |
Formula: =TEXTJOIN(" | ",TRUE,A2:A4)
Result: Ivy | Pia. The empty cell is skipped, so there is no extra delimiter in the middle.
Practice
Cell A2 holds Ann, cell B2 holds Bea, and cell C2 is empty. In D2, write a formula that joins A2:C2 with a comma and skips empty cells.
- In D2, write a TEXTJOIN formula that joins A2:C2 with a comma and ignores empty cells.
- D2 should show A2:C2 joined with commas, skipping blanks.
Step 1 of 2
Enter =TEXTJOIN(",",1,A2:C2) in D2.
Ann and Bea joined with a comma, skipping the empty cell, is Ann,Bea.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
D2 uses TEXTJOIN.
D2 now shows Ann,Bea.
Related functions
CONCAT joins pieces with no delimiter. LEFT can then take a prefix from the joined text.
Common mistake
Setting ignore_empty to FALSE: the empty cell is kept, so TEXTJOIN(" | ",FALSE,A2:A4) returns Ivy | | Pia with an extra delimiter.