The TEXTJOIN function is a useful feature in Excel that allows you to concatenate (join) multiple text values together with a specified delimiter (separator) between them. You can use this function to combine text from different cells, ranges, or constants, and optionally ignore empty cells. For example, you can use the TEXTJOIN function to create a list of names, addresses, products, or any other text data.
The TEXTJOIN function is available in Excel 2016 and later versions, or if you have a Microsoft 365 subscription. In this article, we will explain the basic theory of the TEXTJOIN function, the procedures to use it, and a scenario to give a detailed example with real data. We will also show you some other approaches to combine text in Excel.
The syntax of the TEXTJOIN function is:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
where:
- delimiter is the text to use between the values that are joined together. It can be an empty string (“”) or one or more characters enclosed in double quotes (“”), such as a comma (“,”), a space (” “), or a line break (“\n”). If a number is supplied, it will be treated as text.
- ignore_empty is a logical value (TRUE or FALSE) that determines whether to ignore empty cells or not. If TRUE, empty cells are skipped and no delimiter is added for them. If FALSE, empty cells are included and a delimiter is added for them.
- text1 is the first text value or range to join. It can be a cell reference, a range of cells, or a constant text value.
- [text2], … are optional additional text values or ranges to join. You can provide up to 252 text arguments for the TEXTJOIN function.
The TEXTJOIN function returns a text string that is the result of concatenating the text values with the delimiter. If the resulting string exceeds 32767 characters (the cell limit), the TEXTJOIN function returns the #VALUE! error.
Procedures
To use the TEXTJOIN function to combine text in Excel, follow these steps:
- Select a cell where you want to enter the TEXTJOIN function.
- Type =TEXTJOIN( to start the function.
- Enter the delimiter argument, either as an empty string (“”) or as one or more characters enclosed in double quotes (“”), such as “, ” or “\n”.
- Type a comma (,) to separate the arguments.
- Enter the ignore_empty argument, either as TRUE or FALSE, depending on whether you want to ignore empty cells or not.
- Type another comma (,) to separate the arguments.
- Enter the text1 argument, either as a cell reference, a range of cells, or a constant text value.
- Type another comma (,) to separate the arguments.
- Optionally, enter more text arguments, either as cell references, ranges of cells, or constant text values, separated by commas (,).
- Type a closing parenthesis ()) to end the function.
- Press Enter to complete the function and return the result.
Example
Suppose you have a table of customer data in Excel, as shown below:
| Customer ID | First Name | Last Name | |
|---|---|---|---|
| 1001 | Alice | Smith | alice.smith@email.com |
| 1002 | Bob | Jones | bob.jones@email.com |
| 1003 | Charlie | Brown | charlie.brown@email.com |
| 1004 | David | Lee | david.lee@email.com |
| 1005 | Emily | Green | emily.green@email.com |
You want to use the TEXTJOIN function to create a list of customer names, separated by commas, in cell F2. You also want to ignore any empty cells in the table. To do this, you can use the following formula:
=TEXTJOIN(“, “, TRUE, B2:B6, ” “, C2:C6)
This formula uses “, ” as the delimiter, TRUE as the ignore_empty argument, and B2:B6 and C2:C6 as the text arguments. The result is:
Alice Smith, Bob Jones, Charlie Brown, David Lee, Emily Green
You can also use the TEXTJOIN function to create a list of customer emails, separated by line breaks, in cell G2. To do this, you can use the following formula:
=TEXTJOIN(“\n”, TRUE, D2:D6)
This formula uses “\n” as the delimiter, TRUE as the ignore_empty argument, and D2:D6 as the text argument. The result is:
alice.smith@email.com bob.jones@email.com charlie.brown@email.com david.lee@email.com emily.green@email.com
Other Approaches
Besides the TEXTJOIN function, there are some other ways to combine text in Excel, such as:
- The CONCATENATE function, which joins text values without a delimiter. For example, =CONCATENATE(B2,” “,C2) returns Alice Smith.
- The CONCAT function, which joins text values from a range or an array without a delimiter. For example, =CONCAT(B2:C6) returns AliceSmithBobJonesCharlieBrownDavidLeeEmilyGreen.
- The & operator, which joins text values with or without a delimiter. For example, =B2&” “&C2 returns Alice Smith, and =B2&C2 returns AliceSmith.
- The TEXT function, which applies formatting to numbers or dates before joining them. For example, =TEXT(A2,”0000″)&”-“&B2&C2 returns 1001-AliceSmith.
