TEXTJOIN function

TEXTJOIN(Delimiter, IgnoreBlank, FirstTextParameter, OtherTextParameters...) TEXTJOIN(Delimiter; IgnoreBlank; FirstTextParameter; OtherTextParameters...)

Delimiter

Number, Logical, Text or Color

The text string that appears between the values that are joined together.

IgnoreBlank

Logical

Whether blank values are ignored.

FirstTextParameter

Number, Logical, Text, Color, { Number }, { Logical }, { Text } or { Color }

The first parameter denoting a single value or an array of values.

OtherTextParameters

Number, Logical, Text, Color, { Number }, { Logical }, { Text } or { Color } (accepts many)

Additional parameters denoting single values or arrays of values.

Returns

Text

A text string incorporating all the given values, joined together with the given delimiter.

Joins a number of values together, separating them with a delimiter, and returns the resulting text string. TEXTJOIN(", ", FALSE, "a", "b", "c")TEXTJOIN(", "; FALSE; "a"; "b"; "c") returns "a, b, c".

The second parameter determines if blank values should be part of the returned text string. The remaining parameters are the values that should be joined together. Use any combination of regular values and arrays of values.

Converting numbers to text strings

If a number is given as a parameter, it is converted to a text string:

TEXTJOIN(" — ", FALSE, NumberField1, NumberField2)TEXTJOIN(" — "; FALSE; NumberField1; NumberField2)

This formula returns a value such as "1234.56 — 9876.54", using the decimal separator of the language the app has been configured to use. If that language is German, for instance, "1234,56 — 9876,54" is returned instead. Thousands separators are not used.

Determining how numbers are converted to text

To get a formatted value, respecting a field's formatting options like the use of thousands separators, use the FormattedValue property instead:

TEXTJOIN(" — ", FALSE, NumberField1.FormattedValue, NumberField2.FormattedValue)TEXTJOIN(" — "; FALSE; NumberField1,FormattedValue; NumberField2,FormattedValue)

This formula produces a text string like "1,234.56 — 9,876.54" for an app set to US English, and one like "1 234,56 — 9 876,54" for an app in German (depending on the formatting options of NumberField1).

Use any of these functions to specify formatting preferences through the formula itself:

Colors, logical values and blank values

Colors are converted to CSS color values, such as "#00ff00ff" for green with no transparency. The logical value FALSE is converted to "FALSE" and TRUE is converted to "TRUE". Blank values (including those produced by the BLANK function) are converted to "".

Examples

TEXTJOIN(", ", FALSE, "a", "b", "c")TEXTJOIN(", "; FALSE; "a"; "b"; "c")

Returns "a, b, c".

TEXTJOIN(", ", FALSE, { "a", "b", "c" })TEXTJOIN(", "; FALSE; { "a"; "b"; "c" })

Returns "a, b, c".

TEXTJOIN(", ", FALSE, BLANK(), "a", "b", BLANK(), "c")TEXTJOIN(", "; FALSE; BLANK(); "a"; "b"; BLANK(); "c")

Returns ", a, b, , c". The second parameter, FALSE, instructs TEXTJOIN not to ignore blank text strings.

TEXTJOIN(", ", TRUE, BLANK(), "a", "b", BLANK(), "c")TEXTJOIN(", "; TRUE; BLANK(); "a"; "b"; BLANK(); "c")

Returns "a, b, c". The second parameter, TRUE, instructs TEXTJOIN to ignore blank text strings.

TEXTJOIN(", ", TRUE, TextField1:TextField10)TEXTJOIN(", "; TRUE; TextField1:TextField10)

Returns the values of the TextField1:TextField10TextField1:TextField10 range, joined together with ", ". The second parameter, TRUE, instructs TEXTJOIN to ignore blank text field values.

TEXTJOIN(", ", TRUE, NumberField1:NumberField10)TEXTJOIN(", "; TRUE; NumberField1:NumberField10)

Returns the values of the NumberField1:NumberField10NumberField1:NumberField10 range, joined together with ", ". The second parameter, TRUE, instructs TEXTJOIN to ignore blank text field values. Numbers are converted to strings as though TOTEXT was used.

TEXTJOIN(", ", TRUE, (NumberField1:NumberField10).FormattedValue)TEXTJOIN(", "; TRUE; (NumberField1:NumberField10),FormattedValue)

Returns the formatted values of the NumberField1:NumberField10NumberField1:NumberField10 range, joined together with ", ". Formatted values take the formatting preferences of the owning field into account. The second parameter, TRUE, instructs TEXTJOIN to ignore blank values.

TEXTJOIN(", ", TRUE, FILTER(TextField1:TextField10, STARTSWITH(Element, "A")))TEXTJOIN(", "; TRUE; FILTER(TextField1:TextField10; STARTSWITH(Element; "A")))

Returns the values of the text strings of the TextField1:TextField10TextField1:TextField10 range which start with "A", joined together with ", ". The second parameter, FALSE, instructs TEXTJOIN to not ignore blank values. FILTER returns a filtered version of the TextField1:TextField10TextField1:TextField10 range, where only values which start with "A" are included. As such, there are no blank array elements passed to TEXTJOIN.

TEXTJOIN(", ", FALSE, FILTER(Field1:Field5, Element > 3).Label)TEXTJOIN(", "; FALSE; FILTER(Field1:Field5; Element > 3),Label)

Returns a text string containing a comma-separated list of the labels of fields whose values are greater than 3. Field1:Field5Field1:Field5 is short-hand for an array which includes Field1, Field5 and all fields which appear between them. TEXTJOIN takes the array returned from FILTER and joins the text strings together, separating them with a comma and a space. The second parameter, FALSE, ensures that blank values are not included.

TEXTJOIN("", FALSE, { "a", "b", "c" })TEXTJOIN(""; FALSE; { "a"; "b"; "c" })

Returns "abc". The first parameter, "", specifies that no delimiter between the array elements should be used when joining them together.

TEXTJOIN("", FALSE, UNICHAR(SEQUENCE(26, 65)))TEXTJOIN(""; FALSE; UNICHAR(SEQUENCE(26; 65)))

Returns a text string containing all the upper-case letters of the English alphabet, "ABC...". SEQUENCE returns an array of size 26, starting at 65 ({ 65, 66, 67, ... }{ 65; 66; 67; ... }). When UNICHAR is given an array, it is invoked once for every array element, and the results are collected together in an array. TEXTJOIN then joins these array elements together in a single text string (where the first parameter, "", specifies that no delimiter between the array elements should be used).

TEXTJOIN(Delimiter, IgnoreBlank, Array) TEXTJOIN(Delimiter; IgnoreBlank; Array)

Delimiter

Number, Logical, Text, Color, { Number }, { Logical }, { Text } or { Color }

The text string that appears between the values that are joined together.

IgnoreBlank

Logical or { Logical }

Whether blank values are ignored.

Array

{ Number }, { Logical }, { Text }, { Color }, {{ Number }}, {{ Logical }}, {{ Text }} or {{ Color }}

An array of values to join together.

Returns

Text or { Text }

A text string incorporating all the given values, joined together with the given delimiter.

Joins an array of values together, separating them with a delimiter, and returns the resulting text string. TEXTJOIN(", ", FALSE, { "a", "b", "c" })TEXTJOIN(", "; FALSE; { "a"; "b"; "c" }) returns "a, b, c".

The second parameter determines if blank values should be part of the returned text string. The third parameter represents the array of values to join together.

For information on how numbers, colors, logical values and blank values are converted to text strings, refer to the documentation for the first TEXTJOIN variant above.

The first TEXTJOIN variant can also process arrays of values. This variant differs in that the first two parameters can be arrays, in which case this function is invoked once per array element and the results are collected as an array. The third parameter, which is normally an array, can also be set to an array of arrays, in which case this function is also invoked once per array element, with the results collected as an array. (See the examples below.)

Examples

TEXTJOIN(", ", FALSE, { "a", "b", "c" })TEXTJOIN(", "; FALSE; { "a"; "b"; "c" })

Returns "a, b, c".

TEXTJOIN({ ", ", "; " }, FALSE, { "a", "b", "c" })TEXTJOIN({ ", "; "; " }; FALSE; { "a"; "b"; "c" })

Returns the array { "a, b, c", "a; b; c" }{ "a, b, c"; "a; b; c" }.

TEXTJOIN(", ", FALSE, {{ "a", "b" }, { "c", "d" }})TEXTJOIN(", "; FALSE; {{ "a"; "b" }; { "c"; "d" }})

Returns the array { "a, b", "c, d" }{ "a, b"; "c, d" }.

TEXTJOIN({ ", ", "; " }, FALSE, {{ "a", "b" }, { "c", "d" }})TEXTJOIN({ ", "; "; " }; FALSE; {{ "a"; "b" }; { "c"; "d" }})

Returns the array { "a, b", "c; d" }{ "a, b"; "c; d" }.