Why does Excel treat '123' as text even when you type it directly? Why does SUM(A1:A10) return zero when all cells look like numbers? Why does Paste Special > Values fail to fix it?
The answer is almost always the same: Excel sees leading apostrophes, invisible characters, or number-stored-as-text formatting—and silently ignores them in calculations. You’re not doing anything wrong. Excel is just lying to you.
Quick Answer
Numbers stored as text in Excel won’t calculate, sort correctly, or respond to numeric functions. Fix them by using VALUE(), Paste Special > Add, Text to Columns, or the error-checking triangle—never retype manually. The fastest reliable fix is Alt + E + S + E (Paste Special > Add 0), then press Enter twice.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| VALUE() function | Enter =VALUE(A1) in adjacent cell, drag down, copy → Paste Values over originals | Small batches; clear audit trail | Fails on non-numeric text (e.g., "USD 123") |
| Paste Special > Add | Type 0 in blank cell → Copy → Select number-as-text range → Alt+E+S+E → Enter | Large ranges; preserves formatting | Changes original values permanently; no undo after paste |
| Text to Columns | Select range → Data tab → Text to Columns → Delimited → Next → Next → Finish | Mixed data (e.g., "€45,200" or "12/03/2024") | Overwrites adjacent columns if space isn’t empty |
| Error-checking triangle | Click warning icon → "Convert to Number" | Single cells or small selections with visible green triangle | Only appears if Excel detects *obvious* text-numbers; misses many cases |
| TRIM + SUBSTITUTE + VALUE | =VALUE(SUBSTITUTE(TRIM(A1),CHAR(160)," ")) — handles non-breaking spaces | Data imported from web or PDFs (common CHAR(160) issue) | Requires formula column; more complex to audit |
| Power Query | Get Data → From Table/Range → Transform → Change Type → Whole Number/Decimal | Repeatable workflows; large datasets; refreshable | Steeper learning curve; not available in Excel Starter or older versions |
Method 1 Deep Dive
Let’s fix this dataset in A1:B10:
| Sales Rep | Revenue (text) |
|---|---|
| Sarah Chen | '12450 |
| Miguel Ruiz | '9870 |
| Aisha Patel | '15600 |
| James Wong | '8920 |
| Lena Dubois | '11300 |
| Takashi Sato | '7450 |
| Fatima Nkosi | '13200 |
| Diego Morales | '10950 |
| Yuki Tanaka | '9180 |
| Elena Petrova | '14600 |
The apostrophe in each B-column value forces text mode. Do this: In C1, type =VALUE(B1). Drag down to C10. Now select C1:C10 → Ctrl+C → right-click B1 → Paste Special → Values (or press Alt+E+S+V). Done. B1:B10 now calculates correctly. Test it: =SUM(B1:B10) returns $123,750—not zero.
Counterintuitive tip: Don’t use Find & Replace to remove apostrophes. Excel hides them—you can’t search for them directly. That’s why VALUE() or Paste Special works and manual editing doesn’t.
Method 2 Deep Dive
Paste Special > Add is faster for big sheets. Try it on B1:B10 above:
Type 0 in cell D1 → Ctrl+C. Select B1:B10. Press Alt+E+S+E. Hit Enter. That’s it. Excel adds zero to each cell—forcing conversion to number without changing the value.
This method preserves cell formatting (bold, borders, fill color) and doesn’t require helper columns. But be careful: once pasted, you can’t undo individual cells. Save first. Also, if any cell contains pure text like "N/A", it returns #VALUE!—so scan for errors with =ISNUMBER(B1) before and after.
Sample result after Paste Special > Add:
| Sales Rep | Revenue (now numeric) |
|---|---|
| Sarah Chen | 12450 |
| Miguel Ruiz | 9870 |
| Aisha Patel | 15600 |
| James Wong | 8920 |
| Lena Dubois | 11300 |
| Takashi Sato | 7450 |
| Fatima Nkosi | 13200 |
| Diego Morales | 10950 |
| Yuki Tanaka | 9180 |
| Elena Petrova | 14600 |
Note how the left-aligned numbers in the first table become right-aligned in the second—that’s your visual confirmation they’re numeric.
Cheat Sheet
| Action | Shortcut / Formula | When to Use It |
|---|---|---|
| Convert one cell | =VALUE(A1) | You need traceability or want to keep original intact |
| Convert entire column (fastest) | Type 0 → Copy → Select range → Alt+E+S+E → Enter | Bulk cleanup; no formulas needed |
| Strip non-breaking spaces | =VALUE(SUBSTITUTE(TRIM(A1),CHAR(160)," ")) | Data pasted from websites or SAP exports |
| Auto-detect & convert | Click green triangle → "Convert to Number" | Small manual edits; quick validation |
| Prevent future issues | Format column as Number *before* pasting | When importing CSV or copying from email |
| Check status | =ISNUMBER(A1) → TRUE = good, FALSE = text | Audit existing sheets before reporting |
