The TEXTJOIN function creates a concatenated string by specifying specific characters.
It is very effective when creating strings of comma-separated text (CSV), tab-separated text (TSV), and other formats.
How it works
=TEXTJOIN(delimiter,ignore_empty,text1 - 252)
| Name | Omission | Description |
|---|---|---|
| delimiter | Required argument. | Specify the character to be inserted between the text to be concatenated. More than two characters can be specified. |
| ignore_empty | Required argument. | TRUE: Empty cells have no delimiter. FALSE: Empty cells also have delimiters. |
| text1 - 252 | Only Text 1 is a required field. | Specify targets to be concatenated One by one, separated by commas, or a range of cells |
Execution example by argument
This is the most basic pattern.
Generates comma-separated text.
| Parameter | Set value |
|---|---|
| delimiter | , |
| ignore_empty | FALSE |
| text | Cell range |
delimiter
Two or more delimiter characters
Multiple characters are supported, so||(pipe delimiter) is also possible.
Enclose with double-cotation marks (")
It is also possible to use string concatenation in conjunction with string concatenation to enclose values in double-cotation marks (").
=""""&TEXTJOIN(""",""",FALSE,text)&""""
Create Tab Separated Text (TSV)
The CHAR function can also be used for TAB delimitation.
CHAR(9) refers to the TAB character.
You can see the TAB by pasting it into a text editor, although it is not visible in Excel.
Specify multiple characters
If a range of cells is specified as delimiters, the application is repeated from left to right.
In this example, we specify two delimiters: "," and "||" repeat the delimiters.
ignore_empty
FALSE (do not ignore)
FALSE inserts a delimiter even if there are empty cells.
TRUE (ignore)
TRUE does not insert delimiters in empty cells.
Empty strings are ignored.
Text (multiple designations)
Up to 252 texts can be specified.
If multiple texts are specified, they will be displayed in that order.
This designation may be mixed with a range or single designation.
If there are duplicate ranges, they will be displayed as duplicate results.
Spill
Because "text" is an argument that assumes a cell range, it cannot be Spill as is.
To Spill, use BYROW or BYCOL functions.
=BYROW(A1:C3,LAMBDA(r,TEXTJOIN(",",TRUE,r)))
=BYCOL(A1:C3,LAMBDA(c,TEXTJOIN(",",TRUE,c)))
---