Join two or more text strings together with a delimiter.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

---

Join two or more text strings together with delimiters and the ability to ignore blank cells. Use either defined values (e.g. "string") or cell references (e.g. C3) or a range (e.g. C3:C7).

TEXTJOIN syntax does allow for delimiter and empty cell arguments, unlike the CONCAT function.

---

TEXTJOIN with text strings.

In the examples below, the TEXTJOIN function is used to concatenate ranges or individual cell references along with delimiters. The first example ignores blank cells; the second includes blank cells.

---

TEXTJOIN with numbers.

In the examples below, the TEXTJOIN function is used to concatenate sales figures together for different months. The second and third examples are the same formula, just with the ignore_empty argument set to TRUE and FALSE respectively.

---

Syntax

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

The TEXTJOIN function consists of 3 or more arguments:

1. delimiter. Required. A text string to delimit the text values with. Can be empty (""), one or more characters enclosed by double quotes (","), or a reference (C3) to a cell with a valid text string. If a number is supplied, it will be treated as text.
2. ignore_empty. Required. TRUE (1) ignores empty cells. FALSE (0) includes empty cells.
3. text1. Required. A text string, cell reference or a range of cells to be joined.
4. text2. Optional. Up to 252 text arguments can be used (including text1).

---

Try it now!

Enter your formulas into the grey cells.

---

Scope

TEXTJOIN can be applied to cell references, ranges or text or number constants.

Text:	=TEXTJOIN(" ",1,"These","words","are","concatenated","with","the","TEXTJOIN","function") || returns "These words are concatenated with the TEXTJOIN function".
Number:	=TEXTJOIN("|",1,1,2,3,4,5,6,7,8,9,0) || returns 1|2|3|4|5|6|7|8|9|0.
Cell reference:	=TEXTJOIN(" ",1,C7," is the largest country by population in "," ",D7) || returns the contents of cell C7, the text string and the contents of cell D7 delimited by spaces and ignoring the empty value.
Cell range:	=TEXTJOIN(",",0,C2:C11) || returns the contents of cells in the range C2:C11 delimited by commas and including empty values.

Caveats

• The TEXTJOIN function is available in Excel 2019 and for Microsoft 365 subscribers.
• The TEXTJOIN function joins up to 252 text items together, up to a total of 32,767 characters.
• The TEXTJOIN function can handle cell ranges, unlike the older CONCATENATE function.
• Delimiters to separate text items must be defined.
• Empty cells can be ignored (ignore_empty = TRUE) or included (ignore_empty = FALSE).
• #VALUE! is returned if the concatenated string exceeds 32,767 characters (cell limit).
• Numbers are converted to text when joined and formatting is not retained.
  • Use the TEXT function to format numbers within a TEXTJOIN formula.
  • e.g. =TEXTJOIN("",1,"The GDP per capita in USD of ",C7," is ",TEXT(D7,"$#,##0.00"))

---

Related Functions

• CONCAT joins two or more text strings but without delimiters or the option to ignore blank cells.

Functions Category

• Text