This guide will show you how to combine text strings by using the TEXTJOIN Google Sheets function. We’ll cover the syntax and show you a couple of step by step examples.
What Is the TEXTJOIN Google Sheets Function
The TEXTJOIN Google Sheets function combines text in multiple strings into a single cell. The user can specify the choice of a delimiter. Delimiters can be commas, semi-colons, spaces, and line breaks. TEXTJOIN is useful when consolidating data from multiple cells into one.
One way to use TEXTJOIN is when you have columns containing the first and last names of people and wish to have the full name in the cells.
Google Sheets TEXTJOIN Syntax
Before we take a look at how to use TEXTJOIN in Google Sheets, let’s look at how it works. Here is the syntax for the TEXTJOIN Formula in Google Sheets:
=TEXTJOIN(delimiter, empty, txt1, txtn, … )
The formula needs at least three parameters to work correctly. These are:
- delimiter: this parameter defines the string or a cell reference to a string on which you wish to use the TEXTJOIN Google Sheets function. If the string is empty, then the text will simply be concatenated.
- empty: this parameter defines whether the formula should include the empty cells in the result. This value can either be true or false. If true, the empty cells won’t be included.
- txt1: this parameter defines a text. It can be a string or a cell reference to one or multiple strings in an array.
- txtn: this is an optional parameter defining additional txt parameters.
How to Use TEXTJOIN in Google Sheets
Now that we know how the TEXTJOIN function works in Google Sheets, let’s look at a few examples of the function in action.
Writing Full Names Using TEXTJOIN
This is one of the simplest ways to use the TEXTJOIN formula. In this TEXTJOIN example Google Sheets, we have two columns containing some people’s first and last names. We wish to write their full name in another column.
Here is how you can do this:
- Click on the cell where you wish to execute the formula and type in the initial starting part of the formula, which is =TEXTJOIN(.
- Let’s add a delimiter as the first parameter. In this case, we write it as “ ”. These are quotation marks with a space in between used to add a space between first and last names.
- Add a comma and add the empty parameter, which is “TRUE” in this case.
- Add another comma and the txt parameters to define the cell ranges containing the first and last names. We are going to add these as A2, B2.
- Finally, add a closing bracket and press Enter to execute the formula.
The formula will look like this:
=TEXTJOIN(” “, “TRUE”, A2, B2)
Another commonly used way to write names is to write the Last name first and then the First name with a comma separating them.
To write names using this format, follow the same steps discussed above. Write the delimiter parameter as “, ”. When entering the cell ranges for the first and last names, simply switch their locations with each other so that the last name will be shown first. The entire formula will look something like this:
=TEXTJOIN(“, “, “TRUE”, B2, A2)
Joining Data From Multiple Cells Using TEXTJOIN
In this example, we have addresses, but they aren’t correctly formatted. We wish to display the addresses from all the cells in one cell. However, some cells are empty. This example highlights one of the most useful features of the TEXTJOIN function, which allows it to skip any empty cells.
Let’s look at how you can properly display the addresses in these cells:
- Click on the cell and type in the initial starting part of the formula, which is =TEXTJOIN(.
- Let’s add a delimiter as the first parameter. In this case, we write it as “ ”. Add a comma after this.
- Add the empty parameter, which is “TRUE” in this case. Having it set as TRUE means that the formula will ignore the empty cells. Add a comma afterward.
- Now, add the txt parameters to define the cell ranges containing the addresses. These will look like A2, B2, C2 in this case.
- Add a closing bracket and press Enter to finalize the formula.
The entire formula will look something like this:
=TEXTJOIN(” “, “TRUE”, A2, B2, C2)
Related Reading: How to Merge Cells in Google Sheets
When Not to Use TEXTJOIN Google Sheets
Generally, you shouldn’t use the TEXTJOIN formula when joining non-text values. As an example, assume you are using this formula in your spreadsheet:
=TEXTJOIN(” “, A1:A4)
This formula will return an error, and this is because the A1:A4 parameter is interpreted as a text in Google Sheets. If you’re looking to join numbers in your spreadsheet, you can use CONCATENATE instead of TEXTJOIN.
Frequently Asked Questions
What Are Some Similar Formulae to TEXTJOIN in Google Sheets?
The TEXTJOIN Google Sheets function combines text in multiple strings into a single cell. Google Sheets has multiple functions that allow you to do this. You can use the CONCATENATE function in your spreadsheet to join the text from different cells into a single cell. You can also use the “&” operator to do the same. The VLOOKUP and LOOKUP function in Sheets allow you to join text from various cells into a single cell.
What Is the Difference Between CONCAT and TEXTJOIN?
Although both functions essentially serve the same main purpose, combining the text in multiple strings or cell ranges. However, the main difference between CONCAT and TEXTJOIN is that CONCAT doesn’t have a delimiter, nor does it have the option to ignore empty cells. The TEXTJOIN function allows you to combine the text in multiple cells and also allows you to specify a delimiter that you can specify between each text value to be combined.
Now you should have a thorough understanding of how to use the TEXTJOIN Google Sheets function. With this knowledge, you should be able to clean up your spreadsheets and save yourself hours of manually editing data. If you found this guide useful, check out our related content below to learn more.