VALUE Function in Excel, Google Sheets, Airtable & Notion
VALUE converts text that looks like a number into an actual number you can calculate with. VALUE("$1,234.50") is 1234.5.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | VALUE(text)For other number formats use NUMBERVALUE(text, decimal_separator, group_separator), e.g. for “1.234,50”. |
| Google Sheets | ✔ Built in | VALUE(text) |
| Airtable | ✔ Built in | VALUE(string)Strips currency symbols and thousands separators. Percent signs and some minus signs may not convert the way Excel does, so the tester removes % and divides by 100 itself. |
| Notion | ✔ Built in | toNumber(text)Expects a plain number. Strip symbols first: toNumber(replaceAll(prop("Price"), "[^0-9.-]", "")). Returns empty when the text is not a number, instead of an error. |
| Excel | =VALUE("$1,234.50") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =VALUE("$1,234.50") | open sheets.new ↗ |
| Airtable | VALUE("$1,234.50") | |
| Notion | toNumber(replaceAll("$1,234.50", "[^0-9.-]", "")) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
The tester follows Excel in the US locale: $ and commas are allowed, (250) is −250, and dates in YYYY-MM-DD form become serial numbers. The Airtable and Notion versions handle plain numbers, currency and percentages, but not dates or times.
Arguments
| Argument | What it means |
|---|---|
text | Text containing a number, currency amount, percentage, date or time. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=VALUE("$1,234.50") | 1234.5 | Currency symbol and commas are accepted. | |
=VALUE("12%") | 0.12 | Percentages become fractions. | |
=VALUE("12:30") | 0.5208333 | Times become fractions of a day. | |
=VALUE("abc") | #VALUE! | Not a number. |
Examples use Excel / Google Sheets syntax unless noted.
How VALUE works
Numbers stored as text are a common reason a spreadsheet adds up wrong: they come from CSV imports, copied web tables and functions like LEFT, MID and SPLIT, which always return text. SUM silently skips them and lookups fail to match the number 42 against the text “42”. VALUE converts them. =A2*1 and =--A2 do the same in a shorter form.
VALUE follows your spreadsheet locale. In a US locale “1,234.50” works but “1.234,50” returns #VALUE!; use NUMBERVALUE(A2, ",", ".") in Excel to say which separators the text uses. Dates and times written as text are converted to their serial numbers, so format the result as a date to see it as one.
Airtable's VALUE is more forgiving with currency symbols and separators, but check percentages and negative amounts in your own data. Notion's toNumber() expects a clean number, so strip everything except digits, the decimal point and the minus sign with replaceAll() first.
Frequently Asked Questions
- How do I convert a whole column of text numbers?
- Either use
=VALUE(A2)in a helper column and paste the results back as values, or select the cells and use Excel's “Convert to Number” warning menu or Data → Text to Columns → Finish. - Why does VALUE return #VALUE! for a number with spaces?
- Spaces between digits (for example “1 234”) and non-breaking spaces aren't allowed. Remove them first:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), CHAR(160), "")).