Text concatenation operator

Value1 & Value2 Value1 & Value2

Value1

The first value.

Value2

The second value.

Returns

Text Text

The first value joined together with the second value.

Joins two values together as a single text string and returns it. "te" & "st""te" & "st" returns "test" and "The value is: " & TextValue"The value is: " & TextValue returns "The value is: ", followed by TextValue .

Converting numbers to text strings

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

"The value is: " & NumberValue

This formula returns a value such as "The value is: 1234.56", using the decimal separator of the configured locale. If that locale is German, for instance, "The value is: 1234,56" is returned instead. Thousands separators are not used.

To use a different locale, use the CONCAT and CONCATENATE functions instead.

Determining how numbers are converted to text

Use any of these functions to gain more control over the conversion:

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 "" .

The & operator and CONCATENATE versus CONCAT

The & operator is fully equivalent to CONCATENATE. In many ways, it is also similar to the CONCAT function. These formulas all return the same value:

"Value: " & NumberValue

CONCATENATE ("Value: ", NumberValue)

CONCATENATE ("Value: "; NumberValue)

CONCAT ("Value: ", NumberValue)

CONCAT ("Value: "; NumberValue)

CONCAT differs from this operator and CONCATENATE in that it always returns a single text string, even when invoked with arrays. These two formulas both return the array { "a1", "b1" }{ "a1"; "b1" }:

{ "a", "b" } & 1

{ "a"; "b" } & 1

CONCATENATE ({ "a", "b" }, 1)

CONCATENATE ({ "a"; "b" }; 1)

However, this CONCAT formula returns "ab1":

CONCAT ({ "a", "b" }, 1)

CONCAT ({ "a"; "b" }; 1)

These two formulas both return the array { "ac", "bd" }{ "ac"; "bd" }:

{ "a", "b" } & { "c", "d" }

{ "a"; "b" } & { "c"; "d" }

CONCATENATE ({ "a", "b" }, { "c", "d" })

CONCATENATE ({ "a"; "b" }; { "c"; "d" })

However, this CONCAT formula returns "abcd":

CONCAT ({ "a", "b" }, { "c", "d" })

CONCAT ({ "a"; "b" }; { "c"; "d" })

Examples

"te" & "st"

Returns "test".

"The value is: " & TextValue

Returns "The value is: ", followed by TextValue .

"The value is: " & NumberValue

Returns a text string like "The value is: 1234.56" if the configured locale is Australian English and a text string like "The value is: 1234,56" if the configured locale is German. Thousands separators are not used.

"The value is: " & FORMAT (NumberValue, 2, 2)

"The value is: " & FORMAT (NumberValue; 2; 2)

Returns a text string like "The value is: 1,234.56" if the configured locale is US English, and one like "The value is: 1.234,56" if the configured locale is German. The particulars of the formatted number are determined by the parameters given to the FORMAT function. Here, the second parameter 2 signifies that the number should be formatted with at least two decimal places. Refer to the documentation for FORMAT for more information.

{ "a", 3 } & "b"

{ "a"; 3 } & "b"

Returns the array { "ab", "3b" }{ "ab"; "3b" } (equivalent to { "a" & "b", 3 & "b" }{ "a" & "b"; 3 & "b" }.

{ "a", 3 } & { "b", TRUE }

{ "a"; 3 } & { "b"; TRUE }

Returns the array { "ab", "3TRUE" }{ "ab"; "3TRUE" } (equivalent to { "a" & "b", 3 & TRUE }{ "a" & "b"; 3 & TRUE }.

CONCATENATE ({ "a", 3 }, { "b", TRUE })

CONCATENATE ({ "a"; 3 }; { "b"; TRUE })

Returns the array { "ab", "3TRUE" }{ "ab"; "3TRUE" } (equivalent to { "a" & "b", 3 & TRUE }{ "a" & "b"; 3 & TRUE }. CONCATENATE is equivalent to &.

CONCAT ({ "a", 3 }, { "b", TRUE })

CONCAT ({ "a"; 3 }; { "b"; TRUE })

Returns the text string "a3bTRUE". CONCAT always returns a single text string, never an array.