TextJoin in Google Sheets

What Is TextJoin in Google Sheets?

TEXTJOIN is a powerful alternative to functions like CONCATENATE, helping you join text with a delimiter in between. In other words, it combines the text from multiple strings, ranges, or cells using a specified delimiter. It will ignore empty cells or include them in the result. This function helps you combine data from multiple cells in a readable and organized form.

As an example, we have some values in a Google sheet. Let us try to join these values with a delimiter. Apply the following function in cell A5. =TEXTJOIN(“-“, TRUE,A2:A4). You can observe the text joined in the output. Thus, the text can be joined flexibly as compared to the CONCATENATE function.

Syntax

The syntax of the TEXTJOIN function in Google Sheets is as follows:

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

where,

1. delimiter (mandatory) – A string that is a delimiter when joining the text. It can also be empty. A number given here is also considered a delimiter.
2. Ignore_empty (mandatory) – Determines whether to ignore empty cells or not based on BOOLEAN values.
   • TRUE – ignore blank cells.
   • FALSE – include blank cells in the result.
3. Text1 (mandatory) – first value to join. It can be a string, a cell reference, or a range.
4. Text2, … (optional) – additional text values to be joined.

How To use TextJoin Function in Google Sheets?

As seen above, TEXTJOIN in Google Sheets combines the text from multiple strings or cells along with a specifiable delimiter separating the text. Let us look at how we can use this function with a simple example. Let us combine the values in the cells A1:D1 into one cell, separating the values with a comma and space to print it as a whole address.

Enter the details in the Google Sheets cells manually, as shown below.

Step 1: To combine it into a single address, we will apply the TEXTJOIN formula with a comma and space. Use the TEXTJOIN in Google sheets in cell B2, as shown below.

=TEXTJOIN(

Step 2: Enter the parameters according to the syntax as shown below.

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

We enter =TEXTJOIN(“,  “,TRUE,A1:D1) in cell B2. Here, a comma followed by a space is the delimiter.

Step 3: Press Enter. You can see how the address is displayed in cell B2.

Using the Google Menubar

1. Choose the cell where you want to enter the formula for TEXTJOIN.
2. Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Text” ➝ “TEXTJOIN.”
3. Enter the delimiter, the Boolean value on whether to ignore empty cells, and the range as arguments. Close the bracket and press the “Enter” key.

Examples

Using the CONCATENATE function or operator to join text, especially with a delimiter, is a tedious process. However, we have JOINTEXT in Google Sheets, which helps you combine text easily. Let us look at some interesting examples of this topic below.

Example #1 – Join Text with Spaces

Let us look at how to join text when there are spaces or empty cells. Given below are some movie names split into separate columns. We must combine them to show the exact movie name.

Step 1: Let us consider the conditions where we ignore the empty cells or space in B5. Apply the following TextJoin in Google Sheets formula to cell D2.

=TEXTJOIN(“ “, TRUE, A2:C2)

Step 2: Press Enter. You can drag the choose the Autofill option to get the result for all the movies.

Step 3: Notice how the space in B5 has been ignored. This is because we gave the second parameter TRUE in TEXTJOIN in Google Sheets.

Step 4: Now, let us check what happens if we give FALSE in the function. Enter the following formula in cell E2. We have added a new entry, Sindbad, in cell C7, which has only one word.

=TEXTJOIN(“ “, FALSE, A2:C2)

Step 5: Press Enter and use the Autofill option to see the results for all the movies.

You notice the space between the words King Kong in both columns D and E. Column E has more space between them as there was an empty cell.

Also, Sindbad does not appear at the beginning of the cell but at the end since it does not ignore the two empty cells in the beginning (A7 and B7).

Example #2 – Join Data From Multiple Cells

Let us look at a list of names of some students at a university. You must combine their initials and surnames and place them in a single cell. You should also display only the first letter of their surnames as an initial.

Step 1: To use TEXTJOIN in Google Sheets, enter the data in the sheet below.

Step 2: Let us try to understand how to display their names. We combine the first and surname and print only the first name and initial letter of the surname. For instance, John Steward will be John S.

Enter the following formula in cell C2.

=TEXTJOIN(“ “,TRUE, A2,LEFT(B2,1))

Here,

• The delimiter is space “ “.
• We use the LEFT function to extract the first character from cell B2.

Step 3: Press Enter. Observe the result.

Step 4: To apply this result to the other cells, drag and drop the formula using Autofill up to C8.

You can observe how the TEXTJOIN function has been successfully used to combine the two names and display them with a space in between.

Example #3 – Join Text with Google Sheets TextJoin new Lines

In this example, we have some lines from a lovely poem by Robert Frost in different cells. Let us look at how to join them with a line break as a delimiter.

Step 1: To join the text in these cells with a line break delimiter, enter the following formula in cell C2.

=TEXTJOIN(CHAR(10), TRUE, A1:A4)

Step 2: In case you are wondering what CHAR(10) is in the place of delimiter, it is used to represent a line break in Google Sheets. Press Enter.

You can see how the text in all four cells has been joined together with a Google Sheets TextJoin new line.

Alternative to TextJoin function in Google Sheets

TEXTJOIN in Google Sheets is one of the most exciting functions as it helps you join text with a delimiter. However, it is not the only function available to join text. We have other functions for this. These include the CONCATENATE, & operator, and JOIN functions.

JOIN is used to concatenate elements of one or more one-dimensional arrays using a specified delimiter. For example, if there are three words in an array, you can join them as follows using a delimiter, as the function takes two arguments

=JOIN(” “,A1:A3)

The difference between JOIN and TEXTJOIN is that JOIN works with only one-dimensional arrays. Hence, you can only give one row or one column as an argument.

CONCATENATE is used to join text as well, but the problem is when you need a delimiter. You need to specify it each time, unlike using TEXTJOIN. Let us look at how we applied it to the same example as above.

Notice how the “&” sign helps in the concatenation as we have delimiters.

The CONCAT function is used to combine data from two cells, but its limitation is that you can only link two cells. Also, it doesn’t support operators to add any delimiters. It allows only two arguments, i.e., the two text or two cell references.

Important Things To Note

1. A number we give to TEXTJOIN for the delimiter argument or as text arguments is converted to text.
2. You can use a text string, special characters, spaces, or even other cell references as delimiters.
3. We can use a maximum number of 252 arguments in TEXTJOIN in Google sheets.
4. CHAR(10) is used as a line break delimiter.
5. If no delimiter is specified, the text is concatenated without any delimiter.
6. You may get an #VALUE error if the resulting string is greater than 32,767 characters.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand TextJoin in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is a TextJoin in Google Sheets. We explain how to use TextJoin in Google Sheets with examples. You can learn more from the following articles

Text function in Google Sheets

COUNTBLANK In Google Sheets