How to Convert Text to Numbers in Google Sheets
Sometimes numbers in Google Sheets look normal but behave like text: SUM returns 0, sorting goes 1, 10, 2, and formulas show #VALUE!. This usually happens with imported or pasted data. Here's how to spot and fix it.
How to spot numbers stored as text
- They're aligned to the left of the cell (real numbers align right by default).
- The quick total in the bottom-right corner shows only a Count, not a Sum.
=ISNUMBER(A2)returns FALSE.
When we selected A2:A5, the quick total in the bottom-right corner showed only Count: 4, with no Sum.
Fix 1: change the format
If the cells were formatted as Plain text, select them and choose Format → Number → Automatic (or Number). Values typed afterwards are stored as numbers. Values that are already text may need to be re-entered: copy them and paste back with Edit → Paste special → Values only.
Fix 2: the VALUE function
=VALUE(A2)
VALUE turns text that looks like a number into a real number. Fill it down a helper column, then copy the results and paste them over the original as values.
Fix 3: multiply by 1
=A2*1
Any maths operation forces Sheets to convert the text. It's a quick alternative to VALUE, and it works inside other formulas: in our test, =SUMPRODUCT(A2:A5*1) returned 500 from the text column.
Numbers with symbols, spaces or commas
VALUE is more forgiving than you might expect. In our test (a spreadsheet set to United States), it converted all of these on its own: " 120 " with spaces, "€120", "$120" and "1,200" with a thousands separator.
It failed with #VALUE! on these, which need cleaning first:
| Problem | Formula that worked |
|---|---|
| A word or unit, e.g. "120 kg" | =VALUE(SUBSTITUTE(A2, " kg", "")) |
| A currency code, e.g. "USD 120" | =VALUE(SUBSTITUTE(A2, "USD ", "")) |
| A non-breaking space (common in data pasted from websites) | =VALUE(SUBSTITUTE(A2, CHAR(160), "")) |
| A decimal comma, e.g. "12,5", in a spreadsheet that uses a decimal point | =VALUE(SUBSTITUTE(A2, ",", ".")), or change the locale (see below) |
Is your SUM returning 0? That's the most common symptom of numbers stored as text. See SUM not working in Google Sheets for a quick check and a one-formula fix.
Frequently asked questions
Why does a number start with an apostrophe?
Typing '120 tells Sheets to store it as text. Remove the apostrophe by retyping the value without it.
Can decimal commas cause this?
Yes. If your data uses 12,5 but the spreadsheet locale expects 12.5, Sheets reads it as text: in our test, =VALUE("12,5") returned #VALUE!. Change the locale in File → Settings or replace the commas.