How to Use TEXTJOIN in Google Sheets
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...]).
What TEXTJOIN does
You can join cells with the & operator, but it gets long fast, and it cannot take a whole range. TEXTJOIN joins a range in one step and puts the same separator between every piece. Use it for lists of names, tags, addresses or any set of values you want in one cell.
TEXTJOIN syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
delimiter: the text placed between items, such as", "or"-". It can be empty, or a reference to a cell that holds the separator. If it is empty, the text is simply joined together.ignore_empty:TRUEskips empty cells.FALSEincludes them.text1: a text item, either a string or a range of strings.text2, ...(optional): more text items.
A step-by-step example
Here is a short list of tags in A1:A5. The fourth item, A4, is empty.
| A | |
|---|---|
| 1 | Tag |
| 2 | red |
| 3 | green |
| 4 | |
| 5 | blue |
To join the tags into one comma-separated line:
=TEXTJOIN(", ", TRUE, A2:A5)
This returns red, green, blue. Here is why:
- The delimiter
", "goes between each piece of text. TRUEtells Sheets to skip the empty cell A4.- That leaves three items and two separators.
What ignore_empty changes
Set it to FALSE and the empty cell is kept, which leaves two separators in a row:
=TEXTJOIN("-", FALSE, A2:A5)
This returns red-green--blue. For tidy lists you almost always want TRUE.
Common TEXTJOIN mistakes
- Forgetting ignore_empty. It is a required argument. Leaving it out shifts every later argument.
- Missing quotes around the delimiter. A separator typed directly must be in quotation marks.
- Joining numbers and expecting formatting. Dates and decimals come out as plain values. Use TEXT first if you need a specific format.
- Reaching for CONCATENATE. It appends strings but cannot place a separator between a range of cells. TEXTJOIN can.
Try it yourself
Use the tag list above (A1:A5, where A4 is empty). Type your formula into the box, starting with =, then press Check.
Exercise 1: Comma-separated list
Write a formula that joins A2:A5 into one cell, separated by a comma and a space, skipping empty cells.
Exercise 2: Keep the gaps
Write a formula that joins A2:A5 with a hyphen (-) and keeps empty cells.
What to learn next
- SPLIT: break text into separate cells.
- ARRAYFORMULA: run one formula down a whole column.
- FILTER: pull out the rows that match.
- Split text into columns: the menu and the formula.
- CONCATENATE: appends strings one after another.
Syntax and rules in this guide were checked against Google's TEXTJOIN help page. See all guides.