VALUE converts recognizable numeric text into a number in Google Sheets. It can also interpret supported percentage, date, and time strings.
Interpretation depends on the spreadsheet locale. These examples were tested with the United States locale, where a comma is a thousands separator and a period is the decimal separator.
VALUE function syntax
=VALUE(text)- text: a numeric, date, or time string, or a reference containing one.
The Google VALUE reference describes the function arguments.
Example data
Before entering the values in B2:B7, select that range and choose Format > Number > Plain text. This keeps the imported-looking entries as text for the conversion examples.
Enter this dataset starting in A1. Leave cells marked “leave empty” empty; type any displayed formula as a formula. Keep a separate blank area for the results.
| Type | Text |
|---|---|
| Plain | 1234.56 |
| Currency | $1,234.56 |
| Percent | 7.5% |
| Date | 2026-09-15 |
| Time | 12:00 |
| Invalid | Pending |
The examples below use this dataset unless the formula supplies its own values. Array results need enough empty cells to expand. The formulas use commas as argument separators. Your spreadsheet locale may require semicolons.
Convert numeric text to a number
B2 contains text, not a numeric entry. VALUE returns the number 1234.56.
=VALUE(B2)Result: 1234.56
ISNUMBER confirms that the converted result is numeric.
=ISNUMBER(VALUE(B2))Result: TRUE
Convert currency and percentage text
In the tested US locale, VALUE accepts $1,234.56 directly.
=VALUE(B3)Result: 1234.56
The percentage string 7.5% becomes 0.075. Apply percent formatting if you want it displayed as 7.5%.
=VALUE(B4)Result: 0.075
Convert a date or time string
The date becomes serial number 46280. Format the result as a date to display September 15, 2026.
=VALUE(B5)Result: 46280
Noon becomes 0.5 because Sheets stores time as a fraction of a day.
=VALUE(B6)Result: 0.5
Remove a known text prefix
SUBSTITUTE removes the known USD prefix before VALUE parses the remaining number. This is appropriate only when that exact prefix is expected.
=VALUE(SUBSTITUTE("USD 1,234.56","USD ",""))Result: 1234.56
Convert several text values
ARRAYFORMULA converts B2:B6 into numeric results. Apply date, time, or percentage formatting separately where appropriate.
=ARRAYFORMULA(VALUE(B2:B6))| 1234.56 |
| 1234.56 |
| 0.075 |
| 46280 |
| 0.5 |
Check invalid text and separators
Pending is not a recognizable numeric value and returns #VALUE!.
=VALUE(B7)Result: #VALUE!
The US locale interprets 1,234 as one thousand two hundred thirty-four.
=VALUE("1,234")Result: 1234
Other Google Sheets articles you may also like