SheetSense

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.

fx
=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

  1. 01TRIM(A2) removes leading, trailing and doubled spaces.
  2. 02VALUE reads the remaining text as a number.
  3. 03The result is numeric and can be summed, averaged and formatted.

The same logic in four tools

Excel
=VALUE(TRIM(A2))
Google Sheets
=VALUE(TRIM(A2))
SQL
SELECT CAST(TRIM(amount_text) AS numeric) AS amount
FROM raw_import;
Python (pandas)
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.