Excel VALUE & NUMBERVALUE — Turn Text Back Into Numbers That Calculate

TL;DR — VALUE takes a text string that looks like a number and returns the actual number, so it can be summed, sorted, and fed to math. Syntax: =VALUE(text). =VALUE("1,234.50") returns 1234.5. It is the exact inverse of TEXT. You rarely type it on purpose — you reach for it when data arrives as text (the "numbers stored as text" green triangle) and refuses to add up. Its one weakness: it obeys your system locale for decimal and thousands separators, so a file from a European colleague breaks. That's what NUMBERVALUE is for — you tell it which separators to expect: =NUMBERVALUE("1.234,56", ",", ".") → 1234.56.

=VALUE("1,234.50")                 ' -> 1234.5   (now a real number)
=VALUE("$9.00")                    ' -> 9
=NUMBERVALUE("1.234,56", ",", ".") ' -> 1234.56  (European format, any locale)

Nobody sits down meaning to convert text to numbers. What actually happens is you paste from a website, open a CSV, or pull from an export, and Excel decides your numbers are text: they cling to the left of the cell, wear a little green warning triangle, and SUM reports 0 as if the column were empty. VALUE and its locale-aware sibling NUMBERVALUE are the formula answer. This guide explains why the problem happens, the two functions, and the faster fixes that often beat both.

What you'll learn

The mental model: VALUE is the inverse of TEXT — text-number → number
The real cause: "numbers stored as text" and how to recognise it
VALUE basics, and the locale trap with separators
NUMBERVALUE — parsing foreign number formats on any machine
The faster no-formula fixes: *1, double-unary --, Text to Columns
Why "5"+2 works but SUM of text-numbers doesn't — the key asymmetry

The mental model: the inverse of TEXT

TEXT turns a number into a formatted string. VALUE runs the film backwards: it reads a string that describes a number and gives you the number itself.

=TEXT(1234.5, "#,##0.00")   ' number -> "1,234.50"  (text)
=VALUE("1,234.50")          ' "1,234.50" (text) -> 1234.5  (number)

The word "looks like" is doing the work. VALUE succeeds only when the string is something Excel would recognise as a number in its current locale — "1,234.50", "$9", "75%", "2.5E3" all parse; "twelve" or "12 apples" return #VALUE!. Hold the picture: VALUE is a parser, not a cleaner — it reads a well-formed number written as text, it doesn't strip out junk.

The real problem: numbers stored as text

You almost never need VALUE for numbers you typed — you need it for numbers that arrived. After an import or paste, look for these tells:

The values sit left-aligned (real numbers align right).
A green triangle in the corner; hovering says "Number stored as text."
SUM/AVERAGE over the range returns 0 or ignores rows.
Sorting puts "100" before "9" (text sorts character by character).

All four are the same underlying fact: the cell contains the characters 1, 2, 3 — not the quantity one hundred twenty-three. VALUE converts the characters back into the quantity.

=VALUE(A2)          ' A2 holds the text "1234.5" -> 1234.5
=SUM(VALUE(A2:A9))  ' in modern Excel this spills and sums the converted numbers

The locale trap — and why NUMBERVALUE exists

Here is the sharp edge. VALUE interprets separators using your regional settings. On a US machine . is the decimal point and , groups thousands; on a German or French machine those are reversed — . groups thousands and , is the decimal. So a value exported by a European colleague:

' text is "1.234,56"  (European for one thousand two hundred thirty-four point five six)
=VALUE("1.234,56")     ' on a US machine -> #VALUE! or a wrong 1.23456 — broken

NUMBERVALUE removes the guesswork by taking the separators as arguments, so the same formula gives the same answer on any machine:

=NUMBERVALUE("1.234,56", ",", ".")   ' decimal="," group="." -> 1234.56 everywhere
=NUMBERVALUE("12 345,60", ",", " ")  ' French thin-space grouping -> 12345.6

The rule: if the text comes from a different locale than the machine running the formula, use NUMBERVALUE and state the separators. It's the difference between a workbook that survives being opened in another country and one that silently corrupts numbers.

The faster fixes that need no function

Often the quickest cure isn't VALUE at all. Because a text-number is one math operation away from becoming numeric, any arithmetic that doesn't change the value coerces it:

Multiply by 1: =A2*1. Double unary: =--A2 (a negative of a negative). Both force the text to a number and are shorter than VALUE.
Paste Special → Multiply by 1: select the range, copy an empty cell, Paste Special → Operation Multiply — converts in place, no helper column.
Text to Columns: select the column, Data → Text to Columns → Finish. Excel re-parses each cell and drops the text flag. Best for a one-off cleanup.
The green-triangle menu: select the cells, click the warning icon → Convert to Number. Fine for a handful.

Reach for VALUE/NUMBERVALUE when the conversion must be a live formula (the source text keeps changing) or when locale matters; reach for *1 or Text to Columns for a quick, static cleanup.

The asymmetry worth knowing

Modern Excel does quietly coerce text-numbers in many contexts — ="5"+2 returns 7, and =A1*1 works — which makes VALUE feel less necessary than it was a decade ago. But there is one place coercion never happens, and it's the place that matters most:

="5" + 2          ' -> 7    (arithmetic coerces the text)
=SUM("5", 2)      ' -> 7    (literal text arguments are coerced)
=SUM(A1:A9)       ' -> IGNORES any text-numbers in the range  <-- the trap

Aggregation functions skip text inside a range. SUM, AVERAGE, MAX, and friends walk past text-numbers as if they were labels. That's why a column that looks full can total to zero. The fix is to make the values genuinely numeric — with VALUE, *1, or Text to Columns — before you aggregate.

The judgment call

The strongest fix is the one furthest upstream: stop the text from being created in the first place. If a CSV import is the culprit, set the column type to number during import (or in Power Query); if a system exports numbers with stray characters, fix the export. VALUE and its cousins are the right tool when you can't touch the source and need the numbers now — but if you're running the same cleanup every week, that's a signal to fix the pipeline, not to add another VALUE column.

For the reverse trip — a real number you want to display as formatted text — see TEXT. For dates trapped as text, the specialist is DATEVALUE.

How ExcelMaster helps

"Numbers stored as text" is one of those problems that's obvious once you know the tell and baffling before — and the locale version (a European file on a US laptop) defeats even experienced users. ExcelMaster spots the pattern from the data itself: it sees a column of right-looking numbers that won't sum, identifies whether the separators are local or foreign, and converts them cleanly — with NUMBERVALUE and the correct separators when the file came from abroad, or a one-shot in-place fix when it didn't. You describe the symptom ("these won't add up"); it diagnoses the cause.

Frequently asked questions

How do I convert text to numbers in Excel?

Fastest: multiply by 1 (=A1*1) or use double-unary (=--A1). As a function: =VALUE(A1). For a whole column with no formula, use Data → Text to Columns → Finish, or Paste Special → Multiply.

Why does VALUE return #VALUE!?

The string isn't a well-formed number in your locale. Causes: stray text or units ("12 kg"), a foreign separator ("1.234,56" on a US machine — use NUMBERVALUE), or genuinely non-numeric text. VALUE parses numbers written as text; it does not strip out non-numeric characters.

What's the difference between VALUE and NUMBERVALUE?

VALUE uses your system's decimal and thousands separators. NUMBERVALUE lets you specify them: =NUMBERVALUE(text, decimal_sep, group_sep). Use NUMBERVALUE whenever the text comes from a different regional format than the machine running the formula.

Why does my SUM ignore some numbers?

They're stored as text (left-aligned, green triangle). SUM skips text inside a range. Convert them first with VALUE, =range*1, or Text to Columns, then sum.

Do I even need VALUE anymore?

Sometimes not — ="5"+2 and =A1*1 coerce text automatically. But SUM, AVERAGE, and other aggregations over a range never coerce, so you still need to convert text-numbers before totalling them.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-02.