TEXTJOINformula concatenates (joins together) multiple text strings using a delimiter of your choice. This can be useful when combining lists, creating sentences, or formatting data for export. You can choose to ignore empty cells or include them as part of the output.
- How to use
- Examples of using
TEXTJOINformula not working?
- Similar formulas to
TEXTJOIN formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:
- delimiter (required):
The character or characters to insert between each text string. This can be a single letter, number, symbol, or a longer string of characters. Use quotes to enclose the delimiter.
- ignore_empty (required):
A boolean value that determines whether or not to include empty cells in the output. If set to TRUE, empty cells will be skipped. If set to FALSE, empty cells will be included with the delimiter between them.
- text1 (required):
The first text string to include in the output. This can be a cell reference, a quoted string, or a formula that returns a text string.
- text2 (optional):
Optional. Additional text strings to include in the output. You can include up to 30 text strings in a single formula.
ExamplesHere are a few example use cases that explain how to use the
TEXTJOINformula in Google Sheets.
Combine a list of values
If you have a list of values in separate cells and you want to combine them into a single cell with a delimiter between each value, you can use
TEXTJOIN. For example, to combine a list of names in cells A1:A5 with a comma between each name, you could use the formula:
=TEXTJOIN(", ", TRUE, A1:A5).
Create a sentence
You can use
TEXTJOIN to create a sentence from multiple text strings. For example, to create the sentence "The quick brown fox jumps over the lazy dog." from separate cells, you could use the formula:
=TEXTJOIN(" ", TRUE, "The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog.").
Format data for export
If you need to export data from Google Sheets into another program or file format, you may need to format the data in a specific way.
TEXTJOIN can be useful for creating custom output formats. For example, to create a comma-separated list of email addresses from a list of names and email addresses, you could use the formula:
=TEXTJOIN(", ", TRUE, B2:B5), assuming the email addresses are in column B.
TEXTJOINnot working? Here are some common mistakes people make when using the
TEXTJOINGoogle Sheets Formula:
One of the most common mistakes when using the
TEXTJOIN function is forgetting to include a delimiter. Make sure to include a delimiter as the first parameter.
Incorrect data type
Make sure that the values you are trying to join are text values. If you are trying to join numbers or dates, use the
TEXT function to convert them to text first.
Too many arguments
TEXTJOIN function can only accept up to 30 arguments for the text parameter. If you need to join more than 30 values, consider using a different formula or combining the values with a different method.
The following functions are similar to
TEXTJOIN or are often used with it in a formula:
The CONCAT formula in Google Sheets is used to combine two or more strings or cell values into a single string. It is most commonly used to join values from different cells or to create a custom message or label. The operator CONCAT can also be used in combination with other formulas to manipulate the joined values.
JOINformula concatenates the elements of one or more arrays using a specified delimiter string. It is commonly used to combine text strings or to create comma-separated lists of values.
LEFTformula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.
RIGHTformula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.
You can learn more about the
TEXTJOIN Google Sheets function on Google Support.