How to Use TEXTJOIN in Excel
Joins a whole range into one string with a delimiter you choose — the clean way to build comma-separated lists.
What is TEXTJOIN in Microsoft Excel?
TEXTJOIN glues values together using a separator and can skip blanks in one step. Where CONCATENATE forced you to reference every cell and live with double commas, TEXTJOIN takes a whole range plus ignore_empty = TRUE and produces a tidy list. It is the standard tool for building email lists, tag strings and report headers.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| delimiter | Required | The separator placed between values, such as ", " or CHAR(10) for a line break. |
| ignore_empty | Required | TRUE skips blank cells, FALSE keeps them and adds extra delimiters. |
| text1 | Required | A value, cell or range to join. |
| text2 | Optional | Additional values or ranges, up to 252 in total. |
When to Use TEXTJOIN (Real-World Business Scenarios)
- 1
Building a comma-separated list of names, emails or product codes from a column.
- 2
Creating a single audit string from a row of values, such as 'Region: North | Rep: Rahul'.
- 3
Merging address parts while skipping empty fields so you never get double separators.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Team | Member | Region |
|---|---|---|
| Sales | Rahul | North |
| Sales | Pooja | South |
| Sales | Aman | West |
How it works: Walks down the Member column, ignores any blank entries and joins the three names with a comma and a space.
Common Errors & How to Fix Them
Why it happens: The joined result exceeds 32,767 characters, the maximum length of a single cell.
Fix: Join in smaller groups or split the output across several cells.
Why it happens: ignore_empty was set to FALSE, so blank cells still contributed a delimiter.
Fix: Pass TRUE as the second argument: =TEXTJOIN(", ",TRUE,B2:B100).
Try TEXTJOIN Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (TEXTJOIN)
What does the TEXTJOIN formula do in Excel?
TEXTJOIN glues values together using a separator and can skip blanks in one step. Where CONCATENATE forced you to reference every cell and live with double commas, TEXTJOIN takes a whole range plus ignore_empty = TRUE and produces a tidy list. It is the standard tool for building email lists, tag strings and report headers.
What is the syntax for TEXTJOIN?
What are common mistakes when using TEXTJOIN?
#VALUE!: The joined result exceeds 32,767 characters, the maximum length of a single cell. (Join in smaller groups or split the output across several cells.) Extra commas appear: ignore_empty was set to FALSE, so blank cells still contributed a delimiter. (Pass TRUE as the second argument: =TEXTJOIN(", ",TRUE,B2:B100).)
Related Excel Formulas
Keep building your formula toolkit with these text functions.
- TRIMRemoves leading, trailing and repeated spaces — the first fix for lookups that mysteriously return #N/A.
- SUBSTITUTEReplaces specific text inside a string — clean currency symbols, remove units or swap separators in bulk.
- LEFTExtracts the first characters of a string — pull a prefix, area code or year out of a longer code.
- LENCounts the characters in a cell, including spaces — the quickest way to catch invisible trailing whitespace.
- PROPERCapitalises the first letter of every word — fixes SHOUTED or all-lowercase names from raw imports.
- TEXTSPLITSplits one cell into many by a delimiter and spills the pieces into neighbouring cells — no Text to Columns needed.
Master TEXTJOIN in Intermediate Excel
Learn TEXTJOIN along with our formula library, practice files, and verifiable certificates in our complete curriculum.