TEXTJOIN Function in Excel and Google Sheets
Purpose
Join multiple text strings or ranges into one string with a specified delimiter between each item
Return value
A single text string of all values joined together with the delimiter placed between each item
TEXTJOIN is the text-joining function that should have existed from the beginning. CONCATENATE requires you to list every cell and every separator individually. TEXTJOIN takes a range and a delimiter and does the whole job in one short formula. Join all the tags from A2:A10 with a comma. Build a comma-separated list of team members from a column. Combine multiple address fields with line separators. The ignore_empty argument is the feature that makes it genuinely practical -- set it to TRUE and blank cells are silently skipped so you never get double-delimiter output when some rows are empty. I switched to TEXTJOIN for all multi-value joining the moment I discovered it, and I only reach back to CONCATENATE or the & operator for two or three specific values where function syntax would be overkill. TEXTJOIN is available in Excel 2019, Excel 365, and all versions of Google Sheets.
Syntax
✓ Excel 2019+
✓ Google Sheets
= Same syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| delimiter | Required | The separator to place between each joined value. A comma and space is ", ", a newline is CHAR(10), an empty string "" joins with no separator. |
| ignore_empty | Required | TRUE to skip blank cells in the range, FALSE to include them as empty entries. Almost always use TRUE. |
| text1 | Required | The first range, cell, or text string to join. Accepts ranges like A2:A10 directly. |
| text2 ... | Optional | Additional ranges or values, up to 252 text arguments total. |
How it works
TEXTJOIN places the delimiter between each non-blank value (when ignore_empty is TRUE) in the supplied ranges. It does not add the delimiter before the first item or after the last item -- only between items. If all cells in the range are blank, TEXTJOIN returns an empty string. If only one cell has a value, no delimiter is added since there is nothing to separate. The delimiter can be any text string: a comma and space ", ", a hyphen "-", a newline character CHAR(10) for line breaks inside a cell, or an empty string "" to join with no separation at all. Multiple ranges are accepted as additional arguments after the first range -- all values from all ranges are pooled together and joined in order. For Excel 2016 and earlier where TEXTJOIN does not exist, the CONCATENATE function or the & operator with individual cell references is the only alternative. The combine first and last name formula uses CONCATENATE for simple two-field joins -- TEXTJOIN is the better choice whenever a range or more than three fields are involved.
Examples
1
Join all non-blank tag values from a column into a single comma-separated string for a product description field.
| A | B | |
|---|---|---|
| 1 | Tag | Joined Result |
| 2 | Wireless | |
| 3 | Bluetooth | |
| 4 | ||
| 5 | USB-C | |
| 6 | Portable | |
| 7 | ||
| 8 | Rechargeable | |
| 9 | Result | =TEXTJOIN(", ",TRUE,A2:A8) |
Row 2: Wireless, Bluetooth, USB-C, Portable, Rechargeable
— Five non-blank tags are joined with a comma and space between each one. The two blank cells in rows 3 and 6 are silently skipped.
Setting ignore_empty to TRUE means blank rows in the tag column are ignored. If ignore_empty were FALSE, the result would include extra commas: Wireless, Bluetooth, , USB-C, Portable, , Rechargeable. Always use TRUE unless you specifically need blank entries represented in the output.
2
Build a full address string by joining street, city, and postcode fields with a comma separator in one formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Street | City | Postcode | Full Address |
| 2 | 12 Oak Lane | London | SW1A 1AA | =TEXTJOIN(", ",TRUE,A2:C2) |
| 3 | 5 River Rd | Manchester | M1 2AB | 5 River Rd, Manchester, M1 2AB |
| 4 | 8 High St | Bristol | BS1 3CD | 8 High St, Bristol, BS1 3CD |
| 5 | 22 Park Ave | LS1 4EF | 22 Park Ave, LS1 4EF | |
| 6 | 3 Mill Rd | Birmingham | B1 5GH | 3 Mill Rd, Birmingham, B1 5GH |
Row 2: 12 Oak Lane, London, SW1A 1AA
— Three fields joined with comma-space. Row 4 has no city -- TEXTJOIN skips it and produces 22 Park Ave, LS1 4EF with no double comma.
The same formula works for row 4 with a missing city because ignore_empty is TRUE. CONCATENATE would require an IF to handle the missing field -- TEXTJOIN makes this completely automatic.
3
Combine values from two separate columns into one joined list, treating both ranges as a single pool of items.
| A | B | C | ||||||
|---|---|---|---|---|---|---|---|---|
| 1 | Jan Team | Feb Team | Result | |||||
| 2 | Alice | Carol | =TEXTJOIN(" | ",TRUE,A2:A5,C2:C5) | ||||
| 3 | Bob | |||||||
| 4 | Dan | |||||||
| 5 | Ella | Finn | Alice | Bob | Ella | Carol | Dan | Finn |
Row 2: Alice | Bob | Ella | Carol | Dan | Finn
— Values from A2:A5 are joined first (Alice, Bob, Ella -- skipping the blank), then values from C2:C5 (Carol, skipping blank, Dan, Finn). All six are joined in order with a pipe-space separator.
Multiple range arguments pool all their values into one joined string. The ranges are processed left to right, top to bottom within each range. Blanks in either range are skipped because ignore_empty is TRUE.
Common use cases
1. Join all tags or keywords for a product into a single comma-separated field
=TEXTJOIN(", ",TRUE,A2:A20)
2. Build a full address from separate street, city, and postcode columns
=TEXTJOIN(", ",TRUE,A2:C2)
3. Create a semicolon-separated list of team members from a column for a CSV export
=TEXTJOIN(";",TRUE,B2:B50)
4. Combine values from multiple columns into one joined string treating them as one pool
=TEXTJOIN(", ",TRUE,A2:A10,C2:C10)
5. Join a list of items with line breaks inside a single cell using CHAR(10) as the delimiter
=TEXTJOIN(CHAR(10),TRUE,A2:A10)
Common errors
#NAME? error
TEXTJOIN is not available in Excel 2016 or earlier. The function name is not recognised in older versions.
Fix: Use CONCATENATE with individual cells and & operators as a workaround, or use Excel 2019 or newer.
Output has extra delimiters like double commas
ignore_empty is set to FALSE and the range contains blank cells. Each blank cell contributes an empty entry which produces consecutive delimiters.
Fix: Always set ignore_empty to TRUE unless you specifically want blanks to appear as empty entries in the output.
Result is a very long string that is cut off
The joined result exceeds the cell character limit (32,767 characters).
Fix: Reduce the range, split the output across multiple cells, or use a shorter delimiter.
Tips and variations
Always set ignore_empty to TRUE
Blank cells in the range produce extra delimiters when ignore_empty is FALSE -- the result looks like comma,,comma with gaps. Setting TRUE makes TEXTJOIN robust to sparse data and is the right default for almost every use case.
=TEXTJOIN(", ",TRUE,A2:A100)
Use CHAR(10) as the delimiter for line breaks inside a cell
CHAR(10) is the newline character. Combined with Wrap Text formatting on the cell, =TEXTJOIN(CHAR(10),TRUE,A2:A10) puts each value on its own line inside the cell -- useful for notes, tags, or bullet-style content in a single cell.
=TEXTJOIN(CHAR(10),TRUE,A2:A10)
Use TEXTJOIN instead of CONCATENATE for anything involving a range
CONCATENATE cannot accept ranges -- you must list every cell. TEXTJOIN accepts ranges directly, making it far more maintainable when the number of items changes. For two or three specific values, the & operator is still cleaner.
=TEXTJOIN(", ",TRUE,A2:A50)
Excel vs Google Sheets
Excel vs Google Sheets
TEXTJOIN is available in Excel 2019, Excel 365, and all versions of Google Sheets. It is not available in Excel 2016 or earlier. The syntax and ignore_empty behaviour are identical in Excel 2019+ and Google Sheets. Formulas copy between the two without changes.
Related reading
Frequently asked questions
TEXTJOIN joins multiple text strings or ranges into one string, placing a delimiter of your choice between each item. Unlike CONCATENATE, it accepts entire ranges and can automatically skip blank cells. =TEXTJOIN(", ",TRUE,A2:A10) joins all non-blank values in A2:A10 with a comma and space between each one.
CONCATENATE joins individual arguments one by one and cannot accept ranges -- you must list each cell separately. TEXTJOIN accepts ranges directly, adds a consistent delimiter between every item automatically, and can skip blank cells with the ignore_empty argument. TEXTJOIN is almost always the better choice for joining more than a few values.
Set the ignore_empty argument to TRUE: =TEXTJOIN(", ",TRUE,A2:A10) skips any blank cells in the range and joins only the non-blank values. Setting it to FALSE includes blank cells as empty string entries between delimiters, which often produces unwanted double delimiters like comma-comma in the output.
No. TEXTJOIN was introduced in Excel 2019 and is available in Excel 365. It is not in Excel 2016 or earlier. For those versions, use CONCATENATE with individual cell references and & operators as a workaround. TEXTJOIN is available in all modern versions of Google Sheets.
Yes. TEXTJOIN accepts up to 252 text arguments after the first two -- each can be a range, a single cell, or a literal string. =TEXTJOIN(", ",TRUE,A2:A10,C2:C10) joins non-blank values from both ranges with the same delimiter, treating both ranges as one continuous list of values to join.