Combining text strings across multiple cells is a fundamental spreadsheet task. While the basic ampersand operator (&) and legacy CONCATENATE function work for small pairs of cells, they quickly become unwieldy and error-prone when merging large cell ranges, tables, or dynamic lists that contain blank entries.
The TEXTJOIN function in Google Sheets solves this limitation completely. By allowing you to declare a custom delimiter, automatically skip empty cells, and process multi-dimensional 2D ranges in a single formula, TEXTJOIN simplifies everything from generating comma-separated lists to formatting full postal addresses. In this guide, explore how to master the syntax and real-world applications of TEXTJOIN in Google Sheets.
Syntax and Parameter Breakdown of TEXTJOIN
The TEXTJOIN function accepts three primary arguments followed by one or more cell ranges:
TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])| Parameter | Data Type | Description |
|---|---|---|
delimiter |
String | A character or text string (enclosed in quotation marks) placed between each joined value. Can be a comma, hyphen, space, line break, or empty string "". |
ignore_empty |
Boolean | When set to TRUE, empty or blank cells are ignored. When set to FALSE, consecutive delimiters are retained. |
text1 |
Range / String | The first text item or contiguous cell range (e.g., A2:A10 or A2:D2) to be joined. |
[text2, ...] |
Range / String (Optional) | Additional individual cells, strings, or ranges to include in the combined output. |
Basic Examples: Joining Arrays with Commas and Hyphens
Consider a simple inventory list where item names span cells A2 through A6 (Apple, Banana, Orange, Mango, Grape).
Joining into a Comma-Separated List
To produce a clean comma-separated list with a space after each comma:
=TEXTJOIN(", ", TRUE, A2:A6)Result: Apple, Banana, Orange, Mango, Grape
Handling Delimiters with Blank Cells
If cell A4 is blank, the ignore_empty boolean determines how delimiters appear:
# Ignoring blanks (clean formatting):
=TEXTJOIN(", ", TRUE, A2:A6)
Result: Apple, Banana, Mango, Grape
# Retaining blanks (delimiters indicate missing values):
=TEXTJOIN(", ", FALSE, A2:A6)
Result: Apple, Banana, , Mango, GrapePro Tip: Setting ignore_empty to TRUE is the primary reason to choose TEXTJOIN over traditional JOIN or CONCATENATE, as it prevents messy trailing or double commas (,,) from corrupting exported data.
Advanced Application 1: Concatenating with Multi-Line Breaks
When compiling street addresses, product specifications, or multi-line invoice items into a single cell, you can insert line breaks using the CHAR(10) function as the delimiter:
=TEXTJOIN(CHAR(10), TRUE, B2:E2)Where B2 contains the Street Address, C2 contains Suite Number (which may be blank), D2 contains City, and E2 contains Postcode.
Ensure that text wrapping is enabled on the formula cell (Format > Wrapping > Wrap) so Google Sheets displays each segment on a separate line within the cell boundary.
Advanced Application 2: Combining TEXTJOIN with FILTER for Conditional Merging
One of the most powerful workflows combines TEXTJOIN with the FILTER function. This mimics a “lookup and concatenate” operation, gathering all matching values from a dataset into one cell based on criteria.
Suppose you have a project task list where Column A lists employee names and Column B lists their assigned tasks. To compile a comma-separated list of all tasks assigned specifically to “Sarah”:
=TEXTJOIN(", ", TRUE, FILTER(B2:B50, A2:A50 = "Sarah"))The FILTER function extracts only Sarah’s tasks into a virtual array, and TEXTJOIN instantly bundles them into a formatted sentence.
Advanced Application 3: Merging Two-Dimensional Ranges (Rows and Columns)
Unlike legacy concatenation functions that require manual column-by-column stitching, TEXTJOIN effortlessly reads entire rectangular grids:
=TEXTJOIN(" | ", TRUE, A2:D10)Google Sheets reads the range row by row from left to right, inserting the pipe delimiter between every cell while skipping empty rows automatically.
Comparing TEXTJOIN, JOIN, and CONCATENATE
Understanding which string function suits your project prevents formula errors and sluggish calculation times across large spreadsheets:
| Feature | TEXTJOIN |
JOIN |
CONCATENATE |
|---|---|---|---|
| Delimiters | Custom (single argument) | Custom (single argument) | Must be typed manually between each cell |
| Ignore Blanks | Yes (Built-in parameter) | No (Inserts delimiter for blanks) | No (Creates multiple commas/spaces) |
| 2D Grid Ranges | Yes (Evaluates rows & columns) | No (1D arrays only) | Yes (Flattens without delimiters) |
| Calculated Arrays | Yes (Works with FILTER, MAP, UNIQUE) |
Limited array support | Requires ARRAYFORMULA wrapper |
Resolving Common Formula Errors with TEXTJOIN
If your TEXTJOIN formula returns an unexpected error, check these frequent culprits:
#VALUE!Error: Often caused by omitting quotation marks around text delimiters (e.g., typing,instead of", "). Delimiters must always be supplied as text strings.- Character Limit Overflow (
50,000characters): Google Sheets enforces a 50,000-character ceiling per cell. Joining extremely large datasets with thousands of rows will result in an overflow error. UseQUERYor summarize data upstream before joining. - Unexpected Number Formatting: When joining dates or currency values, Google Sheets converts them to raw serial numbers. Wrap values with the
TEXTfunction to preserve display formats (e.g.,TEXT(C2, "DD/MM/YYYY")).
Streamlining Spreadsheet Data Presentation
By replacing brittle nested formulas with TEXTJOIN, you can streamline complex reports, generate clean CSV strings for export, and create dynamic summary dashboards in Google Sheets. Pair it with UNIQUE and SORT (e.g. =TEXTJOIN(", ", TRUE, SORT(UNIQUE(A2:A30)))) to generate clean, deduplicated summaries in a single step.