Text & Regex FormulaGoogle Sheets V8 Engine Verified
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.
Formula Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])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)How it works: Creates clean "[email protected], [email protected]" string ready for email Bcc.
Example 2: Multiline Bulleted List in Single Cell
=TEXTJOIN(CHAR(10), TRUE, FILTER(A2:A, B2:B="Pending"))How it works: Uses CHAR(10) line break to render formatted lists.
Example 3: Join Filtered Tags with Custom Hash Prefix
=TEXTJOIN(" #", TRUE, "#" & UNIQUE(A2:A))How it works: Generates social media hashtags from spreadsheet topics.
Common Errors & Solutions
❌ String exceeds 50,000 character cell limit
Resolution: Google Sheets cells have a 50,000 character maximum limit.
Performance Pro Tips
- Use CHAR(10) as delimiter and enable Format > Wrapping > Wrap to create readable multi-line summaries in a single cell.
Frequently Asked Questions
Does TEXTJOIN recalculate automatically when data changes?
Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.
Is this formula compatible with Microsoft Excel?
Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.