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, ...])
💡 Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

TEXTJOIN concatenates strings from multiple cells, ranges, or arrays into a single string using a specified delimiter (such as a comma, pipe, or space). Crucially, TEXTJOIN includes an ignore_empty parameter, which prevents unsightly duplicate delimiters when encountering blank cells in a range. TEXTJOIN is one of the most versatile string aggregation functions in Google Sheets. Unlike legacy CONCATENATE or the ampersand operator, TEXTJOIN provides a built-in boolean flag (ignore_empty) that cleanly strips blank cells, preventing unsightly double commas or trailing delimiters in concatenated outputs.

Formula Comparison: TEXTJOIN vs. Alternatives

Compared to the legacy CONCATENATE function (which requires selecting individual cells one by one and inserts delimiters manually), TEXTJOIN accepts whole multi-row ranges (A1:A50) and handles delimiters automatically.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
delimiterTextRequiredString separator (e.g. ", ", " | ", or CHAR(10) for newlines).
ignore_emptyBooleanRequiredTRUE = Ignore blank cells, FALSE = Include empty delimiters.
text1Range / TextRequiredRange 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.

Example 4: Comma-Separated Email Distribution List

=TEXTJOIN(", ", TRUE, SalesTeam!C2:C50)
How it works: Extracts all team emails into a single copy-paste string, ignoring empty rows.

Example 5: Combine Filtered Tags with Pipe Delimiter

=TEXTJOIN(" | ", TRUE, FILTER(A2:A100, B2:B = "Active"))
How it works: Pulls active customer tags into a clean single-line header summary.

Common Errors & Solutions

❌ String exceeds 50,000 character cell limit
Resolution: Google Sheets cells have a 50,000 character maximum limit.
❌ #VALUE! Exceeded maximum cell character limit
Resolution: Google Sheets limits a single cell to 50,000 characters. If concatenating massive datasets, filter or truncate strings first.
❌ Missing ignore_empty boolean argument
Resolution: Ensure the second argument is explicitly TRUE (skip blanks) or FALSE (include blanks).

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.

Related Google Sheets Formulas