TEXTJOIN Function in Excel: Complete Guide (2025)
Quick Answer: TEXTJOIN joins text with delimiters: =TEXTJOIN(delimiter, ignore_empty, text_range). Example: =TEXTJOIN(", ", TRUE, A1:A10) combines cells with commas, skips blanks. Excel 365/2019+ only.
What is TEXTJOIN?
TEXTJOIN is Excel's modern text combining function (Excel 365/2019+). It joins text from multiple cells or ranges with a delimiter (like comma, space, or line break) and can automatically skip blank cells.
Simple Example:
=TEXTJOIN(", ", TRUE, A1:A5)
// Joins: A1, A2, A3, A4, A5
// With: comma and space ", "
// Skips: blank cells (TRUE)
// Result: "Apple, Banana, Orange"Old Way (CONCATENATE)
=A1&", "&A2&", "&A3
Manual, tedious, doesn't skip blanks
New Way (TEXTJOIN)
=TEXTJOIN(", ", TRUE, A1:A10)
Automatic, clean, handles blanks
⚠️ Excel Version Required
Works in: Excel 365, Excel 2019, Excel 2021, Excel for Mac 2019+
Does NOT work in: Excel 2016, Excel 2013, older versions
Alternative for older Excel: Use CONCATENATE or CONCAT
💡 Common Use Cases
- • Email lists: Join names/emails with semicolons
- • Tags/Keywords: Combine tags with commas
- • Addresses: Join address parts with line breaks
- • SQL queries: Build WHERE IN clauses from lists
- • Export data: Create CSV-style text from ranges
TEXTJOIN Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
1. delimiter (required)
Text to insert between each value. Can be empty "".
", " ← Comma and space" | " ← Pipe separatorCHAR(10) ← Line break (new line)"" ← No delimiter (direct join)2. ignore_empty (required)
TRUE/FALSE - whether to skip blank cells.
TRUE ← Skip blank cells (recommended)FALSE ← Include blanks (creates empty delimiters)Example: =TEXTJOIN(",", TRUE, "A","","B") → "A,B"
Example: =TEXTJOIN(",", FALSE, "A","","B") → "A,,B"
3. text1, [text2], ... (required)
Values to join. Can be cells, ranges, or text.
A1:A10 ← Range"Hello", " ", "World" ← Multiple valuesA1:A5, C1:C5 ← Multiple rangesCommon Delimiters
", "- Comma space"; "- Semicolon space" "- Single space" - "- Dash with spaces
CHAR(10)- Line break" & "- Ampersand" | "- Pipe""- No delimiter
Example Breakdown
=TEXTJOIN(", ", TRUE, B2:B10)", ": Join with comma and space
TRUE: Skip any blank cells in range
B2:B10: Combine all values from this range
Result: "Apple, Banana, Orange, Grape"
10+ TEXTJOIN Examples
1. Basic Comma-Separated List
=TEXTJOIN(", ", TRUE, A2:A10)Joins all values with comma-space, skips blanks
Result: "Apple, Banana, Orange"
2. Email List (Semicolon)
=TEXTJOIN("; ", TRUE, B2:B20)Perfect for email CC/BCC fields
Result: "john@email.com; jane@email.com; bob@email.com"
3. Multi-Line Address
=TEXTJOIN(CHAR(10), TRUE, A2:A5)
CHAR(10) creates line breaks (must enable wrap text)
Result: "123 Main St
Apt 4B
New York, NY
10001"
4. Full Name from First/Last
=TEXTJOIN(" ", TRUE, A2, B2, C2)Combines First, Middle, Last with spaces
Result: "John Michael Smith"
Multiple Ranges
5. Combine Non-Adjacent Columns
=TEXTJOIN(", ", TRUE, A2:A10, C2:C10, E2:E10)Joins values from columns A, C, and E
6. Entire Row Join
=TEXTJOIN(" | ", TRUE, A2:Z2)Combine all columns in a row with pipe separator
Conditional Joining (with IF)
7. Join Only Non-Zero Values
=TEXTJOIN(", ", TRUE, IF(A2:A10>0, A2:A10, ""))Excel 365 dynamic array: only includes positive values
8. Join Matching Category
=TEXTJOIN(", ", TRUE, IF(B2:B10="Active", A2:A10, ""))Join names (A) where status (B) is "Active"
Advanced Techniques
9. SQL WHERE IN Clause
="WHERE ProductID IN ('"&TEXTJOIN("','", TRUE, A2:A10)&"')"Result: WHERE ProductID IN ('A123','B456','C789')
10. Reverse Order Join
=TEXTJOIN(" ", TRUE, C2, B2, A2)Join in specific order (e.g., Last, First, Middle)
11. Custom Formatting
=TEXTJOIN(" and ", TRUE, A2:A9)&" and "&A10Result: "Apple and Banana and Orange and Grape"
🎯 Pro Tips
- • Always use TRUE: Skip blanks for cleaner output
- • Line breaks: Use CHAR(10) and enable Wrap Text
- • Dynamic arrays: Combine with IF for conditional joins (Excel 365)
- • Large ranges: TEXTJOIN handles up to 252 arguments
- • Alternative: Use CONCAT (no delimiter) or CONCATENATE (older Excel)
Frequently Asked Questions
Why is TEXTJOIN not working in my Excel?
TEXTJOIN requires Excel 365, Excel 2019, or Excel 2021. It doesn't work in Excel 2016 or earlier versions. Check your Excel version: File → Account → About Excel. For older versions, use CONCATENATE, CONCAT, or create a custom VBA function.
How do I create line breaks with TEXTJOIN?
Use CHAR(10) as delimiter: =TEXTJOIN(CHAR(10), TRUE, A1:A5). Important: You must enable Wrap Text (Home → Wrap Text) for line breaks to display. CHAR(13) works for Windows, CHAR(10) for Mac, or use both: CHAR(13)&CHAR(10).
Can TEXTJOIN handle errors in cells?
No, TEXTJOIN will return an error if any cell contains #N/A, #REF!, etc. Solution: =TEXTJOIN(", ", TRUE, IFERROR(A1:A10, "")) to replace errors with blanks. Or use conditional IF to exclude error cells.
What's the maximum length TEXTJOIN can create?
Excel cell limit is 32,767 characters. TEXTJOIN will truncate at this limit without warning. For very long joins, consider: 1) Multiple TEXTJOIN formulas, 2) VBA to write to file, 3) Power Query for data processing.
TEXTJOIN vs CONCAT vs CONCATENATE - which to use?
TEXTJOIN (Excel 365): Best - has delimiter, ignores blanks. CONCAT (Excel 2016+): No delimiter, supports ranges. CONCATENATE (all versions): No delimiter, cell-by-cell only. Always use TEXTJOIN if available.
How do I use TEXTJOIN with conditional logic?
Combine with IF (Excel 365 dynamic arrays): =TEXTJOIN(", ", TRUE, IF(B2:B10>50, A2:A10, "")) joins A values where B>50. For older Excel, use helper column with IF, then TEXTJOIN on that column.