Excel Exercises Name

Excel TEXTJOIN Function

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 Function Syntax

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], ...)

  • Delimiter or separator argument takes text, a cell reference, or cell range and is the text that goes in between each text argument in the resulting string of text. You might choose to put a space " " 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.
  • Ignore Empty argument takes true or false and specifies whether the function should ignore any empty cells that are supplied as arguments. If set to False, TEXTJOIN will include the empty cells in the final result.
  • Text arguments are the text, individual cells, or range of cells you wish to join together separated by the delimiter. You must supply at least one text argument but you can supply up to a maximum of 252 text arguments.

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.



Examples of the TEXTJOIN Function 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 after John): "John, , Mike, Bill".



Types of Arguments

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)


Excel Exercises with TEXTJOIN Functions

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!