TEXTJOIN Formula in Excel: Combine Text with Custom Delimiters
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)The TEXTJOIN function is a powerful text manipulation tool introduced in Excel 2016 that revolutionized how users combine multiple text values. Unlike its predecessors CONCATENATE and CONCAT, TEXTJOIN offers superior flexibility by allowing you to specify custom delimiters between combined values and intelligently handle empty cells. This function is particularly valuable in data processing workflows where you need to merge information from multiple cells or ranges while maintaining clean, professional formatting. Whether you're creating comma-separated lists, building mailing addresses, generating product descriptions, or consolidating survey responses, TEXTJOIN streamlines operations that would otherwise require complex nested formulas or manual data entry. The function's ability to ignore empty cells automatically prevents awkward spacing and formatting issues, making it essential for anyone working with inconsistent or sparse datasets. Understanding TEXTJOIN transforms your Excel capabilities, enabling you to handle text consolidation tasks efficiently and professionally.
Syntax & Parameters
The TEXTJOIN function follows the syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...). The delimiter parameter is a required text string that acts as the separator between combined values—this can be a comma, space, hyphen, line break (using CHAR(10)), or any custom text. The ignore_empty parameter accepts TRUE or FALSE values; setting it to TRUE automatically skips empty cells, preventing unnecessary delimiters from appearing in your result, while FALSE includes all cells even if empty. The text1 parameter is mandatory and represents your first text value or range, while text2 and additional parameters are optional, allowing you to combine anywhere from two to 252 text sources. You can reference individual cells, entire ranges, or mix both approaches in a single formula. When using ranges, TEXTJOIN processes each cell sequentially, applying the delimiter between non-empty values (if ignore_empty is TRUE) or between all values (if FALSE). This flexibility makes TEXTJOIN exceptionally powerful for dynamic data consolidation across varying dataset sizes.
delimiterignore_emptytext1Practical Examples
Creating Comma-Separated Product Lists
=TEXTJOIN(", ", TRUE, A2:A5)This formula joins the product names from the range A2:A5 using a comma followed by a space as the delimiter. The TRUE parameter ensures that any empty cells in the range are ignored, preventing extra commas from appearing in the final result.
Building Complete Mailing Addresses
=TEXTJOIN(", ", TRUE, B3, C3, D3, E3)This formula combines individual address components from different columns using a comma-space delimiter. If any component is missing (empty cell), it's automatically skipped, preventing incomplete address formatting issues.
Consolidating Multiple Survey Responses
=TEXTJOIN(" | ", TRUE, F2:J2)This formula joins survey responses using a pipe character with spaces as the delimiter, making each response visually distinct. The TRUE parameter ensures respondents who didn't answer specific questions don't create empty entries in the consolidated result.
Key Takeaways
- TEXTJOIN is the modern solution for text consolidation in Excel 2016+, offering superior flexibility with custom delimiters and automatic empty cell handling.
- The ignore_empty parameter (TRUE/FALSE) is crucial for creating clean output—TRUE prevents awkward spacing from empty cells, FALSE includes all values.
- TEXTJOIN can process up to 252 text arguments or ranges, making it suitable for virtually any real-world data consolidation scenario.
- Combine TEXTJOIN with other functions like IF, FILTER, and UNIQUE in Excel 365 to create powerful dynamic text consolidation formulas.
- Always use specific ranges rather than entire columns for optimal performance, and leverage absolute references ($) for reusable templates.
Pro Tips
Use absolute references for your ranges when building reusable templates: =TEXTJOIN(", ", TRUE, $A$2:$A$100). This prevents accidental reference shifts when copying formulas across your workbook.
Impact : Ensures formula reliability across multiple uses and reduces errors from shifted references in complex workbooks.
For performance optimization with large datasets, always specify exact ranges instead of entire columns. Use =TEXTJOIN(", ", TRUE, A2:A1000) rather than =TEXTJOIN(", ", TRUE, A:A) to improve calculation speed.
Impact : Dramatically reduces formula calculation time, especially in workbooks with thousands of rows, improving overall Excel responsiveness.
Combine TEXTJOIN with TRIM to eliminate extra spaces: =TEXTJOIN(", ", TRUE, TRIM(A2:A5)). This ensures clean output even if source data contains inconsistent spacing.
Impact : Produces professional, polished results with consistent formatting, especially important for data exports and client-facing reports.
Use nested TEXTJOIN formulas to create hierarchical consolidations: =TEXTJOIN(" - ", TRUE, TEXTJOIN(" | ", TRUE, A1:A3), B1). This enables complex multi-level text organization for sophisticated reporting.
Impact : Enables creation of complex, professionally formatted consolidated text structures that would otherwise require manual concatenation or multiple helper columns.
Useful Combinations
TEXTJOIN with IF to Create Conditional Lists
=TEXTJOIN(", ", TRUE, IF(B2:B10>500, A2:A10, ""))This array formula combines TEXTJOIN with IF to create a list of product names only where their sales exceed 500. The IF statement evaluates each row, returning the product name if the condition is met or an empty string if not. Enter as an array formula using Ctrl+Shift+Enter in older Excel versions.
TEXTJOIN with FILTER for Dynamic Consolidation
=TEXTJOIN("; ", TRUE, FILTER(A2:A100, C2:C100="Active"))Available in Excel 365, this combines TEXTJOIN with FILTER to join only active records from a list. The FILTER function returns only rows where column C contains "Active", and TEXTJOIN consolidates them with semicolon delimiters, creating dynamic, responsive summaries.
TEXTJOIN with UNIQUE for Non-Duplicate Consolidation
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A50))In Excel 365, combine TEXTJOIN with UNIQUE to join only distinct values from a range, automatically removing duplicates. This is powerful for creating clean lists of unique entries from datasets that contain repetitive values.
Common Errors
Cause: The ignore_empty parameter is not set to TRUE or FALSE, or contains invalid data type such as text or numeric values other than 0 or 1.
Solution: Ensure the second parameter is exactly TRUE or FALSE. If using 0 or 1, convert them: use 1=TRUE (include empty) and 0=FALSE (ignore empty). Verify no text strings like "true" are used instead of the boolean value TRUE.
Cause: One or more cell references in the formula point to deleted rows or columns, or the range reference is invalid or broken.
Solution: Check all cell references and ranges in your formula. Verify that referenced cells and columns still exist. If you deleted rows/columns, update the formula references accordingly. Use absolute references ($A$1:$A$10) for ranges that shouldn't change when copying the formula.
Cause: TEXTJOIN is not available in your Excel version (available only in Excel 2016 and later), or the function name is misspelled.
Solution: Verify you're using Excel 2016, 2019, or Microsoft 365. For older Excel versions, use CONCATENATE or CONCAT instead. Check that the function name is spelled correctly as TEXTJOIN without spaces or special characters.
Troubleshooting Checklist
- 1.Verify TEXTJOIN is available in your Excel version (2016 or later). For older versions, use CONCAT or CONCATENATE instead.
- 2.Check that the ignore_empty parameter is exactly TRUE or FALSE, not text strings or other values that will trigger #VALUE! errors.
- 3.Confirm all cell references and ranges exist and haven't been deleted. Use absolute references ($) for ranges that should remain fixed when copying.
- 4.Ensure your delimiter is enclosed in quotation marks as text, not as a cell reference unless intentional. For example: ", " not A1.
- 5.Test with a smaller range first to verify the formula works before applying it to large datasets. This helps isolate issues quickly.
- 6.If results appear truncated, check the column width and enable 'Wrap Text' formatting if using line breaks (CHAR(10)) as delimiters.
Edge Cases
Using TEXTJOIN with a range containing mixed data types (numbers, text, dates)
Behavior: TEXTJOIN converts all values to text representation. Numbers display as entered, dates display as their serial numbers unless formatted with TEXT function first.
Solution: Wrap numeric or date ranges with TEXT function: =TEXTJOIN(", ", TRUE, TEXT(A2:A5, "0.00")) to control number formatting, or TEXT(A2:A5, "mm/dd/yyyy") for dates.
This is important for maintaining data integrity and professional presentation in consolidated results.
TEXTJOIN with a range containing cells with formulas that return empty strings ("") versus truly empty cells
Behavior: When ignore_empty is TRUE, TEXTJOIN treats formula-generated empty strings as values and includes them, creating extra delimiters. Only truly blank cells are ignored.
Solution: Use IF statements to return actual empty cells instead of empty strings: =IF(condition, value, "") becomes =IF(condition, value, NA()) or restructure formulas to avoid empty string returns.
This distinction is crucial for achieving clean output when combining ranges with conditional formulas.
TEXTJOIN with circular references or self-referential cells
Behavior: Excel displays a #REF! error if the formula references its own cell or creates a circular reference chain.
Solution: Ensure TEXTJOIN formulas reference different cells than the cell containing the formula. Use helper columns if necessary to avoid self-reference.
Always verify that your formula references are pointing to source data, not to the cell where the formula resides.
Limitations
- •TEXTJOIN is unavailable in Excel versions before 2016, requiring users with older Excel installations to use CONCATENATE or CONCAT functions instead.
- •The formula has a maximum result length of 32,767 characters, which can be reached when consolidating very large datasets or long text strings—results exceeding this limit will be truncated.
- •TEXTJOIN processes ranges sequentially and cannot reorder or sort the consolidated text; if you need sorted output, pre-sort your source data or use additional formulas like SORT (Excel 365) before consolidation.
- •Performance can degrade significantly when using entire columns (A:A) instead of specific ranges, especially in workbooks with thousands of rows, making range specification essential for large-scale operations.
Alternatives
Available in Excel 2016+ and simpler syntax for basic concatenation without delimiters. Works across Excel versions with consistent behavior.
When: Use CONCAT when you need simple text joining without custom delimiters or when working with straightforward cell combinations that don't require empty cell handling.
Compatibility
✓ Excel
Since Excel 2016
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) - Identical syntax across Excel 2016, 2019, and Microsoft 365✓Google Sheets
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) - Fully compatible with identical syntaxGoogle Sheets includes TEXTJOIN with the same functionality. Works seamlessly in cloud-based spreadsheets and exports to Excel without issues.
✓LibreOffice
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) - Supported in LibreOffice Calc 5.2 and later versions