VALUE in Excel: Syntax, Examples and Errors
Updated 2026-10-15 · Free · Works in Excel & Google Sheets
Syntax
=VALUE(text)Quick answer
VALUE turns text that looks like a number into a real number: =VALUE("12345") returns 12345 ready for SUM. It also parses dates, times and percentages your locale recognizes, and fails with #VALUE! when the text hides spaces, currency symbols or separators it does not expect — clean with TRIM first.
Syntax
=VALUE(text)| Argument | What it means |
|---|---|
| text | The quoted literal, cell or expression holding the number-like text. Required — and the only argument. Excel parses it with your locale rules: decimal point or comma, date order, default currency. |
One argument, no options: =VALUE(A2) reads identically in every Excel language. What differs between locales is which text it accepts — "1.5" is one-and-a-half in the US and an error in Germany, where =WERT("1,5") is the one-and-a-half.
VALUE is the bridge between the text world and the number world. Imports, exports and copy-paste leave numbers wearing a text costume — left-aligned, ignored by SUM, sorted as letters — and VALUE is the generic tool that takes the costume off. TRIM cleans the text first, LEN and ISNUMBER verify what you caught, and VALUE does the conversion.
Basic examples
=VALUE(A2)The base conversion: "12345" becomes 12345, "2026/10/15" becomes the serial number 45945, "50%" becomes 0.5. The result is a genuine number — right-aligned, summable, sortable — no matter how texty the source looked.
=VALUE(TRIM(A2))The cleanup combo: imports love padding values with trailing spaces, and VALUE rejects " 12345 " with #VALUE!. TRIM strips the padding first, then the conversion lands. This one-two fixes the majority of failed imports.
=VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), " ")))The invisible-character killer: non-breaking spaces CHAR(160) from web copies survive TRIM, so swap them for normal spaces, then trim, then convert. The full pipeline for pasted web data.
=IFERROR(VALUE(A2), "")The safety net: mixed columns where some cells convert and some never will. VALUE handles the convertible rows and IFERROR turns the hopeless ones into empty cells, so SUM and charts keep working while you investigate.
=IF(ISNUMBER(A2), A2, VALUE(A2))The hybrid-column normalizer: already-numeric cells pass through untouched, text numbers get converted — one formula readies the whole column for SUM, VLOOKUP and pivots.
VALUE rejects any text your locale does not recognize: "$50" fails in a German locale, "1,234.56" fails wherever the comma is not the thousands separator, and "12:30 PM" fails in 24-hour locales. Strip the extras with SUBSTITUTE or match the locale before converting.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| #VALUE! on a clean-looking number | Invisible characters — non-breaking spaces CHAR(160) from web copies survive TRIM, and VALUE refuses anything non-numeric. | Swap first: =VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), " "))). |
| #VALUE! on "$50" | Currency symbols are text — VALUE only parses what your locale prints by default. | Strip the symbol: =VALUE(SUBSTITUTE(A2, "$", "")) — then format the result as currency. |
| #VALUE! on "1,234.56" | Thousands separators outside the locale rules, or a decimal comma arriving in a decimal-point locale. | Remove separators with SUBSTITUTE, or rebuild the number piece by piece before converting. |
| Dates convert to odd numbers like 45945 | Not an error — VALUE parsed the date and returned its serial number, which is how Excel actually stores dates. | Format the result cell as a date, or convert only the true number columns. |
| Converted cells still sort as text | The formula works, but something downstream re-wrapped the result in TEXT, or it was pasted back as text. | Check with =ISTEXT: if TRUE, remove the TEXT wrapper or paste as values once. |
A nuance worth knowing: VALUE accepts real numbers too — =VALUE(12345) returns 12345 unchanged — so wrapping a mixed column never breaks the numeric cells; it only rescues the text ones.
VALUE vs. TEXT
| VALUE | TEXT | |
|---|---|---|
| Direction | Text → number — parses "12345" into 12345 | Number → text — formats 12345 into "12345.00" |
| Purpose | Rescue numbers trapped as text so SUM, lookups and charts work | Display numbers with fixed formatting inside labels and reports |
| Locale sensitivity | Parses by your locale rules — "1.5" vs. "1,5" | Format codes localize too — yyyy/mm/dd vs. JJJJ/MM/TT |
| Round trip | =TEXT(12345, "0") returns "12345" | =VALUE(TEXT(12345, "0")) returns 12345 |
Two ends of the same pipe: TEXT renders a number as display text, VALUE parses display text back into a number. Round-trips are safe with numeric-only format codes — but TEXT("0.00") writes a decimal point that a comma locale will refuse to parse back.
VALUE vs. Flash Fill
| VALUE formula | Flash Fill (Ctrl+E) | |
|---|---|---|
| Output | A live formula that recalculates when the source changes | Static values, written once; new rows require repeating |
| Reliability | Deterministic — same input, same number, every time | Guesses patterns — verify the results by sampling |
| Best for | Auditable imports where conversions must stay traceable | One-off cleanups of a column you will verify anyway |
| Available in | Every version of Excel and Google Sheets | Excel 2013 or later; Sheets answers with Smart Fill |
They cooperate: Flash Fill extracts digits from messy cells, and a VALUE column proves each extraction converts cleanly before you trust the fill. The fill guesses, VALUE verifies.
VALUE in Google Sheets
- Same name and same single argument — every formula above pastes into Sheets unchanged.
- Sheets throws #VALUE! for the same reasons: invisible spaces and unrecognized separators.
- TRIM behaves identically, so the TRIM-then-convert combo transfers as is.
- Dates still return as serial numbers — format the result cell as a date to display them.
Limitations and practical tips
- Locale rules are the contract: "1.5" and "1,5" are two different numbers depending on where the file opens — never assume both parse.
- One value at a time: VALUE takes no ranges — fill the formula down a column, or use Text-to-Columns for one-off imports.
- Logical values fail: =VALUE(TRUE) returns #VALUE! — TRUE and FALSE are booleans, not numbers, whatever the export claimed.
- Only the locale default currency parses: "$50" in the US, "€50" elsewhere — strip symbols with SUBSTITUTE and format afterwards.
- Ambiguous strings are dangerous: "3/4" is March 4 or April 3 depending on the locale — split into explicit columns before converting.
Treat VALUE as the last step of a cleaning pipeline, not the first: strip line breaks and non-breaking spaces, trim the padding, audit lengths with LEN — only then convert. Otherwise every failed conversion lands on your desk as a #VALUE! you have to trace backwards.
Related Tools
Related Functions
Frequently Asked Questions
What does the VALUE function do in Excel?
It converts text that looks like a number into a real number: =VALUE("12345") returns 12345, ready for SUM, lookups and charts. It also parses dates, times and percentages your locale recognizes.
Why does VALUE return #VALUE!?
The text is not a number your locale recognizes — hidden spaces, currency symbols, wrong separators, or genuine words. Clean with TRIM and SUBSTITUTE first, or wrap in IFERROR to keep the rest of the sheet calculating.
What is the difference between VALUE and =A2*1 or =--A2?
For clean number text all three return the same number — they parse by the same locale rules. VALUE names the intent explicitly, which matters in shared workbooks and audits; the arithmetic tricks win on brevity in quick one-off fixes.
Can VALUE convert dates and times?
Yes — =VALUE("2026/10/15") returns the serial number 45945 and =VALUE("13:30") returns 0.5625. Format the result cell as a date or time to display it back.
Why does =VALUE("1,5") fail while =VALUE("1.5") works?
Locale: in decimal-point locales (US, UK) the dot is the decimal mark; in comma locales (Germany, Spain, Brazil) it is the reverse. Feed VALUE the format your Excel speaks, or split and rebuild the number manually.
Does VALUE handle percentages?
Yes — =VALUE("50%") returns 0.5 with percentage formatting attached in most versions. Multiply by 100 if you need the plain 50.
Does VALUE work in Google Sheets?
Yes — same name, same single argument, same #VALUE! on unrecognized text. The TRIM-then-convert cleanup transfers unchanged, and dates still return as serial numbers.
Ready to try it on your own file?
Open Convert Text to Numbers in Excel — It's Free