ARRAYTOTEXT Function. Convert an array (cell range) to text.
Function to convert an array (cell range) to text. Currently, this function is of little use.
仕様
=ARRAYTOTEXT(Array,Format)
Name | Omission | Description |
---|---|---|
Array | Required argument. | |
Format | 0 | 0: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),"{","["),"}","]"),";","],["),"""","'")&"]%"

---
Discussion
New Comments
No comments yet. Be the first one!