Don’t forget to share this post

While spreadsheets are more of a home for numbers than for text, there are many occasions when you need to work with the string data type. The names and characteristics of products, the names and addresses of customers, and qualitative data from surveys are just a few of the many text items you regularly find in your Google Sheets. These texts are often split into multiple cells - first name and last name, different address lines - and you need to join them. Fortunately, Google Sheets provides a few solutions, including TEXTJOIN.

In this guide, you will learn about the TEXTJOIN function and its syntax in Google Sheets. You have step-by-step instructions on how to use the function in different scenarios using different options. You will also learn about three other methods you can use to join text items: the ampersand symbol (&), CONCATENATE, and JOIN. Finally, you will learn the differences between CONCATENATE and TEXTJOIN.

What is TEXTJOIN in Google Sheets?

Google Sheets’ TEXTJOIN function combines text from multiple strings or arrays of strings using the delimiter you specify. The delimiter can be a string or a reference to a cell containing one. The function also allows you to determine whether or not empty cells should be ignored.

Google Sheets TEXTJOIN Syntax

The TEXTJOIN function in Google Sheets is as follows:

=TEXTJOIN(delimiter, ignore_empty, text1, …textN*)
  • delimiter: the delimiter you want between the text items. It can be a string or a reference to a cell containing a string.
  • ignore_empty: a Boolean parameter. If TRUE, empty cells will be ignored.
  • text1: any text item, which could be a string or an array of strings in a range of cells.
  • textN*: optional. Additional text items are also in the form of strings or arrays of strings.

How Do I Use TEXTJOIN in Google Sheets?

To use the TEXTJOIN function in Google Sheets, you must specify the delimiter first between quotation marks. Second, choose whether to ignore empty cells. Third, select the cells or the arrays of cells containing your text items.

Below, you have step-by-step instructions on how to use the TEXTJOIN function in different situations. First, you will use the function to join first and last names using a space as the delimiter. Second, you will learn how to do the same when you also have middle names, but only for some people. Third, you will learn how to join the different parts of an address using the line break as a delimiter.

Example 1. How to Join Text with Spaces?

Follow the steps below to join text items using a space as the delimiter.

  1. 1. Open Google Sheets to the spreadsheet with your data.
TEXTJOIN in Google Sheets Easy Guide Open Google Sheets
TEXTJOIN in Google Sheets (Easy Guide) - Open Google Sheets
  1. 2. In the column where you want the full names, type the TEXTJOIN function.
TEXTJOIN in Google Sheets Easy Guide TEXTJOIN Function
TEXTJOIN in Google Sheets (Easy Guide) - TEXTJOIN Function
  1. 3. For the first parameter, type a quotation mark, then add a space before the second quotation mark. The second parameter can be FALSE since there are no empty cells.
TEXTJOIN in Google Sheets Easy Guide Add Delimiter
TEXTJOIN in Google Sheets (Easy Guide) - Add Delimiter
  1. 4. Select the first row of cells with text items and close the parenthesis.
TEXTJOIN in Google Sheets Easy Guide Add Text Values
TEXTJOIN in Google Sheets (Easy Guide) - Add Text Values
  1. 5. Press ‘Enter’ to see the results. Accept the suggested autofill or grab the fill handle and drag it down to the last row.
TEXTJOIN in Google Sheets Easy Guide Text Joined by Spaces
TEXTJOIN in Google Sheets (Easy Guide) - Text Joined by Spaces
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Example 2. How to Ignore Empty Cells?

In this example, I have first and last names for every person on my list, but I also have middle names for a few of them. Follow the steps below to join them using spaces while ignoring empty cells.

  1. 1. In the cell where you want the full names, type the TEXTJOIN function and use a space between quotation marks as the delimiter.
TEXTJOIN in Google Sheets Easy Guide TEXTJOIN Delimiter
TEXTJOIN in Google Sheets (Easy Guide) - TEXTJOIN & Delimiter
  1. 2. For the second parameter, type TRUE to ignore empty cells.
TEXTJOIN in Google Sheets Easy Guide Ignore Empty Cells
TEXTJOIN in Google Sheets (Easy Guide) - Ignore Empty Cells
  1. 3. Select the first row of your data, including all three columns.
TEXTJOIN in Google Sheets Easy Guide Select Text Items
TEXTJOIN in Google Sheets (Easy Guide) - Select Text Items
  1. 4. Close the parenthesis and press ‘Enter’ to see the results. Accept the suggested autofill or grab the fill handle to copy the formula down to the last row.
TEXTJOIN in Google Sheets Easy Guide Text Joined
TEXTJOIN in Google Sheets (Easy Guide) - Text Joined

Example 3. How to Join Text with Line Breaks?

Imagine you have a table with customer addresses split over four cells: street address, city, state, and country. However, instead of joining them with spaces, I want to use line breaks so that each item has a new line in the cell.

  1. 1. In the cell where you want the full address, type the TEXTJOIN function.
TEXTJOIN in Google Sheets Easy Guide TEXTJOIN Function 2
TEXTJOIN in Google Sheets (Easy Guide) - TEXTJOIN Function
  1. 2. Type CHAR(10) as the first parameter to add a new line for every text item.
TEXTJOIN in Google Sheets Easy Guide CHAR10 for Line Break
TEXTJOIN in Google Sheets (Easy Guide) - CHAR(10) for Line Break
  1. 3. You can use TRUE for the second parameter in case there are any empty cells in your data.
TEXTJOIN in Google Sheets Easy Guide Ignore Empty Cells 2
TEXTJOIN in Google Sheets (Easy Guide) - Ignore Empty Cells
  1. 4. Select the first row of text items in all four columns.
TEXTJOIN in Google Sheets Easy Guide Select Text Items 2
TEXTJOIN in Google Sheets (Easy Guide) - Select Text Items
  1. 5. Close the parenthesis and press ‘Enter’ to see the results. Use the fill handle to copy the formula down to the last row.
TEXTJOIN in Google Sheets Easy Guide Text Items Joined by Line Break
TEXTJOIN in Google Sheets (Easy Guide) - Text Items Joined by Line Break
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

What Can I Use Instead of TEXTJOIN?

When you need to join texts in Google Sheets, TEXTJOIN is not your only option. First, you can use the ampersand symbol (&) within your formulas to append items. Second, you can use the CONCATENATE function to do the same. Third, you can use JOIN to also specify a delimiter.

Let’s see how to concatenate text items using all three methods to get the same result.

To join strings using the ampersand (&), type the equal sign followed by the first item, then the ampersand, then the delimiter between quotation marks, followed by another ampersand, then the second item, and so on. In other words, this method involves a fair amount of typing.

TEXTJOIN in Google Sheets Easy Guide Join Text with
TEXTJOIN in Google Sheets (Easy Guide) - Join Text with &
TEXTJOIN in Google Sheets Easy Guide Join Text with CONCATENATE
TEXTJOIN in Google Sheets (Easy Guide) - Join Text with CONCATENATE
TEXTJOIN in Google Sheets Easy Guide Join Text with JOIN
TEXTJOIN in Google Sheets (Easy Guide) - Join Text with JOIN

What is the Difference Between the CONCATENATE Function and the TEXTJOIN Function?

The CONCATENATE function has no built-in parameter to specify the delimiter. If you want to use one, you have to add it manually between each item you want to concatenate. TEXTJOIN, however, allows you to specify the delimiter as the first parameter. Additionally, you can choose whether to ignore empty cells.

To learn more about CONCATENATE, check out our guide on How to Use CONCATENATE in Google Sheets (Complete Guide + Examples).

Conclusion

TEXTJOIN is a very useful function to know. The function allows you to join multiple text items or arrays of items, specify the delimiter you want to use, and decide whether to ignore empty cells. While there are other options when it comes to appending strings in Google Sheets, TEXTJOIN provides more flexibility and control over the process.

You now know about the TEXTJOIN function and its syntax in Google Sheets. You have step-by-step instructions on how to use the function to achieve different results: join text by using spaces as delimiters, ignoring empty cells, and using line breaks as delimiters. You also know how to use alternatives to TEXTJOIN, like the ampersand symbol (&), CONCATENATE, and JOIN. Finally, you know the main differences between CONCATENATE and TEXTJOIN.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 19 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets