What is TEXTJOIN function in Excel?
TEXTJOIN function is one of TEXT functions in Microsoft Office Excel that
Note: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.
Syntax of TEXTJOIN function
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
| argument | Description |
| delimiter (required) |
A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text. |
| ignore_empty (required) |
If TRUE, ignores empty cells. |
| 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. |
For example, =TEXTJOIN(” “,TRUE, “The”, “sun”, “will”, “come”, “up”, “tomorrow.”) will return The sun will come up tomorrow.
- If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.
Examples of TEXTJOIN function
Steps to follow:
1. Open a new Excel worksheet.
2. Copy data in the following table below and paste it in cell A1
Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.
You can adjust the column widths to see all the data, if need be.
Example 1
| City | State | Postcode | Country |
| Tulsa | OK | 74133 | US |
| Seattle | WA | 98109 | US |
| Iselin | NJ | 08830 | US |
| Fort Lauderdale | FL | 33309 | US |
| Tempe | AZ | 85285 | US |
| end | |||
| , | , | , | ; |
| Formula: | =TEXTJOIN(A8:D8, TRUE, A2:D7) | ||
| Result: | Tulsa,OK,74133,US;Seattle,WA,98109,US;Iselin,NJ,08830,US;Fort Lauderdale,FL,33309,US;Tempe,AZ,85285,US;end |
Example 3
| Currency | |
| US Dollar | |
| Australian Dollar | |
| Chinese Yuan | |
| Hong Kong Dollar | |
| Israeli Shekel | |
| South Korean Won | |
| Russian Ruble | |
| Formula: | =TEXTJOIN(“, “, TRUE, A2:A8) |
| Result: | US Dollar, Australian Dollar, Chinese Yuan, Hong Kong Dollar, Israeli Shekel, South Korean Won, Russian Ruble |
Example 2
| A’s | B’s |
| a1 | b1 |
| a2 | b2 |
| a4 | b4 |
| a5 | b5 |
| a6 | b6 |
| a7 | b7 |
| Formula: | =TEXTJOIN(“, “, TRUE, A2:B8) |
| Result: | a1, b1, a2, b2, a4, b4, a5, b5, a6, b6, a7, b7
If ignore_empty=FALSE, the result would be: a1, b1, a2, b2, , , a4, b4, a5, b5, a6, b6, a7, b7 |