| Syntax |
| =ARRAYTOTEXT(array,[format]) |
| Purpose / Description |
| Returns a text string representing an array of values formatted as a string. May employ concise (a simple, readable comma separated list) or strict formatting (formatted as na array that could be passed to a formula) rules. |
| Arguments |
| Name |
Type |
Description |
| array |
reference or
array of any |
A constant, constant array, reference or nested expression that provides the array of values that is to be represented as a text string. |
| [format] |
logical |
An optional constant, reference or nested expression that provides a logical flag that indicates if strict formatting is to apply. Defaults to 0 if omitted.
| |
Concise |
the values appears as simple comma separated listing of values in a text string representation. |
| 1 |
Strict |
text strings are included in quotation marks, entries separated by commas (along rows) or semi-colons (separating rows) and the string includes the braces for array notation. |
|
| Return Values |
| Type |
Specific Value |
Description |
| text string |
|
The text string representation of the array of values, including any errors is presented as a text string. |
| error |
#VALUE! |
If the array argument refers to a single blank cell. If array refers to a range of blank cells, it will be returned as a text string as above. |
| error |
#CALC! |
If the length of the text string, including delimiters and formatting characters exceeds 32,767 characters - the limit for a text string stored in a cell in Excel. |
| Notes / Comments / Advice / Warnings |
| Note: |
Text strings are passed unmodified to the output, all numbers, logical and error values are converted to the same representation they would have in General number format, removing any formatting that may have been applied to the values in the cells. |
| Comment: |
This function is useful in some more abstract and advanced calculations, where it is useful to convert all values to arrays of strings.. It can also be useful to convert the visible contents of cells that have been changed to data types to text for summarising. It can be helpful in converting Excel cell values to JSON data sets. |
| Examples: |
Open / download sample workbook: ARRAYTOTEXT.xlsx |
| |