Difference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets

0
80
JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets

There are four ways to join text in Google Sheets. They are using the functions JOIN, TEXTJOIN, CONCATENATE or the Ampersand. I’ve already explained the use of CONCATENATE as well as Ampersand. Now let us learn the functions JOIN and TEXTJOIN. Also I think you should know the difference of Google Sheets JOIN, TEXTJOIN, CONCATENATE Functions to properly use them.

How to Use JOIN Function in Google Sheets

Google Sheets JOIN function joins the elements of one or more one-dimensional arrays where the joined elements can separated by given delimiter.

Syntax:

JOIN(delimiter, value_or_array1, [value_or_array2, …])

Here delimiter is the character or string to place between each joined values like commas, space, hyphen or any text.

value_or_array1 – The value or values to be joined.

JOIN Formula Example 1:

JOIN formula example 1 - Google Sheets

JOIN Formula Example 2:

JOIN formula example 2 - Google Sheets

In this Google Sheets Join Formula example, you can see that there are two arrays in use. If you apply the formula like below, it won’t work as JOIN function only supports one dimensional arrays.

=join( ” : ” ,C40:D42)

Here comes the use of Google Sheets TEXTJOIN Function.

How to Use TEXTJOIN Function in Google Sheets

Google Sheets TEXTJOIN function joins text from multiple arrays where the joined texts can be separated by given delimiter.

Syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

delimiter: is the character or string to place between texts.

ignore_empty: A Boolean TRUE or FALSE. It controls blank cells in the selected range.

text1: The array containing the text to join or the text itself.

Here let me try to explain the syntax with few examples.

TEXTJOIN Formula Example 1:

Please note that there is no blank cells in between the selected range.

Google Sheets TEXTJOIN Formula Example 1

TEXTJOIN Formula Example 2:

Here there are blank cells in between the selected array. So a Boolean TRUE used in the formula.

Google Sheets TEXTJOIN Formula Example 2

TEXTJOIN Formula Example 3:

See what happens when a Boolean FALSE used in the formula when there are blank cells. See the Comma delimiter separator appears for the blank cells too.

TEXTJOIN Formula Example 3

Comparison of Google Sheets JOIN, TEXTJOIN, CONCATENATE Functions

I am using all the above three text join functions – JOIN, TEXTJOIN, CONCATENATE Functions – and the fourth one, “&”, to join the texts below. See how each formulas differ.

Comparison of Google Sheets join Functions

Expected Result: JOIN✓ ME✓ PLEASE✓ WITH✓ A✓ TICK MARK

1. Using AMPERSAND (&)

=A1&”✓ “&B1&”✓ “&C1&”✓ “&D1&”✓ “&E1&”✓ “&G1

2. Using CONCATENATE Text Function.

=CONCATENATE(A1,”✓ “,B1,”✓ “,C1,”✓ “,D1,”✓ “,E1,”✓ “,G1)

3. Using JOIN Text Function.

=JOIN(“✓ “,A1:E1,G1)

4. Using TEXTJOIN Text Function.

=TEXTJOIN(“✓ “,TRUE,A1:G1)

That’s all. Enjoy.

LEAVE A REPLY

Please enter your comment!
Please enter your name here