The formula
Remove $ and commasExcelGoogle Sheets
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))In C2 → 1,250.5
Trim and convertExcelGoogle Sheets
=--TRIM(A3)In C3 → 840
Percent textExcelGoogle Sheets
=VALUE(A4)In C4 → 0.12
Check: is it a number?ExcelGoogle Sheets
=ISNUMBER(A3)In D3 → FALSE
✓ Tested. All 4 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | |||
| 2 | $1,250.50 | 1,250.5 | ||
| 3 | 840 | 840 | FALSE | |
| 4 | 12% | 0.12 |
How it works
- SUBSTITUTE(…,"$","")
- Removes the currency symbol; nested again to remove commas.
- VALUE(…)
- Converts number-looking text to a number.
- --
- Double negation: a shorter way to convert.
Common errors and fixes
Why does the formula return #VALUE!?
Other characters remain (e.g. "USD", non-breaking space). See TRIM not working.
Wrong value in European formats?
"1.250,50" depends on your locale's decimal separator. Use NUMBERVALUE(A2,",",".") in Excel.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.