How to convert currency text like "$1,234.50" to a number
✓ Verified in LibreOffice 25.8.7.3Clean imported/pasted money values — strip the symbol and thousands separators so math works.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) | Add more SUBSTITUTE layers for other symbols (€, spaces, currency codes). |
| Google Sheets | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) | Identical. |
| LibreOffice Calc | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) | Identical. |
How it works
The inner SUBSTITUTE removes the $, the outer one removes the thousands commas, and VALUE parses what's left into a real number: "$1,234.50" → 1234.5. Text-numbers are the classic import bug — they sort alphabetically and sum to zero. Watch the locale trap: European-formatted text like "1.234,50" needs the separators swapped the other way. In Sheets, REGEXREPLACE(A2,"[^0-9.]","") clears everything non-numeric in one pass.
Verified, not just documented
We ran =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 1234.5 — exactly the expected result. Every formula here is confirmed by actually executing it.