In a Nutshell
The TEXTJOIN function combines the text from multiple ranges and/or strings in one cell. It provides the option to add delimiting characters (ike dashes or spaces) between all the different strings being joined together.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- delimiter (required) – A text string to be used as a delimiting character between all the other strings that we are joining together. It can be empty, or one or more characters enclosed by double quotes, or a cell reference to a valid text string. If a number is supplied, it will be treated as text.
- ignore_empty (required) – If TRUE, then empty cells are ignored
- text1 (required) – Text item to be joined. A text string, or array of strings, such as a range of cells.
- [text2, …] (optional) – Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1. Each can be a text string, or array of strings, such as a range of cells.
Notes
The TEXTJOIN function is a worksheet function so it can be entered as part of a formula in a worksheet cell (see examples below).
TEXTJOIN and CONCAT replaced the CONCATENATE function in Excel 2019, but the CONCATENATE function will stay available for compatibility with earlier versions of Excel.
Examples
Let us have a look at the following examples (Download the example workbook to follow along):
- Example 1 – Joining together a product code
- Example 2 – Joining together a phone number with no delimiter
- Example 3 – Listing items with a comma as delimiter
- Example 4 – Listing items vertically using a CHAR(10) delimiter
- Example 5 – A comparison of “ignore_empty” being TRUE vs FALSE
- Example 6 – Use CONCAT in order to use different delimiters
Example 1 – Joining together a product code
In the table below we have various pieces of text that we want to combine into one product code, but with a “dash” as a delimiting character:
E.g. we want to combine the values in cells B3 to D3 (ABDT, SD87 and SD87) into one cell, as follows: ABDT-SD87-SD87.
In cell E3 we enter the following formula: =TEXTJOIN("-", TRUE, B3:D3)
The strings in the range B3 to D3 are joined together, using a dash (“-“) as delimiter between the individual parts.
ignore_empty is TRUE, meaning that if there were any blank cells in the range from column B to column D, it would be ignored (see example 5 below to see the difference it would make using FALSE instead of TRUE for ignore_empty).
Example 2 – Joining together a phone number with no delimiter
In cell I3 in the table below we have combined the seven individual digits in the range B3 to H3, with no delimiters between the items:
The seven individual digits in the range B3 to H3 are joined together in one cell (I3), using no delimiter between the individual parts.
Between the brackets we normally have a delimiter, then TRUE or FALSE, then the range. But there is no delimiter in the combined phone number. So,
in the brackets we have the following: nothing before the first comma, then TRUE, then the range. If you forget the first comma it won’t work!😉
Example 3 – Listing items with a comma as delimiter
In this example we want to combine the text strings in the cells in columns B to E.
We want to present it as “comma separated values” in the respective cells in column F:
In cell F3 we have the following formula: =TEXTJOIN(",", TRUE, B3:E3)
The strings in the range B3 to E3 are joined together, using a comma (“,”) as delimiter between the individual items.
Example 4 – Listing items vertically using a CHAR(10) delimiter
For each row, we want to combine all of the text strings in the cells from column B to column E, and present it as a vertical list in the cell in column F.
E.g. we want to show the values in cells B3 to E3, as a vertical list in cell F3.
We can use the Excel CHAR function to insert a line-break character. We do this by using CHAR(10) as a delimiter in our TEXTJOIN function:
So in cell F3 we have the following formula: =TEXTJOIN(CHAR(10), TRUE, B3:E3)
The strings in the range B3 to E3 are now joined together, using a line break as delimiter between the individual items, to give us a vertical list in cell F3.
Note: To see the vertical list, just make sure Wrap Text is enabled in the cells in column F. To do this, select the cells for which you want Word Wrap enabled, then click Wrap Text on the Home tab:
Example 5 – Comparing “ignore_empty” TRUE vs FALSE
In all of the examples above, the ignore_empty argument was TRUE.
For example, in Example 1 above the first entry was joined in cell E3 as follows: =TEXTJOIN("-", TRUE, B3:D3)
In the similar example below, if we leave the second part blank, we won’t have two dashes (delimiters) next to each other, as it knows to ignore the empty cell.
In cell E14 we have: =TEXTJOIN("-", TRUE, B14:D14)
If we change the formula to =TEXTJOIN("-", FALSE, B14:D14), the blank cell is not ignored, resulting in two delimiters showing up next to each other:
Example 6 – Using CONCAT instead, in order to use multiple delimiters
In the example below, for each row, we want to combine the text strings in the cells in columns B to F as a combined string in column F. (e.g. the values in cells B3 to E3 are to be combined in an entry in cell G3).
But for this example we want to use a dash (-) as delimiter between the first parts and an equals sign (=) as delimiter between items two and three.
Unfortunately we can’t easily use TEXTJOIN for this, as there is only one “Delimiter” argument in the function.
As a workaround we can use the CONCAT function instead. We add “separator” columns and enter the delimiters in the table, and then join all of the items using CONCAT.
So in cell F3 we have the following formula: =CONCAT(B3:F3)
If you want to see more examples of how the CONCAT function is used, you can click here.
In case you missed it, here is a link to the example workbook.
Errors
TEXTJOIN produces a #VALUE! error if the resulting string exceeds 32,767 characters.
Excel Versions Supported
Excel 2019 onwards
