TEXTJOIN and CONCAT in Excel

TEXTJOIN and CONCAT join text from multiple cells into one string. TEXTJOIN adds a delimiter between items and can skip empty cells, while CONCAT simply stitches values together with no separator at all. Both accept whole ranges, which makes them far cleaner than the old CONCATENATE function that required listing each cell one at a time, and both are useful for building labels and keys in financial models.

Syntax and how they differ

The signatures are =TEXTJOIN(delimiter, ignore_empty, text1, ...) and =CONCAT(text1, ...). TEXTJOIN takes a delimiter to place between items and a TRUE or FALSE ignore_empty flag that controls whether blank cells are skipped.

CONCAT has no delimiter and no empty handling. It just joins everything in order. The headline advantage of both over CONCATENATE is that they accept whole ranges, so =CONCAT(A1:A5) works, whereas CONCATENATE needed each cell spelled out. TEXTJOIN and CONCAT are available in Excel 2019 and Excel 365.

Worked example: build a dynamic label

A region is in A2, a product in B2, and a quarter in C2. You want a single label joined with a space and a dash, and you want empty parts skipped so the label never shows a stray dash.

1. Enter =TEXTJOIN(" - ", TRUE, A2, B2, C2).
2. With West, Widgets, and Q3 in the cells, the result is West - Widgets - Q3.
3. Clear B2 so the product is blank. Because ignore_empty is TRUE, the result becomes West - Q3 with no double delimiter.
4. Switch to =CONCAT(A2, B2, C2) and the same full set returns WestWidgetsQ3 with no separators.

With ignore_empty set to TRUE, the blank B2 in row 2 drops out and no extra dash appears.

How analysts use TEXTJOIN and CONCAT

These functions assemble keys and captions that adapt as inputs change.

• Build a composite key like region and quarter to match against a lookup table.
• Create chart titles and summary captions that update with the selected scenario.
• Join a list of line items into one cell for a comment or note, separated by commas.
• Use ignore_empty so optional fields drop out cleanly without leaving dangling delimiters.

Common pitfalls

Remember that both functions return text, so a joined number loses its formatting. To keep a currency or date format, wrap the value in TEXT, for example =TEXTJOIN(" ", TRUE, A2, TEXT(B2, "$#,##0")), so the number reads the way you intend.

Compatibility is the other watch out. If you save a workbook for someone on Excel 2016 or earlier, TEXTJOIN and CONCAT show a #NAME? error there. Also note that ignore_empty only skips truly blank cells, not cells containing a formula that returns an empty string, which can still leave an unexpected delimiter.