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.
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], ...)
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.
=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 after John): "John, , Mike, Bill".
The TEXTJOIN function can take strings of text (enclosed in quotes), cell references, and ranges of cells as arguments.
=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.
=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:
As with all functions in Excel, the best way to learn is by getting your hands dirty. The more repetitions you get, the more quickly and naturally you'll be able to use them in your everyday work.
Try some Excel Exercises with the TEXTJOIN function now!