ARRAYTOTEXT in Excel: Serialize Ranges and Preserve Shape
Represent an Excel range as readable or strict text, inspect row and column delimiters, and avoid confusing serialization with a pasted formula or live array.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: ARRAYTOTEXT(array,[format]) represents a range as text. Format 0 creates a readable list; format 1 produces a strict string with braces, row delimiters and quoted text values, preserving the input structure in the representation.
Availability and version history
- Introduced: the Microsoft function catalogue marks this identity as an Excel 2021 addition. An exact first build or preview date is not established.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac.
- Checked: October 14, 2026; official ARRAYTOTEXT reference. Documentation and independent models; no native Excel execution or wider platform certification.
Syntax and a documentation distinction
=ARRAYTOTEXT(array,[format])
Format defaults to 0. Values other than 0 or 1 have documented #VALUE!. Microsoft describes strict output as a string suitable for parsing in the formula bar, but another note calls it an array of the same shape. The worked result is serialized text with shape delimiters. Read the shape claim in that representational context; do not assume that typing ARRAYTOTEXT automatically recreates a live, multi-cell copy of the source.
The examples below use the English array-literal convention illustrated by Microsoft: commas between columns and semicolons between rows. Native parsing, locale variations and spill behavior have not been executed here.
Example 1: represent a two-by-two range
Enter the following data in A2:B3. A3 is text, not a cell reference.
| A | B |
|---|---|
| East | 12 |
| West | 8 |
Put this in D2:
=ARRAYTOTEXT(A2:B3,0)
The expected concise text is East, 12, West, 8. Omitting the format argument gives the same default representation.
In D3 use:
=ARRAYTOTEXT(A2:B3,1)
The strict representation is {"East",12;"West",8}. It records two columns per row, separated by a comma, and two rows, separated by a semicolon. The words are quoted; the numbers are not. The braces and quotation marks are characters in the serialized result.
Example 2: distinguish orientation
Compare three simple numeric ranges entered separately:
| Input shape | Values | Strict representation |
|---|---|---|
| E2:F2, one row | 1, 2 | {1,2} |
| E5:E6, one column | 1 then 2 | {1;2} |
| H2:I3, two rows | 1,2 then 3,4 | {1,2;3,4} |
Use =ARRAYTOTEXT(E2:F2,1), =ARRAYTOTEXT(E5:E6,1) and =ARRAYTOTEXT(H2:I3,1) in separate free cells. These are three distinct serialized structures. The first two both read 1, 2 in concise mode, so a readable flat list alone cannot prove the original orientation.
This distinction is useful when documenting a test case: record the source dimensions or retain strict delimiters instead of pasting an ambiguous comma-separated list into a bug report.
Advanced example: inspect mixed types
Set K2 to numeric 10, L2 to text 10 using an apostrophe on entry, K3 to =TRUE() and L3 to =VALUE("x"), which creates an actual #VALUE! error.
=ARRAYTOTEXT(K2:L3,1)
The documented-rule model is {10,"10";TRUE,#VALUE!}. The first row preserves a number-versus-text distinction that concise mode hides. The second keeps Boolean and error tokens unquoted in the strict representation.
The source error in L3 is still an error. A text report containing its name does not validate or repair the source calculation. Keep the original range available so reviewers can inspect formulas and types, not merely a rendered diagnostic string.
Use cases and serialization limits
ARRAYTOTEXT is useful for a compact record of small test inputs, checking orientation and making type differences visible. It is less suitable as a substitute for a whole workbook export: formats, formulas, validation rules and provenance are not reconstructed simply by recording values.
Do not assume the result is JSON or a CSV record. The row/column delimiters and quoting belong to the documented Excel representation. Text that itself contains commas, semicolons, quotation marks or newlines needs an explicitly verified escaping workflow; the ordinary-word examples here do not certify all such cases.
If someone supplies a formula-like string, inspect its origin before pasting it as a formula. No formula evaluation or external request is needed to understand these examples. A text representation and the action of interpreting that text are separate steps.
For a single value use VALUETOTEXT. Use TEXTJOIN for a deliberately chosen readable delimiter and TRANSPOSE when you actually need to change an array’s orientation rather than describe it as text.
References and verification
Expected outputs are independently calculated examples or documented rules, not captured native Excel results. Formulas use English function names and comma argument separators. Locale, rendering and boundary restrictions are stated where relevant.