Excel Exercises Practice 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. For example, TEXTJOIN lets you join several text values from a list in Excel into a comma-separated list.

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 Excel puts 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.

1 John
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".

Types of Arguments

The TEXTJOIN function can take strings of text (enclosed in quotes), cell references, and ranges of cells as arguments.

2 I Excel
=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.

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:


Why Use TEXTJOIN in Excel?

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.

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.

Continue to TEXTJOIN practice exercises!