VALUE

What it does

VALUE(text)

VALUE parses text into a number.

It is tempting to describe this as "the inverse of TEXT", and that is the right intuition, but the honest description is narrower and stranger: VALUE accepts the text forms that Excel itself would recognise as a number if you typed them into a cell, under the current locale, and returns the number they denote. That is not a fixed grammar. It is a grammar parameterised by the machine's regional settings.

The forms in play include, at minimum: plain decimals, scientific notation, digit-group separators, a currency symbol, a percent sign, and date and time text. Each of those brings a locale dependency:

• the decimal separator is . in some locales and , in others, which means "1,5" is one and a half in one place and fifteen — or a rejection — in another;
• the group separator is the mirror image of that problem, and a space in several locales;
• the currency symbol is per-locale, and VALUE accepting "$1.00" says nothing about whether it accepts "€1,00"; in the recorded en-ZA settings "€1" is read and "$1" is not (FN-VALUE-003);
• date text is the worst case, because "1/2/2024" denotes two different days in two locales and returns a serial number, not the digits you can see.

The Handbook's shared coercion chapter already draws this boundary: it pins the plain-number core and states that the locale-dependent surface — separators, currency, percent, date and time text — is an open area. See coercion and lifting. VALUE is the function where that open area is the entire subject matter.

Two structural facts finish the definition. A number arriving as the argument passes through unchanged. And a successful date-text parse returns the date's serial number — a plain Number, formatting-free — which is why VALUE("2024-01-02") shows as a large integer unless the cell is formatted as a date.

Arguments

VALUE(text)

One argument. The subtleties are all about what may occupy it:

• Already-numeric input passes through. VALUE(5) is 5.
• Logicals are the surprising rejection: upstream's contract records logical, blank, missing, and unsupported aggregate or reference payloads as #VALUE! in its admitted slice — so VALUE is stricter than the general to-number coercion, which maps TRUE to 1. That is a real difference between VALUE(TRUE) and SUM(TRUE), and it is upstream's recorded behaviour rather than a Handbook finding.
• Leading and trailing whitespace is ignored by the general to-number rule; whether VALUE matches that exactly, and whether a whitespace-only string is rejected as upstream records, is worth confirming rather than assuming.
• An empty referenced cell delivers Empty, which is a distinct outcome from an omitted argument; see the value universe.

Try it

Quirks

Result and edge cases

The return kind is Number.

Edges that matter in practice:

• Percent text divides. If "50%" is accepted, the result is 0.5, not 50. A pipeline that parses percentages with VALUE and then divides by 100 is wrong by a factor of 100 exactly when the parse succeeds.
• Date and time text returns serials. Time text returns a fraction of a day. VALUE("12:00") is 0.5 if accepted.
• Overflow is not infinity. The shared coercion rule states that text parsing must produce a finite number; text denoting an overflow is treated as non-numeric rather than as infinity.
• Precision is the parser's, not the text's. VALUE of a 30-digit numeral returns the nearest double, silently. Round-tripping through TEXT and back is lossy in both directions and the losses do not cancel.
• Arrays. Whether VALUE lifts over an array argument is not something this page asserts; the general rules are in coercion and lifting, and the specific answer for VALUE is listed as an open probe below.
• Errors propagate.

Errors

Microsoft's page is the authority on the documented error conditions and is linked below; it was not retrievable while this entry was written, so this section states expectations rather than quoting it.

Note what is not here: there is no error for precision loss, and none for a value that parses but means something other than what the author intended. VALUE fails loudly or succeeds silently.

How to implement

Notes for implementers

• Make the locale an explicit input, not ambient state. Upstream's design — a locale-profile seam queried from the host, with the parser written against the profile — is the right shape. A parser that reads the process locale at call time is untestable and irreproducible.
• Do not use the host language's number parser. strtod, double.Parse, and f64::from_str each accept things Excel does not (hex floats, inf, nan, underscores in some languages) and reject things Excel does (currency symbols, percent, grouped digits). Every one of those mismatches is a silent divergence.
• Reject non-finite parses. The shared rule is explicit: text that parses to an overflow is non-numeric, not infinite.
• Keep the date lane separate from the number lane, and be prepared for the two to disagree about the same string — "1.5" as a number and as a date are both plausible readings, and the precedence between them is a real decision that must be recorded.
• Percent must compose with grouping rather than being a separate grammar, but it does not combine with currency: "€1%" is #VALUE! in the recorded en-ZA settings (FN-VALUE-003). It also counts toward the exponent range check: "0e-307" is 0 but "0e-307%" is #VALUE! (FN-ABS-001).
• Round once. Parse to the exact decimal value and round to the nearest double a single time; parsing digit-by-digit with accumulating multiplication introduces error that no test of small numerals will catch. Excel also checks the exponent range when the mantissa is zero, so "0e309" is #VALUE! (FN-ABS-001).

Rust code

1 value_fn "VALUE" (1, 1) [V "text"] |s, a, sv| { value(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

• TEXT — the opposite direction, and the function VALUE is usually paired with. TEXT is equally locale-dependent, in the format-string argument as well as the output, which means a VALUE(TEXT(x, fmt)) round trip is doubly exposed to locale.
• NUMBERVALUE — the function you probably want. It takes explicit decimal and group separator arguments, which removes the ambient-locale dependency that makes VALUE non-portable. NUMBERVALUE does not supersede VALUE in Microsoft's catalogue and both remain current, but for any workbook that travels between machines, NUMBERVALUE is the safer instrument. Upstream notes that NUMBERVALUE's own locale-default lanes are separately blocked and unresolved, so it is not a free lunch either.
• -- (double unary minus) and +0 are the idiomatic shorthands for text-to-number coercion. They go through the general to-number rule described in coercion and lifting, not through VALUE's parser, so they are not interchangeable with VALUE — most visibly for logicals.
• DATEVALUE and TIMEVALUE — the specialised parsers for the date and time lanes that VALUE also happens to cover. When you know you have a date, they say so in the formula and are easier to reason about.
• VALUETOTEXT is not the inverse of VALUE despite the name symmetry; see VALUETOTEXT, which renders any value as text for display or for formula-bar round-tripping.
• ISNUMBER(VALUE(x)) is the common "does this parse" test, and it inherits every locale question on this page.

Named variants

No named variants are recorded yet.