Description
Use this function to combine text from multiple cells, including the option to specify a delimiter to be inserted between each value. Can be used with CHILDREFS.
TEXTJOIN is useful for combining text from multiple cells into a single cell, with the option to specify a delimiter and ignore empty cells. This function provides more flexibility than CONCATENATE or the & operator.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, […, text_251])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
delimiter |
Yes | The text to insert between each text value. | A text string, a reference to a cell containing text, or a formula which results in either of these. If it is empty ("") no delimiter is inserted. |
ignore_empty |
Yes | Specifies whether to ignore empty cells. | TRUE or FALSE |
text1 |
Yes | The first text item to be joined. | A number, a text string, a cell reference, or a formula which results in any of these. |
…, text_n |
No | Additional text item(s) to be joined. | A text string, a number, a cell reference, or a formula which results in any of these. Up to 252 additional values can be provided. |
Example
Sample data
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Apple | Banana | Cherry | ||
| 2 | Red | Blue | Green | ||
| 3 | 1 | 2 | 3 | 4 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Join a row of cells with a delimiter, keeping a placeholder for any empty cells. | =TEXTJOIN(", ", FALSE, A1:D1) |
Joins Apple, Banana, Cherry, and the blank D1 with ", " between each, including a trailing delimiter for the empty cell. Result: Apple, Banana, Cherry, |
| Join a row of cells with a delimiter, skipping any empty cells entirely. | =TEXTJOIN(", ", TRUE, A1:D1) |
Joins Apple, Banana, and Cherry with ", " between each, leaving out the blank D1 and its delimiter. Result: Apple, Banana, Cherry |
| Join a row of cells with a custom delimiter other than a comma. | =TEXTJOIN(" - ", TRUE, A2:D2) |
Joins Red, Blue, and Green with " - " between each, skipping the empty B2. Result: Red - Blue - Green |
| Join a row of cells directly together with no delimiter at all. | =TEXTJOIN("", FALSE, A3:D3) |
Joins 1, 2, 3, and 4 back-to-back with an empty string as the delimiter. Result: 1234 |
| Join cells from a vertical range onto separate lines within a single cell. | =TEXTJOIN(CHAR(10), TRUE, A1:A3) |
Uses CHAR(10) as the delimiter to insert a line break between Apple, Red, and 1. Result: Apple Red 1 |
Notes
- The delimiter can be any text string, including an empty string ("") or special characters. Use an empty string to indicate no delimiter.
- When
ignore_emptyis set to TRUE, empty cells are skipped and do not add to the delimiter count. - When
ignore_emptyis set to FALSE, empty cells are treated as empty strings. - TEXTJOIN can work with both horizontal and vertical ranges.
- Numbers are automatically converted to text when joined.
- TEXTJOIN can work with both individual cell references and ranges.
Tips
- You can combine TEXTJOIN with other functions such as IF for more complex text operations.
- When working with large datasets, consider using TEXTJOIN with array formulas for efficiency.
- Use an empty string ("") as the delimiter to concatenate text without any separators.
- TEXTJOIN can be useful for creating comma-separated lists or formatting data for export.
- Combine TEXTJOIN with other functions such as PROPER or TRIM for more advanced text manipulation.
- Use
CHAR(10)as the delimiter to create multi-line text within a single cell.