Documentation/Calc Functions/TEXTJOIN
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
TEXTJOIN
Category:
Text
Summary:
Concatenates multiple text strings into a single string, inserting delimiters between each sub-string.
The TEXTJOIN function is related to the CONCAT and CONCATENATE functions, but provides the extra facility of including delimiters in the concatenated string.
Syntax:
TEXTJOIN(Delimiter, Skip Empty, String 1 [; String 2 [; … ;[ String 253]]])
Returns:
Returns a text string formed by concatenating the string arguments end-to-end, with delimiters between each sub-string.
Arguments:
Delimiter is a text string (in quotation marks), a number, a reference to a cell, a reference to a cell range, or an inline array of strings (within curly braces '{' and '}'). Delimiter specifies the text to be inserted between each pair of concatenated sub-strings.
Skip Empty specifies whether empty cells / strings are included (0 or FALSE) or ignored (any other value). If set to 0 or FALSE to include empty cells / strings, then the returned string may include adjacent delimiters.
String 1 is a text string (in quotation marks), a number, a reference to a cell, a reference to a cell range, or an inline array of strings (within curly braces '{' and '}'). String 1 specifies the first string or strings that are to be concatenated.
String 2 through to String 253 are similar to String 1 but specify the subsequent strings that are to be concatenated.
- If Delimiter specifies multiple strings (e.g., a cell range or an inline array), the number of delimiters need not be of the same size as the number of strings to be concatenated. If there are more delimiters than strings to be concatenated, not all delimiters will be used. If there are less delimiters than strings to be concatenated, the delimiters will be re-used in a cyclic manner.
- If Skip Empty is non-numeric, then TEXTJOIN reports a #VALUE! error.
- When a cell range is specified, the cells are traversed row by row (from top to bottom). This applies both for the Delimiter argument and the String 1 through to String 253 arguments.
- The length of the string to be returned is limited to be less than 256 * (1024)2 characters. If the arguments passed would cause this limit to be exceeded, TEXTJOIN reports a string overflow error (Err:513).
Additional details:
This function is not a requirement of ODF 1.2 but is compatible with the Microsoft Excel TEXTJOIN function.
Examples:
| Formula | Description | Returns |
|---|---|---|
| =TEXTJOIN(" "; 1; "Here"; "comes"; "the"; "sun") | Here the function concatenates the four string arguments, separating each pair with a space delimiter. In this example, the exact value of the Skip Empty argument is not significant because there are no empty strings to be concatenated. | Here comes the sun |
| =TEXTJOIN(B1; C1; D1; D2; D3; D4) where cell B1 contains the string "-", cell C1 contains the value 1, and cells D1 to D4 contain the strings "Here", "comes", "the", and "sun" respectively. | Here the function concatenates the four string arguments (passed by cell reference), separating each pair with a dash delimiter (also passed by cell reference). In this example, the exact value of the Skip Empty argument (again passed by cell reference) is not significant because there are no empty strings to be concatenated. | Here-comes-the-sun |
| =TEXTJOIN(", "; 0; "John Doe"; ""; "23 High Street"; "Some Town"; "Some City") | Here the function concatenates the string arguments, separating each pair with a two-character delimiter (comma followed by space). With the Skip Empty argument set to 0, TEXTJOIN does not ignore empty strings. Hence two adjacent delimiters appear in the returned string, between "John Doe" and "23 High Street". | John Doe, , 23 High Street, Some Town, Some City |
| =TEXTJOIN(", "; 1; "John Doe"; ""; "23 High Street"; "Some Town"; "Some City") | This example is similar to the previous example, except that the Skip Empty argument is no longer set to 0 and so the extra delimiter does not appear between "John Doe" and "23 High Street". | John Doe, 23 High Street, Some Town, Some City |
| =TEXTJOIN({"+","-","="}; 0; {9,7,5,11}) | Here the delimiters are defined in an inline array of strings, while the strings to be concatenated are defined in an inline array of numbers. The delimiters are used in sequence and the exact value of the Skip Empty argument is not significant because there are no empty strings. | 9+7-5=11 |
| =TEXTJOIN(E1:F2, 0, A1:C3) where cells E1, F1, E2, and F2 contain the delimiter strings " + ", " - ", " * ", and " / " respectively. Cells A1 to C3 contain a string representing the cell’s address, so cell A1 contains "A1", cell B2 contains "B2", etc. | This example is helpful to demonstrate how TEXTJOIN behaves when the delimiters and strings to be concatenated are specified as cell ranges. The cells containing delimiter strings are traversed row by row, from top to bottom, and this cycle is repeated as often as needed. The strings to be concatenated are also traversed row by row, from top to bottom. In this example, the exact value of the Skip Empty argument is not significant because there are no empty cells in the range A1:C3. | A1 + B1 - C1 * A2 / B2 + C2 - A3 * B3 / C3 |
Related LibreOffice functions:
ODF standard:
None.
Related (or similar) Excel functions:
TEXTJOIN