Excel Function: ARRAYTOTEXT

Purpose

The ARRAYTOTEXT function returns a text value that encapsulates a set of values present in either an array constant or a cell range. It has two modes of operation: in default mode it generates a comma-delimited list of values, and in strict mode an array constant. Strict mode is useful for deriving an array constant from a cell range. Values in array constants and cell ranges are read from left to right and top to bottom, and placed into ARRAYTOTEXT’s output in the order they’re encountered.

Invocations of the ARRAYTOTEXT function take the form:

ARRAYTOTEXT(array,[format])

Example

Consider 2 cells containing the following formulae:

=ARRAYTOTEXT({"a",1;"b",2},0)
=ARRAYTOTEXT({"a",1;"b",2},1)

Both formulae operate on the same array constant which represents a 2×2 grid of values. In the first formula ARRAYTOTEXT operates in default mode and in the second, strict mode. The ARRAYTOTEXT function reads the values in the order a → 1 → b → 2 and returns “a, 1, b, 2” in default mode and “{“a”,1;”b”,2}” in strict mode. In default mode, the output neither reflects the fact that values were split across two rows nor does it place the text values (a & b) between quotes. In strict mode, the array constant generated by ARRAYTOTEXT was identical to the one it operated on. The diagram below illustrates the differences between both modes of operation.

The ARRAYTOTEXT function operates on a 2x2 grid of values. Row 1 contains the values "a" and "1", row 2 contains "b" and "2". The 2x2 grid can be represented in Excel by the array constant {"a",1;"b",2}. Having ARRAYTOTEXT operate on this array constant in default mode yields the result "a, 1, b, 2", and strict mode yields the array constant {"a",1;"b",2}.

Arguments

Argument

Argument type

Description

array

Mandatory

Either an array constant or a cell range containing values to be placed into a list. “array” can contain values of any type, including currency, geography & stock.

format

Optional. Defaults to 0 if omitted.

Used to set ARRAYTOTEXT’s mode of operation. It can assume one of two values:

Value	Description
0	Default mode. An array constant or cell range passed to the array argument is transformed into a comma-delimited list of values. Each element in that list is formatted according to the rules of Excel’s general format.
1	Strict mode. An array constant or cell range passed to the array argument is transformed into an array constant which is formatted such that, without modification, it can be included in a formula.

Related Functions

Function	Example	Purpose
VALUETOTEXT	`VALUETOTEXT(value, [format])`	The VALUETOTEXT function converts a value of any type to text. It has two modes of operation: default and strict. The treatments of values given by VALUETOTEXT & ARRAYTOTEXT when operating in default & strict modes are the same.

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	The value supplied to the format argument was neither 0 nor 1.

Examples

The examples are based on the dataset below:

The dateset contains the following:
Cell A1: 1 (number)
Cell B2: a (text)
Cell C1: blank
Cell D1: blank
Cell A2: TRUE (boolean)
Cell B2: 8 September 2023 (date)
Cell C2: blank
Cell D2: blank
Cell A3: USD/GBP (currency)
Cell B3: London (Geography)
Cell C3: blank
Cell D3: blank

Example	Formula	Result	Description
1	`=ARRAYTOTEXT(A1:B1,0)`	`1, a`	Apply ARRAYTOTEXT to the cell range A1:B1 in default mode. A1:B1 includes cells A1 and B1. The values in these cells are placed into a comma-delimited list.
2	`=ARRAYTOTEXT(A1:D1,0)`	`1, a, ,`	Apply ARRAYTOTEXT to the cell range A1:D1 in default mode. A1:D1 includes cells A1, B1, C1 and D1. The values in these cells are placed into a comma-delimited list.
3	`=ARRAYTOTEXT(A1:B3,0)`	`1, a, TRUE, 45177, USD/GBP, London`	Apply ARRAYTOTEXT to the cell range A1:B3 in default mode. A1:B3 includes cells A1, B1, A2, B2, A3 and B3. The values in these cells are placed into a comma-delimited list. A2 contains the boolean value “TRUE”. B2 contains the date 8 September 2023. Excel represents it with the number 45177. 45177 is added to the output of ARRAYTOTEXT. A3 contains the currency value USD/GBP. It represents a currency exchange rate between USD & GBP. B3 contains the geography value “London“. It represents London, UK.
4	`=ARRAYTOTEXT(A1:B1,1)`	`{1,"a"}`	Apply ARRAYTOTEXT to the cell range A1:B1 in strict mode. A1:B1 includes cells A1 and B1. The values in these cells are placed into an array constant.
5	`=ARRAYTOTEXT(A1:D1,1)`	`{1,"a",,}`	Apply ARRAYTOTEXT to the cell range A1:D1 in strict mode. A1:D1 includes cells A1, B1, C1 and D1. The values in these cells are placed into an array constant.
6	`=ARRAYTOTEXT(A1:B3,1)`	`{1,"a";TRUE,45177;"USD/GBP","London"}`	Apply ARRAYTOTEXT to the cell range A1:B3 in strict mode. A1:B3 includes cells A1, B1, A2, B2, A3 and B3. The values in these cells are placed into an array constant. A2 contains the boolean value “TRUE”. B2 contains the date 8 September 2023. Excel represents it with the number 45177. 45177 is added to the output of ARRAYTOTEXT. A3 contains the currency value USD/GBP. It represents a currency exchange rate between USD & GBP. B3 contains the geography value “London“. It represents London, UK.