VALUETOTEXT

What it does

VALUETOTEXT(value, [format])

VALUETOTEXT renders a value as text. Its reason to exist is that Excel has two different, equally legitimate answers to "what does this value look like as text", and this function lets you ask for either one.

Concise (format = 0, the default) returns the value as it would be rendered in a cell with General formatting. This is the display register: it is what a human reading the sheet sees. Microsoft's page states this equivalence explicitly, and it is the useful anchor — the output is defined by reference to General-format rendering, not by an independent formatting rule.

Strict (format = 1) returns the value in a form suitable for embedding in a formula. Microsoft's page describes it as including escape characters and row delimiters, and generating a string that can be parsed when entered into the formula bar. Text is quoted, with inner quotes escaped; Booleans, numbers and errors are left unquoted. This is the round-trip register: the point is that pasting the result into the formula bar reconstructs the value.

The distinction is exactly the distinction between showing a value and denoting it. "Hello" displays as Hello and denotes as "Hello". The empty string displays as nothing and denotes as "" — and only the second of those two is recoverable.

For anyone building tooling on top of a workbook, that makes strict mode the interesting one: it is the only ordinary worksheet function that produces a machine-reparseable rendering of an arbitrary value.

Arguments

VALUETOTEXT(value, [format])

The format argument is a mode selector with exactly two admitted values, and this is one of the few places in the text family where an out-of-range option is documented as an error rather than being clamped or ignored. Do not pass TRUE expecting it to mean 1 without checking; whether the logical coerces into the admitted set is not something this page asserts.

The first position takes any value, which is the function's whole point. It is not restricted to scalars in the sense of rejecting other kinds — it is the scalar renderer, and how it responds to an array argument is discussed below.

Try it

Quirks

Result and edge cases

The return kind is Text. Upstream's contract records the result as always a scalar text value.

• Errors are rendered, not propagated, in one specific sense — and propagated in another. Microsoft's strict-mode description says errors are left unquoted, which implies they are rendered as their error text. Upstream's contract, by contrast, records error values in the first argument as propagating in both format modes. These two readings can both be true of different situations — an error inside an array being rendered, versus an error arriving as the whole scalar argument — but the Handbook has not established which applies where. This is a genuine open point and it is listed below rather than resolved by assertion.
• Numbers render as decimal strings in concise mode, which means the General-format rendering, including its width-dependent behaviour in a cell. Whether VALUETOTEXT's rendering depends on column width the way a cell's does is not established and is a probe worth running.
• The empty string is distinguishable in strict mode ("") and not in concise mode.
• An empty referenced cell delivers Empty, distinct from an omitted argument; see the value universe.
• Array arguments. ARRAYTOTEXT is the function for arrays. Whether VALUETOTEXT lifts elementwise over an array or delegates to array rendering is a shape question the Handbook has not settled; see the probes below.

Errors

The tension between error propagation and error rendering noted above is unresolved; treat the second row as upstream's reading rather than as settled Handbook fact.

How to implement

Notes for implementers

• Do not write your own number renderer. Concise mode is defined as General-format rendering, so it inherits every subtlety of that: the switch to scientific notation, the significant-digit budget, negative-zero handling. Reusing the engine's General formatter is the only way to stay consistent; a fresh format!("{}", x) will diverge.
• Strict mode is a grammar, so write it as one. Quoting, quote-escaping (doubling), the column separator, and the row separator are all part of a syntax that must reparse. The separator characters are locale-dependent in Excel's formula language, which means strict mode has a locale dependency that concise mode does not obviously share.
• The two modes must agree on nothing. Do not implement strict as concise-plus-quotes; the number renderings can legitimately differ.
• Decide the error question explicitly and test it: error as argument, error inside an array, and error as the format argument are three different paths.
• Validate format before rendering, so that VALUETOTEXT(error_value, 7) has a defined precedence between the two failures.

Rust code

6 valuetotext_fn "VALUETOTEXT" (1, 2) [V "value", V "format"] |s, a, sv| { valuetotext(s, a) }

fn valuetotext(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let strict = binary_flag(site, a, 1)?;
    let shown = value_text(site, 0, &a[0], strict)?;
    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

• ARRAYTOTEXT — the array counterpart, introduced alongside this function and sharing its format argument and its two registers. The pair splits by input shape: one value, or a grid.
• TEXT — the formatting function, and the one readers confuse with this. TEXT(x, fmt) applies a number-format string you supply; VALUETOTEXT applies General formatting or a fixed strict grammar. TEXT cannot render a logical or an error usefully; VALUETOTEXT is defined for every value kind.
• VALUE — despite the name, not the inverse. VALUE parses text into a number under locale rules; VALUETOTEXT renders any value as text. The nearest thing to an inverse of strict mode is the formula parser itself.
• T — the old narrow relative: returns the text if the argument is text and the empty string otherwise. It is a filter, not a renderer.
• CONCAT and & perform implicit to-text conversion by the general rule in coercion and lifting. That conversion is not guaranteed to agree with VALUETOTEXT(x, 0) for every kind, and where they differ is worth knowing.

Named variants

No named variants are recorded yet.