Convert text to numbers with VALUE
VALUE converts text that looks like a number into a real number, and TRIM first removes stray spaces that would make it fail.
=VALUE(TRIM(A2))The sheet: A CSV export left amounts such as "1250.50" in column A as text. They are left aligned and SUM ignores them.
How it works, step by step
- 01TRIM(A2) removes leading, trailing and doubled spaces.
- 02VALUE reads the remaining text as a number.
- 03The result is numeric and can be summed, averaged and formatted.
The same logic in four tools
=VALUE(TRIM(A2))=VALUE(TRIM(A2))SELECT CAST(TRIM(amount_text) AS numeric) AS amount
FROM raw_import;df["amount"] = pd.to_numeric(df["amount_text"].str.strip(), errors="coerce")Mistakes that break it
- !Text that is not a number returns #VALUE!. In pandas errors="coerce" turns it into NaN, and the SQL CAST raises an error instead.
- !A non-breaking space from a web copy is not removed by TRIM. Replace it first with SUBSTITUTE(A2,CHAR(160),"").
- !Decimal and thousands separators depend on locale. 1.250,50 may not convert on a US setup.
Common questions
- Is there a shorter way?
- =--A2 or =A2*1 also convert numeric text, but VALUE makes the intent clear.
- Will it keep leading zeros?
- No. Numbers do not hold leading zeros, so keep IDs as text.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.