ARRAYTOTEXT

What it does

ARRAYTOTEXT(array, [format])

ARRAYTOTEXT renders a whole array — or a range — as a single string.

It has the same two registers as its scalar sibling VALUETOTEXT, and the difference between them is sharper here because an array has structure to lose.

Concise (format = 0, the default) walks the array in row-major order and joins the elements' General-format renderings with ", ". Row structure is not preserved: a 3×2 array and a 1×6 array with the same elements in the same order render identically. Microsoft's page shows this directly — a two-column, three-row range renders as a single comma-separated run.

Strict (format = 1) renders an array literal: braces around the whole thing, commas between columns, semicolons between rows, text elements quoted, and Booleans, numbers and errors left unquoted. This preserves shape and kind, and it reparses. Microsoft's page shows a 3×2 range becoming {TRUE,#VALUE!;1234.01234,"Seattle";"Hello","1,123"} — which is worth studying, because it contains the two facts that matter most: an error appears as bare #VALUE! (a rendered error, not a propagated one) and the number 1123 formatted with a thousands separator appears as the quoted string "1,123", because concise/strict rendering follows the cell's displayed text, not its underlying number.

That last point is the trap. ARRAYTOTEXT renders what the cells show, so a formatted number can come back as text that no longer parses as that number.

Arguments

ARRAYTOTEXT(array, [format])

Two things about the first position. It accepts a range, not only an array value — which is the usual way it is used, and which means the reference is resolved to values before the function sees it (see the call pipeline). And a scalar is accepted, promoted to a 1×1 array; upstream records that promotion explicitly, and it is why ARRAYTOTEXT("abc") is not an error.

The format argument admits exactly 0 and 1; Microsoft documents anything else as #VALUE!.

Try it

Quirks

Result and edge cases

The return kind is Text — always a single scalar string, never an array. ARRAYTOTEXT is a reducer: it is the one place in this part of the catalogue where a grid goes in and a scalar comes out.

• The 32,767-code-unit cap is genuinely reachable. Rendering a large range produces a long string, and strict mode adds quoting overhead. The cap and its two enforcement paths — truncation on the interop path, #VALUE! on the formula path — are described in the value universe. Which one ARRAYTOTEXT meets has not been probed.
• Errors inside the array are rendered, not propagated, in the documented example. That is a striking exception to the usual discipline in coercion and lifting, where coercion never silently discards a worksheet error — here the error is neither discarded nor propagated but displayed. Whether an error arriving as the whole array argument behaves the same way is a separate question and is not settled here.
• Empty cells inside a range have no obvious rendering. Whether they contribute an empty element, a zero, or nothing at all to the concise join is not established.
• Row-major order is the documented traversal, so concise mode reading order matches how you read the range on screen.
• Concise mode loses shape irreversibly. If you need the shape back, use strict mode.

Errors

How to implement

Notes for implementers

• Reuse the engine's General-format renderer, exactly as for VALUETOTEXT. Concise mode is defined by reference to cell display, and the documented example shows that cell formatting reaches the output. An implementation that renders from the underlying double will disagree with Excel on every formatted cell.
• Strict mode's separators are formula-language separators, which are locale-dependent in Excel. A strict rendering that is meant to reparse must use the separators of the locale it will be pasted into. This is a real portability hazard and deserves a test.
• Quote only what needs quoting. The documented example quotes text and leaves Booleans, numbers and errors bare; a uniform-quoting implementation is visibly wrong.
• Escape inner quotes by doubling, and test with a string that contains one.
• Traverse row-major and do not assume square.
• Check the result against the text cap and decide which failure mode you implement.
• Decide what an empty cell renders as and write it down; it is the most likely silent divergence.

Rust code

7 arraytotext_fn "ARRAYTOTEXT" (1, 2) [R "array", V "format"] |s, a, sv| { arraytotext(s, a, sv) }

fn arraytotext(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    // An array that is one error value is the result; an error inside an array is shown (Excel
    // 16.0.20430, 2026-10-25, dc-6au: `=ARRAYTOTEXT(#REF!)` #REF!, `=ARRAYTOTEXT(#DIV/0!,1)`
    // #DIV/0!, `=ARRAYTOTEXT({1,#N/A})` "1, #N/A").
    if let CoreRef::Error(code) = a[0].core_ref() {
        return fail(code);
    }
    let strict = binary_flag(site, a, 1)?;
    let g = grid(site, a, 0, sv)?;
    let mut parts = Vec::with_capacity(g.cells.len());
    for value in &g.cells {
        parts.push(value_text(site, 0, value, strict)?);
    }
    let shown = if strict {
        let rows: Vec<String> = parts.chunks(g.cols.max(1)).map(|row| row.join(",")).collect();
        format!("{{{}}}", rows.join(";"))
    } else {
        parts.join(", ")
    };
    Ok(crate::catalogue::kit::text_value(&shown))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• VALUETOTEXT — the scalar counterpart, same format argument, same two registers. The pair splits on input shape.
• TEXTSPLIT — the closest thing to an inverse, and its sibling in the same upstream implementation module. TEXTSPLIT turns a delimited string into a grid; concise ARRAYTOTEXT turns a grid into a delimited string. They do not compose cleanly, because concise mode drops row structure and quotes nothing.
• TEXTJOIN — the older and more controllable flattener: you choose the delimiter and whether to ignore empties, but you get no shape and no quoting. TEXTJOIN(", ", FALSE, range) is the hand-rolled approximation of concise mode, and it differs at least in how it renders errors and formatted numbers.
• CONCAT — flattens a range with no delimiter at all.
• TEXT — formats a single number with a supplied format string; unrelated except by name confusion.
• Strict mode is the function to reach for when you want to see what a dynamic-array formula actually produced, which makes it a debugging tool as much as a data function.

Named variants

No named variants are recorded yet.