CONCATENATE was Excel's text-joining function for decades. It worked, but it had a painful limitation: you had to list every cell individually, type the delimiter between each one, and when a cell was blank it left a double-comma gap in your result. TEXTJOIN replaces it completely. You specify the delimiter once, pass a whole range, and set a single switch to skip blank cells automatically. The result is cleaner formulas, fewer keystrokes, and no more blank-cell gaps. This guide covers the full syntax, a direct comparison with CONCATENATE, and six practical examples.
TEXTJOIN Syntax
| Argument | Required? | What it means |
|---|---|---|
| delimiter | Required | The character(s) inserted between each text value — a comma, space, hyphen, pipe, or any string. Use ", " for comma-space, " " for space only, or "" for no separator at all. |
| ignore_empty | Required | TRUE — blank cells are skipped and no extra delimiter is inserted. FALSE — blank cells are included, leaving a visible gap. Use TRUE in almost every real-world formula. |
| text1 | Required | The first value or range to join. Can be a single cell, a range like A2:A20, a hardcoded string, or another formula. Up to 252 text arguments total. |
| text2, text3... | Optional | Additional values or ranges. TEXTJOIN processes all arguments left to right, inserting the delimiter between each non-empty value (when ignore_empty = TRUE). |
TEXTJOIN vs CONCATENATE — Why Make the Switch
Here is the same task — join five name cells with a comma separator — written with both functions:
=CONCATENATE(A2,", ",A3,", ",A4,", ",A5,", ",A6)
Delimiter typed 4 times.
Blank in A4 → "Alice, , Carlos"
Add a cell → rewrite the formula.
=TEXTJOIN(", ", TRUE, A2:A6)
Delimiter written once.
Blank in A4 → "Alice, Carlos"
Add a row → formula updates.
Example 1: Join a List with a Comma Separator
The most common use — take a column of items and produce a single comma-separated string. TEXTJOIN processes the entire range in one formula with no need to reference each cell individually.
Example 2: Build a Full Address from Parts
Address fields are rarely all filled in. Street, apartment, city, state, and postcode can have empty cells depending on the record. TEXTJOIN with ignore_empty = TRUE automatically skips missing parts without leaving extra commas or spaces.
& before passing them to TEXTJOIN. This lets you build composite parts — like "Austin TX" — while still using TEXTJOIN to handle the outer comma-separation and blank-skipping.
Example 3: Conditional Join — Only Items Meeting a Criterion
Nest an IF function inside TEXTJOIN to join only the values that match a condition. IF returns the value when the condition is TRUE and an empty string when it is FALSE. With ignore_empty = TRUE, the empty strings are automatically skipped.
Example 4: Join a Multi-Column Range (Full Name Builder)
TEXTJOIN can join values from multiple columns at once. Passing a 2D range processes cells left-to-right, top-to-bottom. More commonly, you pass separate column arguments to control the order and handle blanks in individual columns.
Example 5: Join Dates and Numbers (Using TEXT for Formatting)
TEXTJOIN converts numbers to text automatically. Dates, however, are stored as serial numbers internally — passing a date cell directly to TEXTJOIN gives you a number like 46118 instead of "28-Mar-2026". Wrap the date in TEXT() before passing it to TEXTJOIN to control the display format.
TEXT(date_cell, "DD-MMM-YYYY") (or any format string) before passing the date into TEXTJOIN to get a readable result.
Example 6: Line-Break Separator and Special Characters
The delimiter in TEXTJOIN does not have to be a comma or space. Use CHAR(10) (line feed) to join items with a line break — each value on its own line inside one cell. Use CHAR(13)&CHAR(10) for Windows-style carriage return + line feed, or any other character code you need.
Troubleshooting TEXTJOIN Errors
#NAME? error
You are on Excel 2016 or earlier, which does not support TEXTJOIN. Use CONCATENATE or the ampersand operator instead, adding delimiters manually between each argument.
#VALUE! error
The resulting string exceeds 32,767 characters — the maximum a single Excel cell can hold. Split the join across multiple cells, or reduce the number of items being combined. Also check that all arguments are valid text, numbers, or empty strings.
Dates display as numbers
Date cells are stored as serial numbers. Wrap any date argument in TEXT(date_cell, "format") before passing it to TEXTJOIN: =TEXTJOIN(", ", TRUE, A2, TEXT(B2,"DD-MMM-YYYY")).
Extra blank gaps appearing even with ignore_empty = TRUE
Cells that appear blank but contain a space character are not treated as empty by TEXTJOIN — they contain text (a space). Use TRIM() to clean them first: =TEXTJOIN(", ", TRUE, TRIM(A2:A20)). This removes leading, trailing, and excess internal spaces before joining.
Frequently Asked Questions
-
What does TEXTJOIN do in Excel?TEXTJOIN combines the text from multiple cells or a range into a single string, inserting a delimiter you specify between each value. You can also set it to automatically skip blank cells with a single TRUE/FALSE argument. It replaces CONCATENATE for almost all text-joining tasks because it is shorter, accepts ranges, and handles blank cells gracefully.
-
What is the difference between TEXTJOIN, CONCAT, and CONCATENATE?CONCATENATE is the legacy function — it requires each cell listed individually, has no delimiter argument, and cannot skip blanks. CONCAT (Excel 2019+) improves on it by accepting ranges, but still has no delimiter argument and no blank-skipping. TEXTJOIN (Excel 2019+) is the most capable — it accepts ranges, has a dedicated delimiter argument, and can skip blank cells. For any task needing a delimiter between values, TEXTJOIN is the right choice.
-
What does ignore_empty do in TEXTJOIN?When set to TRUE, TEXTJOIN skips any empty cells in the range and does not insert the delimiter for them. The result contains no double-commas or trailing delimiters from blank entries. When set to FALSE, blank cells are treated as empty strings and delimiters are still inserted around them — producing gaps like "Alice, , Carlos". Use TRUE for almost every real-world formula.
-
How do I use TEXTJOIN with a condition?Nest an IF function inside TEXTJOIN: =TEXTJOIN(", ", TRUE, IF(B2:B10="Yes", A2:A10, "")). IF returns the value when the condition is TRUE, or an empty string when it is not. With ignore_empty = TRUE, those empty strings are automatically skipped and only the matching values are joined. This pattern works for any comparison operator or text match.
-
Why do my dates show as numbers in TEXTJOIN?Excel stores dates as serial numbers internally. TEXTJOIN converts whatever value is in the cell to text — and for a date cell that means converting the number, not the displayed date string. To get a readable date, wrap the date in TEXT() with a format code: =TEXTJOIN(", ", TRUE, A2, TEXT(B2, "DD-MMM-YYYY")). This converts the date to text in your preferred format before TEXTJOIN processes it.
-
Which Excel versions support TEXTJOIN?TEXTJOIN is available in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365. It is not available in Excel 2016 or any earlier version. If you share workbooks with colleagues on Excel 2016, they will see a #NAME? error where TEXTJOIN formulas appear. Use CONCATENATE or the ampersand operator with manual delimiters as the compatible alternative.