The TEXTJOIN Function | Full Stack Modeller

Concatenates values together from cells or ranges of cells

What does Excel's TEXTJOIN function do?

The TEXTJOIN function is one of Excel's text functions.

The TEXTJOIN function concatenates (or joins) values together from cells or ranges of cell. 

This function is a great improvement on the CONCAT and CONCATENATE functions as it allows you to include a delimiter and to choose whether or not to ignore empty cells.

Here's a simple example

Let's take a look at a simple example of the TEXTJOIN function.

We would like to concatenate the three words "Full", "Stack" and "Modeller" together from the range of cells A1 to A3 and insert a space delimiter between each word.

=TEXTJOIN(" ",,A1:A3)

What does that mean in plain English?

Join the three words "Full", "Stack" and "Modeller" together from the range of cells A1 to A3 and insert a space (delimiter) between each word. Ignore empty cells.

Note that the second arguement "ignore_empty" is left blank. As the function defaults ot TRUE this will ignore any empty cells. The function could also have been written as: =TEXTJOIN(" ",TRUE,A1:A3)

How do I write a formula using the TEXTJOIN function?

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Delimiter – Seperator between each value (space, comma, hyphon)

Ignore empty – Ignore empty cells or not (default is to ignore)

text1 – The first value or range of values to join

What to consider when using the TEXTJOIN function in your  model

• The TEXTJOIN function was introduced in 2019, so be aware of compatibility issues for users of previous Excel versions.

Read more about the TEXTJOIN function on the Microsoft support page here.