Why does SUM(A1:A10) return zero when your cells clearly show '$2', '$5.75', '$12'? Why does FILTER() ignore those cells entirely? Why does Paste Special → Values paste nothing but blanks?
The answer is simple: Excel sees '$2'—not $2. That leading apostrophe (or lack of number formatting) turns your dollar amount into text. And text doesn’t add up. Text doesn’t sort correctly. Text breaks every function that expects numbers.
The Setup
You’re auditing supplier invoices for Q1. Finance sent you a raw export from their legacy system. Column A is vendor name, Column B is invoice amount—but all values are typed or pasted with a dollar sign *in front*, no formatting applied. No currency format. No number formatting. Just plain strings.
| Vendor | Amount | Date |
|---|---|---|
| Acme Corp | $2 | 2024-03-12 |
| Nexus Labs | $14.50 | 2024-03-14 |
| Stellar Dynamics | $1,240.00 | 2024-03-08 |
| Vanta Systems | $2 | 2024-03-10 |
| Orion Solutions | $75.99 | 2024-03-05 |
| TerraLink Inc | $2 | 2024-03-16 |
| Lumenix Group | $8.25 | 2024-03-07 |
| Crestline Partners | $2 | 2024-03-13 |
| Zephyr Tech | $312.00 | 2024-03-09 |
This is B2:B10 — eight rows of amounts, all prefixed with $, all stored as text. You can tell instantly: select B2, look at the formula bar. If you see $2 — not 2 — and the cell is left-aligned, it’s text. Numbers align right by default.
The Challenge
You need to calculate total spend, average invoice size, and filter vendors with amounts > $10. But =SUM(B2:B10) returns 0. =AVERAGE(B2:B10) returns #VALUE!. =FILTER(A2:A10,B2:B10>10) returns #CALC!. None of it works.
It’s not about missing decimals or commas. It’s about Excel’s type system: $2 ≠ 2. The dollar sign isn’t decorative — it’s a red flag. And worse: some entries have spaces ($ 2), others use non-breaking spaces (invisible), and one uses a Unicode dollar symbol (U+FF04) — which looks identical but breaks every formula.
You could manually retype everything. Or copy-paste into Notepad first. But you’ve got 2,300 rows next week. You need a repeatable fix — fast, clean, and bulletproof.
Walking Through It
Step 1: Confirm the problem
Click B2. Press F2 to edit. See the cursor jump to the far left? That means there’s a hidden apostrophe (') — Excel’s text override marker. Or press Ctrl+1, go to Number tab: it says “Text”. Not “Currency”, not “Number”.
Step 2: Strip the $ and convert
Select B2:B10. Press Alt+H+F+F (Home → Fill → Flash Fill). Type 2 in C2, 14.5 in C3, then press Enter. Flash Fill detects the pattern and fills C2:C10 with cleaned numbers. Done. But — this only works if all $ signs are standard ASCII and no hidden characters exist.
Step 3: The reliable method (do this instead)
In C2, enter: =VALUE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",",""))
This removes $, removes commas, then converts to number. Drag down to C10.
| B2 (raw) | C2 (formula result) |
|---|---|
| $2 | 2 |
| $14.50 | 14.5 |
| $1,240.00 | 1240 |
| $2 | 2 |
Step 4: Handle hidden junk
If VALUE() returns #VALUE! on a cell like $ 2, add TRIM():=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",","")))
Still failing? Try CLEAN() to remove non-printing chars:=VALUE(CLEAN(TRIM(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",","")))))
Surprising tip: Don’t use Find & Replace to delete $ across the whole column. If any cell contains $ inside a description (e.g., “Refund: -$2”), you’ll break it. Always operate on a new column — never overwrite raw data.
The Result
Here’s C2:C10 after applying the full cleanup formula — now fully numeric, right-aligned, usable in all formulas:
| Vendor | Clean Amount |
|---|---|
| Acme Corp | 2 |
| Nexus Labs | 14.5 |
| Stellar Dynamics | 1240 |
| Vanta Systems | 2 |
| Orion Solutions | 75.99 |
| TerraLink Inc | 2 |
| Lumenix Group | 8.25 |
| Crestline Partners | 2 |
| Zephyr Tech | 312 |
Now =SUM(C2:C10) returns 1658.74. =FILTER(A2:A10,C2:C10>10) returns Nexus Labs, Stellar Dynamics, Orion Solutions, Zephyr Tech. Everything works.
What Could Go Wrong
| Symptom | Cause | Fix |
|---|---|---|
| #VALUE! appears in C2 after pasting formula | Cell B2 contains a Unicode dollar symbol (U+FF04) or non-breaking space (U+00A0) | Use =UNICODE(LEFT(B2,1)) to check first character code. Replace with SUBSTITUTE(B2,CHAR(65508),"") if needed. |
Numbers show as dates (e.g., $2 becomes 2-Jan) | Column formatted as Date before entering data; Excel auto-converted | Re-select column → Ctrl+1 → Format → Text → OK → Re-enter formula in new column. |
| Formula works in C2 but fails in C3 onward | Mixed data types — some rows have $2.00, others $2, others $ 2; VALUE() chokes on inconsistent spacing | Wrap in IFERROR(...,0) and add TRIM() + CLEAN() — always. |
Your next move: Open your problematic sheet right now. Select the column with $2-style entries. Press Alt+H+F+F and test Flash Fill with two clean examples. If it works, accept it. If not, paste this into the first empty column’s top cell:=IFERROR(VALUE(CLEAN(TRIM(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",",""))))),0)
Drag down. Then replace your original column with values only (Ctrl+C, Alt+E+S+V). Done.
