Excel TEXTJOIN: Concatenate Text with Separators

Formulas & functions · Updated November 22, 2026

The fastest way to concatenate text in Excel is the TEXTJOIN function, which combines multiple ranges or strings with a delimiter of your choice. For example, =TEXTJOIN(", ", TRUE, A2:A10) joins the values in A2:A10 with a comma and space, skipping any empty cells.

What Is Concatenation in Excel?

Concatenation means joining two or more text strings into one. Excel has offered several ways to do this over the years. The classic CONCATENATE function (still available for compatibility) and the & operator both work, but they have limits: you must list each cell reference individually, and they do not handle ranges or delimiters automatically. The newer CONCAT function (introduced in Excel 2016) can take a range but offers no delimiter. TEXTJOIN, available in Excel 2019 and Microsoft 365, solves both problems by letting you specify a separator and whether to ignore empty cells.

How TEXTJOIN Works

The syntax is: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

  • delimiter – a text string placed between each item. Use " " for a space, ", " for comma-space, or "" for no separator.
  • ignore_empty – TRUE skips blank cells; FALSE includes them (as empty slots).
  • text1 – the first item; can be a cell, a string, or a range. You can add up to 252 additional items.

Example: with names in A2:A5 and scores in B2:B5, =TEXTJOIN(" - ", TRUE, A2:A5, B2:B5) returns something like Alice - 85 - Bob - 92 - Carol - 78 - Dave - 88 (the delimiter is inserted between every item, including between the name and score). To create a list of names only, use =TEXTJOIN(", ", TRUE, A2:A5).

Choosing Between &, CONCAT, and TEXTJOIN

For most modern work, TEXTJOIN is the best choice because it handles ranges and delimiters in one step. However, the & operator is still useful for simple joins of a few cells, especially when you need to insert custom text or line breaks. For example, =A2 & " " & B2 combines first and last names. CONCAT is a middle ground: it accepts a range but no delimiter, so you would need to nest it with other functions to add separators. In practice, if you have Excel 2019 or later, use TEXTJOIN for any range-based join; use & for quick one-off joins.

Common Problems and Solutions

One frequent issue is that TEXTJOIN returns a #VALUE! error if any argument is an array that is not the same size as others. For example, joining A1:A3 with B1:B5 will error because the ranges differ in length. Another issue: numbers joined with TEXTJOIN are converted to text, which is usually fine, but if you need to keep number formatting (like currency or leading zeros), you must first format the cells as text or use the TEXT function inside the formula, e.g., =TEXTJOIN(", ", TRUE, TEXT(A2:A5, "0.00")). Also, if you want a line break between items, use CHAR(10) as the delimiter and enable Wrap Text on the cell. On Mac, CHAR(10) works the same, but the shortcut to enter a line break in a cell is Control+Option+Return (Windows: Alt+Enter).

Comparing TEXTJOIN with Other Methods

Let’s compare the three main approaches for joining a range A2:A5 with a comma:

  • =A2 & ", " & A3 & ", " & A4 & ", " & A5 – tedious, but works in all versions.
  • =CONCAT(A2:A5) – returns AliceBobCarolDave, no delimiter.
  • =TEXTJOIN(", ", TRUE, A2:A5) – returns Alice, Bob, Carol, Dave.

For a simple two-cell join, & is shorter and more readable. For anything larger, TEXTJOIN wins. There is also CONCATENATE, which is identical to & but more verbose; it is kept for compatibility with older workbooks.

Using TEXTJOIN with Other Functions

TEXTJOIN becomes powerful when combined with functions like IF, FILTER, or UNIQUE. For instance, to list only sales above $100 from B2:B10 with corresponding names in A2:A10, you can use an array formula: =TEXTJOIN(", ", TRUE, IF(B2:B10>100, A2:A10, "")). In Microsoft 365, you must press Enter (it spills automatically), but in older Excel you would need to confirm with Ctrl+Shift+Enter. Another example: =TEXTJOIN(" | ", TRUE, UNIQUE(A2:A20)) creates a pipe-separated list of unique values. These combinations let you build dynamic summaries without VBA.

Frequently Asked Questions

Why is my TEXTJOIN returning #VALUE!? The most common cause is mismatched range sizes in the arguments. For example, joining A1:A3 with B1:B5 will error because the arrays are not the same length. Ensure all ranges have the same number of rows and columns, or use single cells for extra items.

Can I use TEXTJOIN to combine columns into one cell? Yes, but you must transpose the range. For example, to join A1:C1 horizontally, use =TEXTJOIN(", ", TRUE, A1:C1). To join A1:A3 vertically, it’s the same formula. To join a 2D range like A1:C3, just use =TEXTJOIN(", ", TRUE, A1:C3) – it reads row by row.

How do I add a line break between items? Use =TEXTJOIN(CHAR(10), TRUE, A2:A5) and then apply Wrap Text to the cell. On Windows, you can also press Alt+Enter inside the formula bar to insert a literal line break, but CHAR(10) is more portable.

Does TEXTJOIN work in Excel for the web? Yes, TEXTJOIN is available in Excel for the web, Excel for Mac, and Excel for Microsoft 365. It is not available in Excel 2016 or earlier. If you are using an older version, use CONCAT with a helper column or the & operator.

Can I ignore empty cells but keep zeros? Yes, ignore_empty only affects truly blank cells. A cell containing 0 is not empty, so it will be included. If you want to exclude zeros, use an IF inside the formula: =TEXTJOIN(", ", TRUE, IF(A2:A10<>0, A2:A10, "")).

Is there a limit to how many characters TEXTJOIN can return? Yes, the result cannot exceed 32,767 characters, which is the cell limit in Excel. If your joined text is longer, you will get a #VALUE! error. In that case, consider splitting the output into multiple cells or using a different approach.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.