TEXTJOIN Function

Today in this article I will discuss about a new excel function TEXTJOIN, an advanced version of CONCAT or CONCATENATE.

As the name indicates it is used to join texts or strings from more than one cell and you can use a separator if require. It works in the same manner the function CONCAT or CONCATENATE works.

One obvious question arises in our mind that if CONCATENATE & CONCAT is available in Microsoft Excel then what is the justification to introduce another function which works same, joining texts ?

There are differences between these three functions although they are used to get the same result.

Let’s discuss the function.

Description

The Microsoft Excel TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

The TEXTJOIN function is a built-in function in Excel categorized as a String/Text Function and TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

Syntax

The syntax for the TEXTJOIN function in Microsoft Excel is

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

Parameters or Arguments

delimiter

It is your separator, string or a single character inserted between each text value in the resulting string. Most commonly, a comma or space character or a hyphen.

ignore_empty

Determines whether empty values are included in the resulting string.

TRUE ignores empty values and FALSE includes empty values in the result.

This is most interesting and useful parameter in TEXTJOIN function compared to CONCAT or CONCCATENATE function, there was no such scope to avoid blank cells. Our example will clarify the usefulness of this parameter.

text1, text2, ... text_n

The strings that you wish to join. There can be up to 252 strings that are joined together.

Returns

The TEXTJOIN function returns a string/text value.

Note : This function is only available to Excel 2016, not before this version.

Example : 

Suppose we  need to join the characters as shown in the below table. There are two functions CONCAT and TEXTJOIN.

TEXTJOIN Function in Microsoft Excel

If we use CONCAT function the formula will be like :

=CONCAT(A6,",",B6,",",C6,",",D6,",",E6)

whereas if we use TEXTJOIN function the formula becomes :

=TEXTJOIN(",",TRUE,A5,B5,C5,D5,E5)

Very simple, lets analyze these two functions. As you are aware CONCAT is simple, type =CONCAT and an opening bracket then select the CELLs and after one selection of cell you must type your separator (for my case it is “,”). After you complete selecting the range press enter and you are done.

In TEXTJOIN function what you need to do is type =TEXTJOIN and an opening bracket then

1.   First type your SEPERATOR (may be comma or a hyphen)

2.   Put a comma, you will get two options,

a.   TRUE – Ignore empty cells

b.   FALSE – Include empty cells

TEXTJOIN Function in Microsoft Excel

3.   These options are self-explanatory, if you want to ignore the empty cells, it will not be displayed including the SEPERATOR. But if you select the FALSE statement then this cell content will be counted and a separator will be visible which CONCAT function normally does.

4.   Now select the ranges you want to join.

From the above example, we can notice that the using TEXTJOIN is much easier than the usual CONCAT or CONCATENATE function.

Difference between CONCAT or CONCATENATE and TEXTJOIN

1.   you don’t to type the separator repetitively but if you need two different separators then this function will not help, you must use CONCAT function.

2.   Unlike CONCAT function you are provided by an option to exclude or include the blank cells. If there is a blank cell and you use CONCAT function the output gives us TWO SEPERATOR one after another. But if you use TEXTJOIN function this problem will not occur.

3.   The most useful parameter is the RANGE selection. There is no way to select the whole range in a row or column using CONCAT or CONCATENATE function. The beauty of TEXTJOIN is that if the data is available in a row or column you can easily provide the range as we do for other functions like SUM function, the formula then becomes, =TEXTJOIN(",",TRUE,A5:E5).

4.   TEXTJOIN allows to use the ARRAY within this function, I will discuss it latter in another article.

I hope you have enjoyed this article.

Thank you for reading….