The ARRAYTOTEXT function returns an array of text values from any specified range. It passes text values unchanged, and converts non-text values to text.
Syntax
ARRAYTOTEXT(array, [format])
The ARRAYTOTEXT function syntax has the following arguments.
| Argument | Description |
|---|---|
| array | The array to return as text. Required. |
| format | The format of the returned data. Optional. It can be one of two values: 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. |
Note
- The concise format returns a list of values inside one cell, whereas the strict format returns an array of the same size and shape as the input.
- If format is anything other than 0 or 1, ARRAYTOTEXT returns the #VALUE! error value.
Examples
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Note To convert "Seattle" to a linked data type, Select cell B3, and then select Data > Geography.
| Data | |
|---|---|
| TRUE | #VALUE! |
| 1234.01234 | Seattle |
| Hello | $1,123.00 |
| Formula | Result |
| =ARRAYTOTEXT(A2:B4,0) | TRUE, #VALUE!, 1234.01234, Seattle, Hello, 1,123 |
| =ARRAYTOTEXT(A2:B4,1) | {TRUE,#VALUE!;1234.01234,"Seattle";"Hello","1,123"} |
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.