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 |
|---|---|---|---|
| Apple | Banana | Cherry | |
| Red | Blue | Green | |
| 1 | 2 | 3 | 4 |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=TEXTJOIN(", ", FALSE, A1:D1) |
Joins cells A1:D1 with comma and space, not ignoring empty cells | Apple, Banana, Cherry, |
=TEXTJOIN(", ", TRUE, A1:D1) |
Joins cells A1:D1 with comma and space, ignoring empty cells | Apple, Banana, Cherry |
=TEXTJOIN(" - ", TRUE, A2:D2) |
Joins cells A2:D2 with dash, ignoring empty cells | Red - Blue - Green |
=TEXTJOIN("", FALSE, A3:D3) |
Joins cells A3:D3 with no delimiter | 1234 |
=TEXTJOIN(CHAR(10), TRUE, A1:A3) |
Joins cells A1:A3 with line breaks, ignoring empty cells | 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.
Related functions
CHAR
CODE
CONCATENATE
CONCATENATEIF
FIND
LEFT
LEN
LOWER
MID
PROPER
REPT
RIGHT
SEARCH
SUBSTITUTE
TEXT
TEXTJOIN
TRIM
UNICHAR
UNICODE
UPPER