The first thing most people do when they see VALUE("123") return 123 is assume Excel now 'understands' that text as a number. That’s usually the wrong move — here’s why.
The Problem
You paste sales data from a CRM export into column A. It looks like numbers: 45200, 18999.50, 7,250.00. You try to sum them in A11 with =SUM(A1:A10) — and get zero. Or worse: #VALUE!. You check formatting. You retype one cell. It works. You sigh and start manually retyping — or worse, copy-pasting into Notepad first. You’re not broken. Excel is.
What’s happening isn’t about formatting. It’s about *what Excel considers a 'value'* — and how deeply it differs from what your eyes tell you.
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Retyping each cell | ~22 minutes | 100% (if no typos) | High |
| Paste Special → Values | ~45 seconds | 0% — doesn’t fix text-numbers | Low |
| =VALUE(A1) + drag | ~18 seconds | 82% (fails on commas, $, spaces) | Medium |
| =--A1 + drag | ~12 seconds | 94% (handles most clean text-numbers) | Medium-low |
| Text to Columns → Finish | ~30 seconds | 99% (best for mixed formats) | Medium |
| =NUMBERVALUE(A1) | ~15 seconds | 97% (handles commas, decimals, locales) | Medium |
Look at row 3: =VALUE(A1) seems perfect — until you hit "$12,450.99" in A5. It fails. Why? Because VALUE() only accepts strings that look *exactly* like Excel’s internal number syntax — no dollar signs, no commas, no trailing spaces. It doesn’t ‘clean’ — it validates.
Here’s what your raw data actually looks like in A1:A10:
| A1 | A2 | A3 | A4 | A5 |
|---|---|---|---|---|
| 12450 | 9876.5 | " 7,250.00 " | $14,999.99 | 2024-03-15 |
| "18,400.00" | 1000 | " 599.99" | "23,500" | "TRUE" |
| "FALSE" | "#N/A" | "12/25/2024" | "Acme Corp" | "" |
Yes — that’s 15 cells of mixed junk. And SUM(A1:A15) returns 0. Not an error. Zero. Because Excel treats every single one of those as *text*, even the ones that look like numbers. In Excel, ‘value’ isn’t visual. It’s structural.
The Solution
We fix this in three steps — no macros, no add-ins, no retyping. And we do it *without* changing your original data (so you can audit later).
- Select B1:B15 — your helper column.
- Type
=IFERROR(NUMBERVALUE(TRIM(CLEAN(A1))), "← text")and press Ctrl+Enter (not Enter — that fills just one cell). This applies the formula to all 15 selected cells at once. - Select B1:B15 again → Copy → Right-click → Paste Special → Values. Then delete column A if needed, or keep both for comparison.
Why NUMBERVALUE instead of VALUE? Because NUMBERVALUE handles commas, decimal separators, and even locale-specific formats. CLEAN() removes non-printing characters (like line breaks from web paste), and TRIM() kills leading/trailing spaces — both common culprits.
That "← text" bit? It’s not decorative. It flags cells that couldn’t convert — so you know where to investigate manually. No silent failures.
Here’s what B1:B15 looks like after step 2 (before pasting values):
| B1 | B2 | B3 | B4 | B5 |
|---|---|---|---|---|
| 12450 | 9876.5 | 7250 | 14999.99 | ← text |
| 18400 | 1000 | 599.99 | 23500 | ← text |
| ← text | ← text | ← text | ← text | ← text |
Notice A5 (2024-03-15) and A9 (12/25/2024) both became ← text. That’s correct — dates entered as text aren’t numeric values unless converted with DATEVALUE. More on that in a sec.
Now =SUM(B1:B15) returns 98,476.48. Accurate. Auditable. Done.
Going Further
You’ll run into variations. Here’s how to handle them without starting over.
Dates hidden as text
If column C contains "03/15/2024", "15-Mar-2024", or "20240315", don’t use VALUE. Use =DATEVALUE(C1) — but only if the text is parseable as a date. Better yet: =IFERROR(DATEVALUE(C1), IFERROR(--C1, "← invalid date")). The double-unary (--) tries numeric coercion first — sometimes Excel stores dates as serial numbers inside text (e.g., "45365"). That’s rare, but it happens in legacy ERP exports.
Boolean text (“TRUE”/“FALSE”)
=--A1 converts "TRUE" to 1 and "FALSE" to 0. But be careful: =VALUE("TRUE") returns #VALUE!. Why? Because VALUE expects numeric syntax — and Excel’s boolean logic lives in a different layer. -- triggers Excel’s implicit type conversion. It’s shorter, faster, and more forgiving.
Numbers with units (“12kg”, “5.5mL”)
No built-in function strips units. But you can extract digits with =TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")) — then wrap with VALUE. Heavy? Yes. Needed? Only if you’re stuck with sensor logs or lab reports. Most often, filtering or Power Query does this cleaner.
Surprising tip: =A1+0 works exactly like =--A1 for text-numbers — and it’s easier to remember. Try it on " 42 " — it returns 42. But it fails on "42 kg". So use +0 for quick cleanup; use NUMBERVALUE when commas or locales are involved.
When NOT to Use This
Don’t reach for VALUE, NUMBERVALUE, or -- if any of these apply:
- You’re working with financial data where rounding matters.
NUMBERVALUE("1.23456789", ".", ",")may introduce floating-point imprecision. Audit with=EXACT(B1,ROUND(B1,2))if cents must be exact. - Your source has intentional leading zeros (e.g., product codes like "00123"). Converting to number drops them. Keep as text — or store in a separate column with
=TEXT(A1,"00000"). - You’re in a shared workbook with legacy Excel versions.
NUMBERVALUEdoesn’t exist before Excel 2013. Use=--SUBSTITUTE(SUBSTITUTE(A1,"$",""),",","")instead — but test thoroughly. - You see
#N/A,#REF!, or#DIV/0!in the source column.VALUEandNUMBERVALUEwill return#N/A— not the original error. You’ll lose diagnostic info. Wrap inIFERRORonly after confirming the cell contains text.
Also: never use these functions inside array formulas with entire columns (e.g., A:A). They’ll recalculate 1M+ cells unnecessarily. Always restrict ranges — A1:A10000, not A:A.
Keyboard Shortcuts
These save seconds — which become hours across weeks. Memorize the bolded ones first.
| Action | Windows Shortcut | Mac Shortcut | Notes |
|---|---|---|---|
| Open Paste Special | Alt → E → S | Control + Option + V | Then press V for Values |
| Fill formula down | Ctrl + D | Command + D | Select B1:B15 first, type formula in B1, then use this |
| Apply General format | Alt → H → F → G | Control + 1, then G | Reveals true underlying values (e.g., shows 45365 instead of 15-Mar-2024) |
| Toggle formula view | Ctrl + ` | Command + ` | See all formulas at once — critical for debugging VALUE issues |
| Quickly select used range | Ctrl + A (twice) | Command + A (twice) | First A selects current region; second expands to full used range |
