Google Sheets TEXTJOIN: Concatenate Arrays with Custom Delimiters
TEXTJOIN combines text from multiple strings or ranges, inserting a specified delimiter between each item while automatically skipping empty blank cells.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])Technical Overview & Engine Mechanics
Formula Comparison: TEXTJOIN vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| delimiter | Text | Required | String separator (e.g. ", ", " | ", or CHAR(10) for newlines). |
| ignore_empty | Boolean | Required | TRUE = Ignore blank cells, FALSE = Include empty delimiters. |
| text1 | Range / Text | Required | Range of cells to join. |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Comma-Separated Email List for Group Messaging
=TEXTJOIN(", ", TRUE, Contacts!A2:A100)Example 2: Multiline Bulleted List in Single Cell
=TEXTJOIN(CHAR(10), TRUE, FILTER(A2:A, B2:B="Pending"))Example 3: Join Filtered Tags with Custom Hash Prefix
=TEXTJOIN(" #", TRUE, "#" & UNIQUE(A2:A))Example 4: Comma-Separated Email Distribution List
=TEXTJOIN(", ", TRUE, SalesTeam!C2:C50)Example 5: Combine Filtered Tags with Pipe Delimiter
=TEXTJOIN(" | ", TRUE, FILTER(A2:A100, B2:B = "Active"))Common Errors & Solutions
Performance Pro Tips
- Use CHAR(10) as delimiter and enable Format > Wrapping > Wrap to create readable multi-line summaries in a single cell.
- Combine TEXTJOIN with FILTER or UNIQUE to create comma-separated tag lists or distinct customer rosters in a single summary cell: =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(A2:A, B2:B="Active"))).
- The delimiter in TEXTJOIN is not limited to single characters; you can use CHAR(10) to join items with clean multi-line paragraph breaks within a single wrapped cell.
Frequently Asked Questions: TEXTJOIN
How do I insert line breaks as delimiters in TEXTJOIN?
Use CHAR(10): =TEXTJOIN(CHAR(10), TRUE, A2:A20). Ensure cell formatting has 'Text wrapping' enabled.
Can TEXTJOIN combine ranges from multiple different sheets?
Yes: =TEXTJOIN(", ", TRUE, Sheet1!A2:A10, Sheet2!A2:A10).
How does TEXTJOIN handle dates and numbers?
Dates convert to serial numbers unless formatted first with TEXT: ARRAYFORMULA(TEXT(DateRange, "yyyy-mm-dd")).
Is TEXTJOIN available in Excel?
Yes, Microsoft Excel 2019 and Excel 365 support TEXTJOIN.
Can TEXTJOIN be used with UNIQUE?
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A100)) outputs distinct values cleanly separated by commas.
What is the maximum character limit for TEXTJOIN in Google Sheets?
Google Sheets imposes a maximum cell string limit of 50,000 characters. If your joined output exceeds 50,000 characters, the formula returns a #LIMIT! error.