ARRAYTOTEXT function
ARRAYTOTEXT flattens a range or array into one piece of text. It is handy for showing what a spilled result contains, or for pasting a list into a single cell.
Example
Read it as a sentence: turn the cells in B1:C2 into one line of text, with the values separated by commas.
Excel shows Item, Category, Boxer cartridge, Tools
B
C
1
Item
Category
2
Boxer cartridge
Tools
Items
Syntax
ARRAYTOTEXT(array, [format])
Arguments in [square brackets] are optional.
-
array(required) - The range or array to turn into text.
-
format(optional) - 0 gives a readable comma-separated list (default); 1 gives strict format with quotes and braces, like an array constant.
Returns: A single text string describing the array contents.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=ARRAYTOTEXT({1,"a";TRUE,2}) | 1, a, TRUE, 2 | Concise |
=ARRAYTOTEXT({1,"a";TRUE,2},1) | {1,"a";TRUE,2} | Strict |
=ARRAYTOTEXT(5) | 5 | Scalar |
=ARRAYTOTEXT(Items!B1:C2) | Item, Category, Boxer cartridge, Tools | Range |
=ARRAYTOTEXT(Data!F1:F5) | , 123, 45351, TRUE, #DIV/0! | Blank, text number, date, bool, error |
=ARRAYTOTEXT({1,2},2) | #VALUE! | Bad format |
The Items sheet these formulas read
B
C
1
Item
Category
2
Boxer cartridge
Tools
Items
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
Data
Watch out for
- Strict format (1) wraps text in quotes and uses semicolons between rows.
- Concise format hides the difference between numbers and text that look alike.
Where it works
- Excel
- ARRAYTOTEXT arrived in Excel 2021. Older versions do not have it.
- Google Sheets
- No direct equivalent. Use TEXTJOIN(", ",TRUE,range) for a similar result.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .