The Excel ARRAYTOTEXT function converts an entire range or array into a single text string. In its default concise format it simply lists the values separated by commas; in strict format it returns the array exactly as Excel would write it in a formula — braces, quoted strings, row separators and all. That strict view is the killer feature: it lets you see precisely what a spilled formula such as FILTER or TEXTSPLIT is really returning, hidden spaces and text-numbers included.
1 as the second argument for the strict, formula-style version: {"North",8200;"South",7400}.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range, spilled array, or array constant to convert to text. |
format | Optional | 0 (default) = concise: values joined with “, ” and nothing else. 1 = strict: full array syntax with braces { }, text in quotes, commas between columns and semicolons between rows — a string you could paste straight back into a formula. |
Available in: Excel for Microsoft 365 and Excel for the web only. Excel 2021 and earlier show #NAME?. For a plain joined list with your own delimiter in any modern version, use TEXTJOIN instead.
Concise vs strict format
The same two-row range, both ways. Concise reads nicely in a sentence; strict shows the array’s true structure — which values are text, and where the rows break:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Units | Result | |
| 2 | North | 8200 | =ARRAYTOTEXT(A2:B3) | North, 8200, South, 7400 |
| 3 | South | 7400 | =ARRAYTOTEXT(A2:B3, 1) | {"North",8200;"South",7400} |
Concise mode is built for messages. Glue a whole spilled result into one cell of commentary:
Strict mode is built for debugging. Point it at a formula that “looks right” but misbehaves, and the quotes give the game away — {"42"} is a text-number, {"42 "} has a trailing space, {42} is the real thing:
Try it: interactive ARRAYTOTEXT demo
A 2×2 range sits in A2:B3 (North / 8200, South / 7400). Switch the format argument and watch the output change shape.
Errors & common pitfalls
#NAME? — older Excel. ARRAYTOTEXT exists only in Excel for Microsoft 365 and Excel for the web. In Excel 2021 and earlier the name isn’t recognized — TEXTJOIN is the closest substitute.
Pitfall: concise format erases the structure. North, 8200, South, 7400 could be one row, one column, or a 2×2 grid — you can’t tell. When shape matters, use format = 1.
Pitfall: you get underlying values, not formatted ones. Dates arrive as serial numbers (45831, not 23-Jun-2025) and $8,200 arrives as 8200. Wrap values with TEXT first if you need the display format.
Pitfall: no delimiter control. Concise format always uses a comma-space and strict format follows array syntax — neither is configurable. To join with a dash, a line break, or anything else, that job belongs to TEXTJOIN.
Practice workbook
Frequently asked questions
What's the difference between ARRAYTOTEXT and TEXTJOIN?
What does the format argument actually change?
0 (concise, the default) returns the values joined by “, ” with no decoration. 1 (strict) returns valid array syntax: braces around the whole thing, quotes around text, commas between columns, semicolons between rows — so {"North",8200;"South",7400} tells you the array is 2 rows × 2 columns.Why do my dates come out as numbers like 45831?
=ARRAYTOTEXT(TEXT(A2:A10, "d-mmm-yyyy")) — in Excel 365, TEXT happily processes the whole range at once.When would I use VALUETOTEXT instead?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class