ARRAYTOTEXT Function. Convert an array (cell range) to text.

Japanese version.

Function to convert an array (cell range) to text. Currently, this function is of little use.

仕様

=ARRAYTOTEXT(Array,Format)
NameOmissionDescription
ArrayRequired argument.
Format00:Default.
Concise format that is easy to read. The text returned will be the same as the text rendered in a cell that has general formatting applied.

1:Strict format that includes escape characters and row delimiters.
Generates a string that can be parsed when entered into the formula bar. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors.

Demonstrate

Here is an example of an array (cell range) and the result.

Specify 0 for the format

If 0 is specified for the format, the result is similar to the TEXTJOIN function. Dates are converted to serial values.

123, 123.456, 123, ABC, TRUE, FALSE, 45068, 45068.5213541667

Specify 1 for the format

If 1 is specified for the format, it will be a textual representation of an array (cell range). Dates are converted to serial values and text is enclosed in double-cotation marks.

{123;123.456;"123";"ABC";TRUE;FALSE;45068;45068.5213541667}

= and specify it in the formula bar, it will be expanded into a cell range as a spill.

Cell ranges can be converted with multiple columns.

{"Numeric (Integer)",123;"Numeric (Small number)",123.456;"Text(Numeric)","123";"Text","ABC";"Boolean",TRUE;"Boolean",FALSE;"Date",45068;"Date and Time",45068.5213541667}

It can also be used for functions that take an array (cell range) as an argument.

Although infrequent, it is useful when you want to create a single cell formula without using a cell range.

Code for arrays in programming languages

It is efficient to use as an aid in creating code when you want to initialize an array with a large number of items.

one-dimensional array

If the values are arranged on a single line and the parentheses are converted with the SUBSTITUTE function, they become an array, such as in JavaScript or OfficeScript.

JavaScript, etc.
=SUBSTITUTE(SUBSTITUTE(ARRAYTOTEXT(CellRange,1),"{","["),"}","]")
Power Automate Desktop

Use the SUBSTITUTE function to convert double-cotation marks to single-cotation marks (although they are automatically converted without conversion), and enclose them in % to create a Power Automate Desktop list.

two-dimensional array

Further conversions with the SUBSTITUTE function can be increased to create two-dimensional arrays.

JavaScript, etc.
="["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYTOTEXT(CellRange,1),"{","["),"}","]"),";","],[")&"]"
Power Automate Desktop
="%["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYTOTEXT(CellRange,1),"{","["),"}","]"),";","],["),"""","'")&"]%"

---

Links

Microsoft Excel Functions Text