TEXT

What it does

TEXT(value, format_text)

TEXT(value, format_text) applies a number format code to value and returns the formatted rendering as a text string.

The important structural fact is that format_text is written in the same number-format language as the Format Cells dialog — the 0, #, ?, ., ,, %, E+, @ placeholder vocabulary, the four semicolon-separated sections for positive / negative / zero / text, the [...] condition and colour clauses, and the date and time codes. TEXT is not a small formatting helper with its own mini-language; it is the cell-formatting engine exposed as a function, and Microsoft's page documents it by pointing at the number-format guidance rather than by listing a closed grammar. Its own note is that it does not support every format code.

The difference from cell formatting is what happens to the result. A formatted cell holds a number and displays it formatted; TEXT produces an actual text value. Everything downstream changes accordingly — sorting, comparison, arithmetic, lookups. Microsoft flags this on the page: converting numbers to text can make them hard to reference in later calculations. The common mistake is using TEXT to make a number look right when the intent was a cell format.

Arguments

Both are required; there is no default format.

format_text must be quoted text or a reference to text. A bare TEXT(A1, 0) passes the number zero where a format string is expected, and what happens then is a coercion question, not a formatting one — see Coercion and lifting.

The commonly misunderstood position is value: the documentation describes it as numeric, but number formats have a fourth, text section (@). In a four-section format numbers use the other sections, while a lone @ applied to a number gives the General display (FN-TEXT-002). What TEXT does with a text value under a format with and without an @ section is not something this page can state.

Try it

Quirks

The locale problem

format_text is a string interpreted in a language, and this bites harder than any other property of the function.

The characters that mean "decimal separator" and "group separator" in a format code are the ones the workbook's language uses, not fixed . and ,. A format literal that produces 1.234,56 in one language setting produces something else, or nothing useful, in another — and the format string is stored in the formula verbatim, so the workbook carries a language-specific literal across every machine it is opened on. The same is true of the date and time code letters, which are localized: the code for "year" is not y everywhere.

This is not a hypothetical fragility. It is why the number-format guidance Microsoft's TEXT page links documents locale-identifier prefixes in the [$-...] form for pinning a format to a specific language regardless of the host.

Two independent signals in the Handbook's own data agree that this dependence is structural rather than incidental:

TEXT is therefore the mirror image of NUMBERVALUE, which exists precisely so that the parsing direction can escape locale dependence. There is no equivalent escape hatch in the formatting direction: TEXT has no separator arguments, and pinning the locale means embedding a locale identifier in the format string.

Result and edge cases

Returns Text.

Open rather than settled, because Microsoft's page does not address them:

• Text value. Passed through, formatted by the @ section, or #VALUE! — unverified.
• Empty format_text. An empty format string is legal in the Format Cells dialog, where it hides the value; whether TEXT(1, "") returns the empty string is unverified.
• Logical value. Whether TRUE coerces to 1 and formats, or renders as TRUE.
• Unsupported codes. The page states that not all format codes are supported, but does not say what an unsupported code does — error, ignore, or literal.
• Rounding. TEXT(0.5, "0") requires a rounding decision at the format boundary, and which rule applies (half-up, half-even, or the display rounding the cell formatter uses) is not stated. This is the single most numerically consequential unanswered question on the page.

Errors

Microsoft's TEXT page carries no error table. The reachable errors are the shared ones: an error value in either argument propagates, and a value that cannot convert to the expected kind surfaces #VALUE! under the shared coercion rules. Whether an invalid or unsupported format_text produces #VALUE! is unverified and is on the probe list.

How to implement

Notes for implementers

The rounding rule at the format boundary needs to be established before anything else, because it is the part that produces silently wrong numbers rather than visibly wrong strings. Rendering is the easy half of this function; deciding the last digit is the hard half.

Section selection — positive / negative / zero / text — is a per-format parse, not a per-value branch, and the number of sections present changes the meaning of the ones that are there. That parse has to happen once per format string, not once per value, or performance and semantics both suffer.

Rust code

2 text_fn "TEXT" (2, 2) [V "value", V "format_text"] |s, a, sv| { text(s, a) }

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

• VALUE is the inverse direction, and NUMBERVALUE is the locale-independent inverse. The asymmetry is worth stating: the parse direction has an explicit-separator escape hatch, the format direction does not.
• T is the function readers confuse with this one on the strength of its catalogue description. T is a type filter that returns "" for a number; TEXT renders a number. They have nothing in common.
• TEXTJOIN, CONCAT, & consume TEXT output constantly — building a sentence around a formatted figure is the function's most common use.
• Cell number formatting is the alternative that is usually correct: if the goal is appearance rather than a text value, format the cell and leave the number a number.
• TEXT is not a Compatibility-category function and is not superseded.

Named variants

No named variants are recorded yet.