How to Convert Text to Numbers in Google Sheets With VALUE
=VALUE(text). If the text cannot be converted, the function shows an error.
Why numbers turn into text
Imported data, copied web tables and formulas such as REGEXEXTRACT often return numbers as text. They look right but SUM ignores them, they sort in the wrong order and a lookup cannot match them. VALUE converts them into real numbers.
VALUE syntax
=VALUE(text)
The one argument is the string containing the value to convert. Google's help page notes that the result is a plain number even when the text is read as a date, and that an error appears if the text cannot be converted.
A step-by-step example
Cell A2 holds the text 123 (stored as text).
| A | |
|---|---|
| 1 | Text |
| 2 | 123 |
=VALUE(A2)
This returns the number 123. You can now add it, compare it and use it in a lookup. As a check, ISNUMBER returns FALSE on the text and TRUE on the result.
Dates become serial numbers
Sheets stores dates as numbers. Per the help page, =VALUE("01/01/2012") displays as 40909 by default. VALUE also accepts times, such as VALUE("12:00:00"). Format the cell as a date if you want to see a date again.
Common mistakes
- Text that is not a number. VALUE("abc") gives an error. Clean the text first, for example with TRIM.
- Forgetting the quotation marks around typed text.
- Expecting a date back. The result is a plain number.
Try it yourself
Use the cell above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Text to number
Write a formula that converts the text in A2 into a number.
Exercise 2: Date as a number
Write a formula that converts the typed text 01/01/2012 into its serial number.
What to learn next
- ISNUMBER: check whether a value is a number.
- REGEXEXTRACT: pull text out with a pattern.
- TEXT: format numbers and dates as text.
Syntax and rules in this guide were checked against Google's VALUE help page. See all guides.