The TEXTJOIN function in Excel joins multiple text and cell values and returns one string of text. TEXTJOIN is very similar to the CONCAT function in that it joins multiple pieces into a single text string, but TEXTJOIN has some more advanced features which make it more useful than CONCAT in certain situations, especially when you are trying to list several items from your Excel file in an organized way. For example, TEXTJOIN lets you join several text values from a list in Excel into a comma-separated list.
TEXTJOIN lets you choose a separator (or "delimiter") text to put in between each argument (values in the function separated by commas). TEXTJOIN is written as follows:
=TEXTJOIN(delimiter, ignore_empty, text_1, [text_2], [text_3], ...)
" " or a comma and space ", " between each text argument. You could also choose to separate them with nothing "" but at that point you might as well use the CONCAT function, which joins text values together without any separation.The maximum length string that TEXTJOIN can return is 32,767 characters, as this is the limit of what a cell can hold in Excel.
There are many ways we could use the TEXTJOIN function to join all these names into a single string.
| A | B | |
|---|---|---|
| 1 | John | |
| 2 | ||
| 3 | Mike | |
| 4 | Bill |
=TEXTJOIN(" ", True, A1:A4) Because this function specifies a space as the delimiter and ignores empty cells, this function would return "John Mike Bill".
=TEXTJOIN(", ", True, A1:A4)Because this function specifies a comma and space as the delimiter and still ignores empty cells, this function would return "John, Mike, Bill".
=TEXTJOIN(", ", False, A1:A4)This function does not ignore empty cells, so TEXTJOIN will insert the delimiters around the empty value (note the extra comma and space after John): "John, , Mike, Bill".
The TEXTJOIN function can take strings of text (enclosed in quotes), cell references, and ranges of cells as arguments.
| A | B | |
|---|---|---|
| 1 | ||
| 2 | I | Excel |
| 3 | ||
| 4 |
=TEXTJOIN(" ", True, A2, "love", B2)returns "I love Excel".
The delimiter can also be a cell reference or range of cells. By referencing a range of cells, TEXTJOIN will cycle through the delimiters until it runs out of arguments to join together.
| A | B | |
|---|---|---|
| 1 | Montana | Alaska |
| 2 | Wyoming | Idaho |
| 3 | Oklahoma | Texas |
| 4 | , | - |
=TEXTJOIN(A4, True, A1:B2)would return "Montana,Alaska,Wyoming,Idaho". Notice that when using TEXTJOIN on a range of cells that includes multiple rows and multiple columns, Excel will read from left to right, then top to bottom.
=TEXTJOIN(A4:B4, True, A1:B3)would return "Montana,Alaska-Wyoming,Idaho-Oklahoma,Texas". Notice that the delimiter character cycles through the values supplied in the first argument of the function.
As mentioned before, the delimiter argument can simply be an empty string "" in which case the outcome would be the same as that of the CONCAT function. Suppose we had the following function:
=TEXTJOIN("", True, A1:B2)TEXTJOIN would give us the following result: "MontanaAlaskaWyomingIdaho" which is the same result we would get with the following CONCAT function:
=CONCAT(A1:B2)If TEXTJOIN is so similar to CONCAT, what's the difference? While CONCAT is good for joining together text exactly as it appears into one long string, TEXTJOIN is good for joining multiple items from Excel into a human-readable list. For example, say your boss sends you an email asking who the top 5 sales people are. You pull up your Excel sales report below, and it appears that TEXTJOIN is the tool for the job.
| A | B | |
|---|---|---|
| 1 | NAME | SALES ($) |
| 2 | Brad | 12,000 |
| 3 | Janet | 11,800 |
| 4 | Frank | 10,500 |
| 5 | Eddie | 8,000 |
| 6 | Rocky | 7,200 |
| 7 | ... | ... |
Now you could use CONCAT and send your boss a string of text that says "BradJanetFrankEddieRocky" but your boss wouldn't be too thrilled. Instead, you could use TEXTJOIN to send her a nice, readable, list.
=TEXTJOIN(", ", True, A2:A6)The function above would return "Brad, Janet, Frank, Eddie, Rocky". That's much better. This is a small example, but if you were dealing with a much larger list you could see how valuable TEXTJOIN would be.
Now suppose your boss asks who the top 5 sales people were, as well as their sales amounts. TEXTJOIN becomes more valuable still!
=TEXTJOIN({": $", ". "}, True, A2:B6)The TEXTJOIN formula above would return the following: "Brad: $12,000. Janet: $11,800. Frank: $10,500. Eddie: $8,000. Rocky: $7,200". This is an example of where Excel and TEXTJOIN can save you a lot of time and effort.