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. |
Notes:
-
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.
