How to Use TEXTJOIN in Excel
TEXTJOIN is Excel's most powerful text concatenation function, introduced in Excel 2019 and Microsoft 365. Unlike older functions like CONCATENATE or CONCAT, TEXTJOIN lets you specify a delimiter...
Key Insights
- TEXTJOIN combines text from multiple cells or ranges with a specified delimiter, replacing clunky CONCATENATE formulas and eliminating the need for helper columns
- The ignore_empty parameter automatically skips blank cells, solving the common problem of unwanted extra delimiters in concatenated strings
- TEXTJOIN becomes exceptionally powerful when combined with IF, FILTER, or array formulas to conditionally join data based on criteria
Introduction to TEXTJOIN
TEXTJOIN is Excel’s most powerful text concatenation function, introduced in Excel 2019 and Microsoft 365. Unlike older functions like CONCATENATE or CONCAT, TEXTJOIN lets you specify a delimiter once and automatically insert it between all text values. More importantly, it can intelligently skip empty cells and work with entire ranges in a single formula.
If you’ve ever built a formula like =A2&", "&B2&", "&C2 and then struggled when some cells were blank (resulting in awkward extra commas), TEXTJOIN solves this problem elegantly. It’s the difference between writing a brittle, cell-by-cell concatenation and a robust, range-based solution.
TEXTJOIN requires Excel 2019, Excel 2021, or a Microsoft 365 subscription. If you’re on Excel 2016 or earlier, you’ll need to stick with CONCATENATE or the ampersand operator, unfortunately.
Basic TEXTJOIN Syntax and Parameters
The TEXTJOIN function takes three parameters:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Delimiter: The character(s) you want between each text value. This could be a comma, space, comma-space combination, line break, or any text string.
Ignore_empty: A boolean (TRUE or FALSE) that determines whether to skip empty cells. Set this to TRUE in almost every case—it prevents double delimiters when cells are blank.
Text arguments: Individual cells, ranges, or text strings to combine. You can provide up to 252 arguments.
Here’s a simple example combining first and last names:
=TEXTJOIN(" ", TRUE, A2, B2)
If A2 contains “Sarah” and B2 contains “Chen”, this returns “Sarah Chen”. The space delimiter separates the two values, and ignore_empty is TRUE so blank cells won’t create extra spaces.
For a full name with a middle initial that might be blank:
=TEXTJOIN(" ", TRUE, A2, B2, C2)
If C2 (middle initial) is empty, you get “Sarah Chen” instead of “Sarah Chen” with an awkward double space.
Using TEXTJOIN with Cell Ranges
The real power of TEXTJOIN emerges when you reference entire ranges instead of individual cells. This is where it dramatically outperforms older concatenation methods.
To create a comma-separated list from a column of values:
=TEXTJOIN(", ", TRUE, A2:A10)
This single formula replaces what would have required either a complex nested CONCATENATE formula or multiple helper columns. If your range contains:
Apple
Banana
Cherry
Dragonfruit
The formula returns: “Apple, Banana, Cherry, Dragonfruit”—automatically skipping the blank cell because ignore_empty is TRUE.
You can also use multi-column ranges:
=TEXTJOIN(" | ", TRUE, A2:C2)
This concatenates all values across three columns in row 2, separated by " | “. If you have product data spread across columns (Category, Product Name, SKU), this creates a single descriptive string.
Advanced TEXTJOIN Techniques
Combining TEXTJOIN with conditional logic creates powerful data transformation formulas. Use IF to concatenate only values that meet specific criteria:
=TEXTJOIN(", ", TRUE, IF(B2:B10>100, A2:A10, ""))
This formula examines values in B2:B10 and only includes corresponding names from A2:A10 when the value exceeds 100. In Excel 2019, you’ll need to enter this as an array formula using Ctrl+Shift+Enter. In Microsoft 365, it works automatically as a dynamic array formula.
For Microsoft 365 users, combining TEXTJOIN with FILTER is even cleaner:
=TEXTJOIN(", ", TRUE, FILTER(A2:A10, B2:B10>100))
This achieves the same result with better readability—FILTER extracts names where the corresponding value exceeds 100, then TEXTJOIN combines them.
Use CHAR(10) as a delimiter to create line breaks within a single cell:
=TEXTJOIN(CHAR(10), TRUE, A2:A5)
This stacks values vertically in one cell. Make sure to enable “Wrap Text” for the cell to display properly. CHAR(10) is the line feed character; you can also use CHAR(13)&CHAR(10) for a carriage return + line feed if needed.
For creating numbered lists:
=TEXTJOIN(CHAR(10), TRUE, "1. "&A2, "2. "&A3, "3. "&A4)
Or more elegantly with SEQUENCE in Microsoft 365:
=TEXTJOIN(CHAR(10), TRUE, SEQUENCE(5)&". "&A2:A6)
Common Use Cases and Practical Applications
Creating mailing addresses: Combine separate address fields into a properly formatted multi-line address:
=TEXTJOIN(CHAR(10), TRUE, B2, C2&", "&D2&" "&E2)
Where B2 is the street address, C2 is city, D2 is state, and E2 is ZIP code. This produces:
123 Main Street
Springfield, IL 62701
Building SQL IN clauses: When you need to generate SQL from Excel data, TEXTJOIN creates the comma-separated list:
="SELECT * FROM customers WHERE customer_id IN ("&TEXTJOIN(", ", TRUE, A2:A50)&")"
If you need quoted strings for text values:
="SELECT * FROM products WHERE category IN ('"&TEXTJOIN("', '", TRUE, A2:A10)&"')"
This produces: SELECT * FROM products WHERE category IN ('Electronics', 'Books', 'Clothing')
Generating email distribution lists: Create semicolon-separated email addresses for Outlook:
=TEXTJOIN("; ", TRUE, D2:D100)
Copy the result directly into the To or CC field of an email.
Creating tags or keywords: Combine multiple tag columns into a single field:
=TEXTJOIN(" #", TRUE, "#", F2:J2)
This creates hashtag-formatted tags like “#excel #data #analytics #automation” from individual tag cells.
Troubleshooting and Limitations
TEXTJOIN has a maximum result length of 32,767 characters (the cell character limit in Excel). If your concatenated result exceeds this, the formula returns a #VALUE! error. For extremely large datasets, consider breaking the data into chunks or using alternative approaches.
In Excel 2019 (non-Microsoft 365), formulas combining TEXTJOIN with IF or other array functions require Ctrl+Shift+Enter to work as array formulas. Microsoft 365’s dynamic arrays handle this automatically. If your formula returns only one value instead of processing the entire range, you likely need to enter it as an array formula.
If source cells contain errors (#N/A, #VALUE!, etc.), TEXTJOIN propagates those errors. Wrap error-prone references in IFERROR:
=TEXTJOIN(", ", TRUE, IFERROR(A2:A10, ""))
This replaces any errors with blank text, which ignore_empty then skips.
Performance can degrade with very large ranges (tens of thousands of cells). TEXTJOIN must evaluate every cell in the range, so a formula like =TEXTJOIN(", ", TRUE, A:A) referencing an entire column will be slow. Always reference the specific range you need rather than entire columns.
TEXTJOIN doesn’t automatically remove duplicate values. If your range contains duplicates and you need unique values only, combine with UNIQUE in Microsoft 365:
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A100))
For Excel 2019, you’ll need a more complex approach using helper columns or array formulas with COUNTIF.
TEXTJOIN transforms how you work with text data in Excel. Whether you’re building simple concatenations or complex conditional joins, it eliminates the tedious cell-by-cell formulas that plague older workbooks. Master this function and you’ll find uses for it constantly—from data cleaning to report generation to API query building. The combination of delimiter specification, empty cell handling, and range support makes it indispensable for modern Excel work.