HomeGoogle DocsSpreadsheetDifference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets

Difference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets

Published on

There are four main ways to join text in Google Sheets: using JOIN, TEXTJOIN, CONCATENATE, and ampersand (&) functions.

The CONCAT and QUERY functions can also be used to join text, but they are not as popular as the other four functions.

The CONCAT function is a simpler function that does not offer as many options as the JOIN or TEXTJOIN functions.

The QUERY function is a more powerful function that can be used to manipulate data in Google Sheets. It is not specifically designed for joining text, but it can do so by specifying an arbitrarily large number in the QUERY header. We will see how to do this in another tutorial.

I have already explained the use of CONCATENATE and the ampersand. Now let us learn the functions JOIN and TEXTJOIN. I think it is important to know the difference between these three functions so that you can choose the right one for your needs.

How to Use JOIN Function in Google Sheets

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

Syntax:

JOIN(delimiter, value_or_array1, [value_or_array2, …])
  • delimiter: The character or string to place between each joined value. This can be a comma, space, hyphen, or any other value.
  • value_or_array1: The value or values to be joined. This can be a single cell, a range of cells (one-dimensional), or a list of values.
  • value_or_array2, …: Additional values or arrays (one-dimensional) to be joined.

JOIN Formula Example 1:

=JOIN(", ",C40:C45)
JOIN formula example 1 - Google Sheets

The above JOIN formula joins the values (addresses in separate rows) in the cell range C40:C45. When joining, the formula places a comma and space as delimiters to return a proper address in a single cell.

JOIN Formula Example 2:

=JOIN(": ",C40:C42,D40:D42)
JOIN formula example 2 - Google Sheets

In this Google Sheets JOIN function example, you can see that there are two arrays in use. It returns the same result as example 1 above, with the only difference being the delimiter in use. In this case, the delimiter is a colon (;) and a space, instead of a comma (,) and a space.

The below formula will not work as the JOIN function only supports one-dimensional arrays.

=join( " : " ,C40:D42)

Here is the use of the 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 a given delimiter.

Syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])
  • delimiter: The character or string to place between the joined texts. This can be a comma, space, hyphen, or any other value.
  • ignore_empty: A Boolean TRUE or FALSE (you can also use 1 or 0 [zero]). It controls blank cells in the selected range.
  • text1: The array containing the text to join or the text itself. This can be a single cell, a range of cells, or a list of values.
  • text2, …: To include additional arrays (usually to combine distant ranges) or texts.

Here I will try to explain the syntax with a few examples.

The TEXTJOIN Formula Example 1:

=TEXTJOIN(", ",TRUE,C52:D53)
Google Sheets TEXTJOIN Formula Example 1

Please note that there are no blank cells in the selected range. Therefore, the result will be the same whether you use FALSE or TRUE for the ignore_empty argument.

The TEXTJOIN Formula Example 2:

=TEXTJOIN(", ",TRUE,C58:D60)
Google Sheets TEXTJOIN Formula Example 2

Here, there are blank cells in between the selected array. Therefore, I used a Boolean TRUE in the formula to ignore these blank cells.

The TEXTJOIN Formula Example 3:

See what happens when the ignore_empty argument is set to FALSE in the formula when there are blank cells. The comma delimiter separator will also appear for the blank cells.

=TEXTJOIN(", ",FALSE,C63:D65)
TEXTJOIN Formula Example 3

Note: When using a two-dimensional array in the TEXTJOIN function, the formula combines the values by row, not by column.

Comparison of Google Sheets JOIN, TEXTJOIN, and CONCATENATE Functions

I am using three text join functions—JOIN, TEXTJOIN, and CONCATENATE—and the fourth one, &, to join the texts in A1:G1 below. See how each formula differs.

Comparison of Google Sheets join Functions

My expected result is as follows: JOIN✓ ME✓ PLEASE✓ WITH✓ A✓ TICK MARK

1. Using the AMPERSAND (&):

=A1&"✓ "&B1&"✓ "&C1&"✓ "&D1&"✓ "&E1&"✓ "&G1

2. Using the CONCATENATE Text Function:

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

3. Using the JOIN Text Function:

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

4. Using the TEXTJOIN Text Function:

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

I know you want to learn how to use the QUERY function to combine values in Google Sheets. Please check out this tutorial: The Flexible Array Formula to Join Columns in Google Sheets.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here