ID EN
…
#Dynamic array

ARRAYTOTEXT

Excel Functions

Converts array or range to a text string

Syntax

EXCEL 
=ARRAYTOTEXT(array, [format])

Arguments

Parameter Description
array The array or range to convert to text.
format [optional] Output format. 0 = concise (default), and 1 = strict.

Return Value

A text string showing values

Details

The ARRAYTOTEXT function converts an array or range into a text string in a specific format that contains all values. Values are separated by commas (,) or semicolons (;), depending on the format requested and the structure of the array. ARRAYTOTEXT takes two arguments: array and format. Array is the array or range to convert to text. Array can be provided as a range like A1:A3 or an array generated by another function. The optional format argument controls the structure of the output. There are two formats available, concise and strict. The concise format is a simple, human-readable format. For example, values separated by commas. The strict format outputs a machine-parseable structure that describes an array in Excel. All arrays in Excel are wrapped in curly brackets {}, and the delimite

Examples

Array syntax

All arrays in Excel are wrapped in curly brackets {}, and the delimiters between array elements indicate rows and/or columns. In the US version of Exc

EXCEL 
{1,2,3} // columns (horizontal)
{1;2;3} // rows (vertical)
Array syntax

The array below shows the numbers 1-6 in three rows and two columns:

EXCEL 
{1,2;3,4;5,6} // 3 rows x 2 columns
Concise format

When format is zero (0), ARRAYTOTEXT will return a concise format. Essentially, the concise format is a plain, human-readable text string separated by

EXCEL 
=ARRAYTOTEXT(A1:A3) // returns "Red, Blue, Green"
Concise format

If the values "Red", "Blue", and "Green" are in the range A1:C1 (i.e., columns instead of rows), the output is exactly the same:

EXCEL 
=ARRAYTOTEXT(A1:C1) // returns "Red, Blue, Green"
Strict format

When format is set to 1, ARRAYTOTEXT will return a text string that is a fully qualified array constant like {"red","blue","green"}. The output will a

EXCEL 
=A1:C3
Strict format

In fact, you can paste the strict output format directly into the formula bar, and Excel will return values to the worksheet in a range that follows t

EXCEL 
={"red",1;"blue",2;"green",3}

See Also

VALUETOTEXT TEXT