The TEXTJOIN function in Google Sheets is a powerful tool for combining text from multiple sources into a single cell, with a customizable delimiter. It simplifies the process of concatenating strings without the need for repetitive formulas, making data management more efficient and organized.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
- delimiter: The character or characters that separate each text string.
- ignore_empty: A boolean value (TRUE or FALSE) that determines whether to ignore empty cells.
- text1: The first text string or range to combine.
- text2: Optional. Additional text strings or ranges to combine. Can include multiple values.
Example #1
TEXTJOIN(", ", TRUE, A1:A3)
This example combines all text from cells A1 to A3, separating them with a comma and a space, while ignoring any empty cells. For instance, if A1 contains “Apple”, A2 contains an empty string, and A3 contains “Banana”, the result would be:
Apple, Banana.
Example #2
TEXTJOIN(" - ", FALSE, B1:B5)
In this case, the function concatenates all values in cells B1 to B5, using ” – ” as the separator, including any empty cells. If B1 contains “2023”, B2 contains “Q1”, and B3 is empty, the result will be:
2023 – Q1 – .
Example #3
TEXTJOIN("; ", TRUE, C1, C2, C3)
Here, the function combines the text in cells C1, C2, and C3 with a semicolon and space as the delimiter, ignoring empty cells. So if C1 is “John”, C2 is “Doe”, and C3 is empty, the outcome would be:
John; Doe.
Error handling
- VALUE!: This error occurs if the delimiter is not a valid string or if any of the provided text arguments are invalid.
- N/A: This indicates that one or more ranges referenced in the function do not return a value.
- REF!: This error indicates that one of the ranges specified in the function does not exist or has been deleted.