Are you tired of manually combining text in your Google Sheets? Do you find yourself spending too much time on this tedious task? Well, worry no more! In this article, I will show you the power of JOIN, TEXTJOIN, and CONCATENATE functions in Google Sheets. These functions will save you time and effort by automatically joining text in a breeze. Let’s dive in!
How to Use JOIN Function in Google Sheets
The JOIN function in Google Sheets allows you to join the elements of one or more one-dimensional arrays, with the option to specify a delimiter. This function is perfect for combining values in separate cells or ranges. Let’s take a look at the syntax:
JOIN(delimiter, value_or_array1, [value_or_array2, …])
Here’s an example of how to use the JOIN function:
=JOIN(", ",C40:C45)
In this formula, we join the values in the cell range C40:C45, using a comma and a space as the delimiter. This will return a proper address in a single cell.
You can also use multiple arrays with the JOIN function, like this:
=JOIN(": ",C40:C42,D40:D42)
In this example, we have two arrays, and the delimiter is a colon and a space. The result will be similar to the previous example, but with a different delimiter.
Please note that the JOIN function only supports one-dimensional arrays. So, if you try to combine two-dimensional arrays, it won’t work.
Now, let’s move on to the TEXTJOIN function.
How to Use TEXTJOIN Function in Google Sheets
The TEXTJOIN function in Google Sheets allows you to join text from multiple arrays, with the option to specify a delimiter. This function is handy when you want to combine text from different cells or ranges. Let’s take a look at the syntax:
TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])
Here’s an example of how to use the TEXTJOIN function:
=TEXTJOIN(", ",TRUE,C52:D53)
In this formula, we join the text in the range C52:D53, using a comma and a space as the delimiter. The TRUE value for the ignore_empty argument ensures that any blank cells in the range are ignored.
You can also use the TEXTJOIN function with arrays that have blank cells, like this:
=TEXTJOIN(", ",TRUE,C58:D60)
In this example, there are blank cells between the selected array, so we use TRUE to ignore these blank cells.
If you set the ignore_empty argument to FALSE, the formula will include the delimiter for blank cells, like this:
=TEXTJOIN(", ",FALSE,C63:D65)
It’s important to note that when using a two-dimensional array with the TEXTJOIN function, the formula combines the values by row, not by column.
Now that you know how to use the JOIN and TEXTJOIN functions, let’s compare them to the CONCATENATE function.
Comparison of Google Sheets JOIN, TEXTJOIN, and CONCATENATE Functions
In addition to JOIN and TEXTJOIN, Google Sheets also has the CONCATENATE function, which can be used to join text. However, CONCATENATE is not as popular as JOIN and TEXTJOIN. Let’s see how each function differs:
-
Using the AMPERSAND (&):
=A1&"✓ "&B1&"✓ "&C1&"✓ "&D1&"✓ "&E1&"✓ "&G1 -
Using the CONCATENATE function:
=CONCATENATE(A1,"✓ ",B1,"✓ ",C1,"✓ ",D1,"✓ ",E1,"✓ ",G1) -
Using the JOIN function:
=JOIN("✓ ",A1:E1,G1) -
Using the TEXTJOIN function:
=TEXTJOIN("✓ ",TRUE,A1:G1)
As you can see, each function has its own syntax and usage. Depending on your specific needs, you can choose the most suitable function to join your text.
If you’re interested in learning how to use the QUERY function to combine values in Google Sheets, check out my tutorial on Crawlan.com: “The Flexible Array Formula to Join Columns in Google Sheets.”
Now that you have the power of JOIN, TEXTJOIN, and CONCATENATE at your fingertips, say goodbye to manual text combining in Google Sheets. You’ll be amazed at how much time and effort you’ll save with these powerful functions. Happy sheeting!
