Convert Text to Numbers in Google Sheets: What VALUE Can and Can’t Read

A number stored as text looks like a number, but most functions treat it as words. The dangerous part is that they rarely complain. In our test, three cells holding the text “1”, “2” and “3” gave 0 with SUM, and COUNTIF(range, ">0") counted none of them. This page shows how to spot those cells and which formula converts each format.

Tested in Google Sheets. On November 11, 2026 we passed 20 kinds of text to VALUE and other functions in a spreadsheet with United States settings, and copied each result or error message. How we test

How to spot numbers stored as text

  • =ISNUMBER(A2) returns FALSE for a text number. =ISNUMBER("5") returned FALSE.
  • A SUM that is too small, or 0, over cells that clearly contain numbers.
  • Text numbers are usually aligned to the left of the cell unless someone changed the alignment.

Found in testing: functions and operators don’t agree. With the text “1” and “2” in A2 and A3, =A2+A3 returned 3, because the + operator converts text, while =SUM(A2, A3) returned 0 and =N(A2) returned 0. A formula can work in one cell and silently fail in the next. We saw the same with SUM in our #VALUE! guide, where a total came out 413 instead of 515.

The basic fix: VALUE

=VALUE(A2)

For a whole column in one formula, =ARRAYFORMULA(VALUE(A2:A100)) returns a column of real numbers. In our test =SUM(ARRAYFORMULA(VALUE(A2:A4))) returned 6 where SUM alone returned 0. Shorter alternatives that also worked: =A2*1, =--A2, and for totals =SUMPRODUCT(--A2:A4), which returned 6.

To replace the original text, put the formula in a helper column, copy it, and use Edit > Paste special > Values only over the original data.

What VALUE reads, and what it doesn’t

Text =VALUE(...) Fix when it fails
$1,234.56 1234.56
1,234 1234
12% 0.12
(123) -123
1.5E3 1500
” 5 ” (normal spaces) 5
12:30 0.5208… (a time)
1 234 (space as thousands separator) #VALUE! =VALUE(SUBSTITUTE(A2, " ", ""))
5 followed by a non-breaking space #VALUE! =VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), "")))
5 kg #VALUE! =VALUE(REGEXEXTRACT(A2, "[\d.]+"))
Total: $1,234.50 #VALUE! =VALUE(REGEXEXTRACT(A2, "[\d,.]+")) returned 1234.5
€1.234,56 (European format) #VALUE! See below
−5 (typographic minus sign) #VALUE! =VALUE(SUBSTITUTE(A2, UNICHAR(8722), "-"))
5- (trailing minus) #VALUE! =VALUE(REGEXREPLACE(A2, "^(.*)-$", "-$1"))
+5 #VALUE! =VALUE(SUBSTITUTE(A2, "+", ""))
TRUE #VALUE! =A2="TRUE" gives a real TRUE or FALSE

Every failure showed the same message pattern, for example VALUE parameter ‘1 234’ cannot be parsed to number. The text between the quotes is what VALUE received, which helps you see the problem character.

Found in testing: two results were wrong without any error. =VALUE("1/2") returned 46024, which is the date January 2, 2026, not 0.5. =VALUE("3-4") returned 46085, March 4, 2026. VALUE reads anything that looks like a date as a date in the current year. And =VALUE("") returned 0, so empty cells become zeros and lower any average you take.

European format: 1.234,56

In a spreadsheet with United States settings, the dot is the decimal separator and the comma groups thousands, so VALUE can’t read 1.234,56. Remove the currency sign and the dots, then turn the comma into a dot:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "€", ""), ".", ""), ",", "."))

It returned 1234.56. If all your data uses that format, another option is to change the spreadsheet locale in File > Settings so the file reads commas as decimals. That changes how every formula in the file is written, so read our note on commas and semicolons in formulas first.

TO_PURE_NUMBER doesn’t convert text

The name suggests otherwise, but =TO_PURE_NUMBER("$12") returned the text “$12” unchanged, and =TO_PURE_NUMBER("50%") returned “50%”. It removes the formatting from a real number (=TO_PURE_NUMBER(10%) returned 0.1), not the symbols from text. Use VALUE for text.

Where text numbers come from

  • Text functions. LEFT, MID, RIGHT, REGEXEXTRACT and SUBSTITUTE always return text, even when the result is “123”. Wrap them in VALUE. See extracting text before or after a character.
  • Pasted data with hidden spaces, especially non-breaking spaces from web pages. See removing extra spaces.
  • Imports from CSV files or other apps that use a different number format.

The reverse problem exists too: SPLIT turns codes like “05” into the number 5 and loses the leading zero.

Related guides