TEXTJOIN function. Concatenates strings by specifying a delimiter.(Microsoft Excel)
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 |
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-268.png)
delimiter
Two or more delimiter characters
Multiple characters are supported, so||(pipe delimiter) is also possible.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-269.png)
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)&""""
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-270.png)
Create Tab Separated Text (TSV)
The CHAR function can also be used for TAB delimitation.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-271.png)
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.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-272.png)
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.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-273.png)
ignore_empty
FALSE (do not ignore)
FALSE inserts a delimiter even if there are empty cells.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-274.png)
TRUE (ignore)
TRUE does not insert delimiters in empty cells.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-275.png)
Empty strings are ignored.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-276.png)
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.
![](https://www.samurai-emblem.com/wp-content/uploads/2023/05/image-277.png)
Spill
Because "text" is an argument that assumes a cell range, it cannot be Spill as is.
![](https://www.samurai-emblem.com/wp-content/uploads/2024/03/image-249.png)
To Spill, use BYROW or BYCOL functions.
=BYROW(A1:C3,LAMBDA(r,TEXTJOIN(",",TRUE,r)))
![](https://blog-tips.sekenkodqx.jp/wp-content/uploads/2024/03/image-31.png)
=BYCOL(A1:C3,LAMBDA(c,TEXTJOIN(",",TRUE,c)))
![](https://blog-tips.sekenkodqx.jp/wp-content/uploads/2024/03/image-32.png)
---
Discussion
New Comments
No comments yet. Be the first one!