How to Use the TEXTJOIN Function in Excel
The TEXTJOIN function in Excel and Google Sheets enables you to concatenate multiple text strings into one. This is particularly useful for aggregating text values from various cells or a range of cells into a single cell.
Basic Syntax
The syntax for the TEXTJOIN function is consistent across both Excel and Google Sheets:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)- delimiter: The character or string used as a separator between text items.
- ignore_empty: A boolean value (TRUE or FALSE) that determines whether to exclude empty cells from the concatenation.
- text1, text2, …: The text items or cell ranges to be concatenated.
Examples
Example 1: Joining Text Strings
Consider the scenario where text values reside in cells A1, A2, and A3 in Excel:
| A | B |
|---|---|
| Apple | =TEXTJOIN(“, “, TRUE, A1, A2, A3) |
| Orange | |
| Banana |
The formula =TEXTJOIN(", ", TRUE, A1, A2, A3) concatenates the values from cells A1, A2, and A3 using a comma and a space as the delimiter. This will output “Apple, Orange, Banana” into cell B1.
Example 2: Joining Text Strings with Ignore Empty Cells
If you prefer to exclude empty cells while concatenating text, set the ignore_empty argument to TRUE. Consider this example:
| A | B |
|---|---|
| Apple | =TEXTJOIN(“, “, TRUE, A1, “”, A3) |
| Banana |
In this scenario, the formula =TEXTJOIN(", ", TRUE, A1, "", A3) considers only the non-empty cells (A1 and A3), resulting in “Apple, Banana” displayed in cell B1.
Example 3: Joining Text Strings from a Range
The TEXTJOIN function can also concatenate text values across a cell range. For example, to join text values in cells A1 to A5, separated by a line break, you can use:
| A | B |
|---|---|
| Apple | =TEXTJOIN(CHAR(10), TRUE, A1:A5) |
| Orange | |
| Banana | |
| Peach |
The formula =TEXTJOIN(CHAR(10), TRUE, A1:A5) concatenates the values from A1 to A5, using a line break as the delimiter, resulting in the respective values appearing in cell B1 separated by line breaks.
These examples showcase the flexibility and utility of the TEXTJOIN function in Excel and Google Sheets for merging text strings in a variety of contexts based on specific needs.
More information: https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c