The Excel VALUE function converts a number that is stored as text into a real number you can sum, average, and chart. It is the antidote to the classic import headache: a column of “numbers” that SUM stubbornly totals as zero. VALUE understands currency symbols, thousands separators, percent signs, and date/time formats — anything Excel itself would recognize as a number.
=VALUE("$1,250.00") returns the number 1250, and =VALUE("25%") returns 0.25. If the text isn’t a recognizable number, VALUE returns #VALUE!.
Syntax
| Argument | Description | |
|---|---|---|
text | Required | Text that looks like a number, currency amount, percentage, date, or time — in quotes or as a cell reference. Anything Excel would accept typed into a cell, VALUE accepts here. |
Available in: every version of Excel, Excel for the web, and Google Sheets. How to spot the problem VALUE solves: text-numbers sit left-aligned, often wear a green corner triangle, and a SUM over them returns 0.
Fix numbers stored as text
A CSV import lands every “number” as text. One helper column makes them real:
| A | B | |
|---|---|---|
| 1 | Imported text | =VALUE(A2) |
| 2 | 1042 | 1042 |
| 3 | $1,250.00 | 1250 |
| 4 | 25% | 0.25 |
| 5 | 6/19/2026 | 46184 ← date serial |
Dates and times convert to their serial numbers — format the result cell as a date to see 6/19/2026 again. In Excel 365 one formula converts the whole column:
VALUE also rescues digits extracted by other text functions, which always come out as text:
Try it: interactive VALUE demo
Type a text value — with a $, commas, or a % sign — and see what VALUE makes of it.
VALUE vs the alternatives
VALUE is one of several ways to coerce text into numbers — pick by situation:
| Method | Best for |
|---|---|
=VALUE(A2) | Formulas, audit trails, US-style formats including dates and percents |
=A2*1 or =--A2 | Quick inline coercion inside a bigger formula (same recognition rules) |
| NUMBERVALUE | European formats — you specify the decimal and group separators (Excel 2013+) |
| Text to Columns / paste-multiply | One-time, in-place conversion of a whole column with no helper cells |
If VALUE still fails after a TRIM, imports often hide a non-breaking space (character 160) that TRIM ignores. Strip it first:
Errors & common pitfalls
#VALUE! — the text isn’t a recognizable number. =VALUE("12 apples") fails; so does a number with hidden junk around it. Clean first: =VALUE(TRIM(A2)), and for non-breaking spaces from the web, the SUBSTITUTE-CHAR(160) combo above.
Pitfall: locale decides what “a number” looks like. On a US system =VALUE("1.234,56") fails because the separators are European. That exact job is what NUMBERVALUE was built for — it lets you declare the separators explicitly.
Pitfall: dates come back as serials. =VALUE("6/19/2026") returns 46184. That is the date — Excel stores dates as day counts — you just need to format the cell as a date to see it.
Pitfall: VALUE doesn’t fix the source column. It returns converted copies. To repair the original data in place, use the helper column + Paste Special › Values routine, or Data › Text to Columns › Finish on the selected column.
Practice workbook
Frequently asked questions
Why does SUM return 0 over a column that's clearly full of numbers?
=VALUE(A2) in a helper column, or select the column and use Data › Text to Columns › Finish to fix them in place.What's the difference between VALUE and NUMBERVALUE?
Does VALUE handle currency symbols, commas, and percent signs?
Is =VALUE(A2) the same as =A2*1 or =--A2?
Why does VALUE still fail after I used TRIM?
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) to strip it first.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class