Part of the free Module 5: Excel Formulas and Functions · Function 85 of 105 · Full Excel course
The Excel TEXTJOIN function joins text from several cells or ranges into one string, placing a delimiter of your choice between each item and optionally skipping empty cells. It is the modern replacement for long CONCATENATE formulas and is available in Excel 2019, Excel 2021, Excel for Microsoft 365 and Excel for the web.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)Arguments
| Argument | Required / Optional | Meaning |
|---|---|---|
| delimiter | Required | The text inserted between items, such as “, “, ” ” or CHAR(10). An empty string joins with nothing between. |
| ignore_empty | Required | TRUE skips blank cells so no double delimiters appear; FALSE includes them. |
| text1 | Required | The first text item, cell or range to join. |
| text2, … | Optional | Up to 252 further items or ranges. |
How TEXTJOIN works
TEXTJOIN reads each argument in order, converts values to text, drops blanks when ignore_empty is TRUE, and then inserts the delimiter between the surviving items. Ranges are read row by row. The delimiter itself can be a range, in which case Excel cycles through its cells, which allows different separators between items. The result is limited to 32,767 characters; anything longer returns #VALUE!. Numbers and dates are joined as stored, so use TEXT for a specific display format.
Step-by-step example: build a comma-separated list
- Enter customer names in A2:A8, leaving one or two cells blank to see the effect of ignore_empty.
- In C2 enter
=TEXTJOIN(", ",TRUE,A2:A8). - Press Enter. The names appear in one cell separated by a comma and a space, with no gaps for the blank cells.
- Change TRUE to FALSE and notice that the blanks now produce consecutive commas. Switch back to TRUE for clean output.
Practical use cases
1. Full address on separate lines
=TEXTJOIN(CHAR(10),TRUE,A2:D2)Turn on Wrap Text; empty address parts are skipped automatically.
2. Conditional join: all products bought by one customer
=TEXTJOIN(", ",TRUE,IF($A$2:$A$200=F2,$B$2:$B$200,""))IF returns the product where the customer matches and an empty string elsewhere; TEXTJOIN drops the empties. In Excel 2019 confirm with Ctrl+Shift+Enter.
3. Unique values as a list (Excel 365)
=TEXTJOIN("; ",TRUE,UNIQUE(A2:A200))4. Reverse a string
=TEXTJOIN("",TRUE,MID(A2,LEN(A2)-SEQUENCE(LEN(A2))+1,1))5. Join with formatted numbers
=TEXTJOIN(" | ",TRUE,A2,TEXT(B2,"dd-mmm"),TEXT(C2,"#,##0.00"))Common mistakes and tips
- #NAME? error: TEXTJOIN is missing in Excel 2016 (perpetual) and earlier. Use CONCATENATE or the ampersand operator there.
- #VALUE! error: the result exceeds 32,767 characters. Split the join across several cells.
- Double delimiters: ignore_empty is FALSE. Set it to TRUE unless you need positional gaps.
- Cells that look blank but hold a space are not empty, so they still produce a delimiter. Clean the source with TRIM.
- Dates show as numbers: wrap them in TEXT before joining.
TEXTJOIN compared with CONCAT and CONCATENATE
All three combine text, but only TEXTJOIN has a delimiter argument and the ability to skip blanks. CONCAT joins ranges with nothing between the items, so it suits assembling codes or digits. CONCATENATE cannot accept ranges at all and is kept for backward compatibility. A formula such as =CONCATENATE(A2,", ",B2,", ",C2,", ",D2) becomes =TEXTJOIN(", ",TRUE,A2:D2), which is shorter, handles missing values and is easier to extend. TEXTJOIN also works with array expressions, which makes it the basis for conditional joins and for turning a filtered list into a single text value, tasks that previously needed VBA. When you only join two values, the ampersand operator remains the simplest option in every version.
Related functions
- CONCAT: joins ranges without a delimiter.
- CONCATENATE: the legacy joining function.
- TEXT: formats numbers and dates before joining.
- CHAR: supplies a line break as the delimiter.
- See all lessons in the Excel Formulas course.
Frequently asked questions
Which Excel versions have TEXTJOIN?
Excel 2019, Excel 2021, Excel for Microsoft 365, Excel for the web and Excel for Mac 2019 or later. Excel 2016 and earlier return #NAME?.
How do I join cells with a comma and skip blanks?
Use =TEXTJOIN(“, “,TRUE,A2:A20). The TRUE argument tells Excel to ignore empty cells so no double commas appear.
Can TEXTJOIN join values that meet a condition?
Yes. Nest an IF that returns the value or an empty string, for example =TEXTJOIN(“, “,TRUE,IF(A2:A200=F2,B2:B200,””)), and TEXTJOIN keeps only the matches.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.