It’s 3:12 PM. You just pasted a column of sales figures from your CRM into Excel—and suddenly VLOOKUP returns #N/A. You check the cells: they look like numbers. You format them as Currency. Still fails. You type =ISNUMBER(A2) and get FALSE—even though A2 clearly shows $42,850. That’s when it hits you: Excel doesn’t see what you see.
Quick Answer
In Excel, "value" means any data that can be used in calculations or logical tests—numbers, dates, text strings, logicals (TRUE/FALSE), and even errors—but only if Excel recognizes them as such. A cell showing "12/05/2024" might be text (not a date value); "$42,850" might be text (not a number); "TRUE" typed manually might be text—not the logical value TRUE. Excel’s internal type matters more than appearance.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| =VALUE() | Converts text that looks like a number/date into an actual numeric or date value | Fixing imported numbers stored as text (e.g., "123" → 123) | Fails on non-numeric text ("abc"), commas in thousands ("1,234" unless system locale matches), or time-only text without date |
| Paste Special > Add | Copy a blank cell → select target range → Paste Special → Operation: Add | Bulk-converting text-to-numbers without formulas or editing original data | Overwrites formulas with static values; breaks links; doesn’t handle dates or logicals |
| Text to Columns | Select column → Data tab → Text to Columns → Delimited/ Fixed width → Finish (or set column data type) | Cleaning mixed-format imports (e.g., "$24,500.00", "2024-03-15", "Yes") | Requires manual column-type selection per segment; overwrites adjacent cells if not careful |
| Error-checking green triangle + Convert to Number | Click warning icon → “Convert to Number” | Single-cell or small-range fixes when Excel detects text-that-looks-like-a-number | Only appears for obvious cases (no $, no commas, no leading zeros); invisible for most real-world data |
| -- (double unary) | Prefix text with --, e.g., =--A2 (forces numeric coercion) | Array formulas, SUMPRODUCT, or dynamic arrays where VALUE() would break compatibility | Returns #VALUE! on truly non-numeric text; fails on empty strings or spaces; unreadable to beginners |
| Custom Number Format trick | Format cells as General → type 0 → press Enter → copy down | Revealing hidden text-vs-value status without changing content | Doesn’t convert—just exposes the underlying type; requires manual entry per cell |
Method 1 Deep Dive
The =VALUE() function seems simple. But its behavior is tightly bound to your Windows regional settings—not Excel’s language or your spreadsheet locale.
Try this with real data:
| A1 | B1 | C1 |
|---|---|---|
| "12/05/2024" | "42,850" | "TRUE" |
| =VALUE(A1) | =VALUE(B1) | =VALUE(C1) |
| 45,265 (serial number for Dec 5, 2024) | #VALUE! | TRUE (logical, not text) |
Why does B1 fail? Because =VALUE() rejects commas in number strings unless your Windows short date separator is "/" and your list separator is "," and your thousands separator is "" (blank). On most US systems, it fails. On German systems (where comma = decimal), it fails differently.
Fix B1: use =SUBSTITUTE(B1, ",", "")+0 instead. Or better—use =NUMBERVALUE(B1) (available Excel 2013+). It respects your system’s number format rules. Try it: =NUMBERVALUE("42,850") returns 42850. =NUMBERVALUE("42.850", ".", ",") returns 42850 in German locale.
Surprising tip: =VALUE() converts "TRUE" and "FALSE" to 1 and 0—but only if typed in lowercase or proper case. "true" works. "True" works. "TRUE" works. But "tRuE" fails. Case sensitivity is inconsistent and undocumented.
Method 2 Deep Dive
Paste Special > Add is the fastest bulk fix—and the most dangerous if misused.
Here’s exactly what to do:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Type 0 in any empty cell (e.g., Z1) | Cell contains literal zero | None |
| 2 | Copy Z1 (Ctrl+C) | Zero is on clipboard | Ctrl+C |
| 3 | Select your text-number range (e.g., A2:A25) | Range highlighted | Shift+↓ (from A2) |
| 4 | Right-click → Paste Special → Operation: Add → OK | Each text number becomes a true number (e.g., "123" → 123) | Alt+E+S+V (then Tab twice, Space, Enter) |
This works because adding zero to text-that-represents-a-number forces Excel to coerce it. It’s faster than dragging =VALUE() down 10,000 rows.
But here’s the trap: if your range includes a formula like =B2*C2, Paste Special > Add replaces it with a static number. No warning. No undo after save. Always check for formulas first. Use =CELL("type",A2) to test: "v" = value, "f" = formula, "l" = label (text).
Real example: Sarah Chen at Acme Corp pasted Q1 sales from Salesforce. Column D had "14250.00", "18999.50", "21040.75" — all text. She ran Paste Special > Add. Instant fix. But column E held =D2*1.15 (markup). After Paste Special, E2 became 16387.5 — no longer linked to D2. She lost traceability.
Cheat Sheet
| Task | Formula / Action | Shortcut | Test First |
|---|---|---|---|
| Check if cell is a true number | =ISNUMBER(A2) | None | =CELL("type",A2) returns "v" |
| Convert single text-number | =VALUE(A2) or =--A2 | F2 → edit → add -- → Enter | =LEN(A2)=LEN(TRIM(A2)) (no trailing spaces) |
| Bulk convert text-to-number | Copy 0 → select range → Alt+E+S+V → Tab×2 → Space → Enter | Alt+E+S+V | Filter for green triangles (error indicators) |
| Force number from messy text | =NUMBERVALUE(SUBSTITUTE(A2,"$","")) | None | =EXACT(A2,TRIM(A2)) (confirms no hidden spaces) |
| See raw underlying value | Select cell → press F2 → look at formula bar | F2 | =TYPE(A2) → 1=number, 2=text, 4=logical, 16=error |
| Prevent future text-numbers | Data tab → Get Data → From Text/CSV → Set column type during import | None | Always preview data types before loading |
