VALUE
Turn a number that is stored as text into an actual number, so arithmetic and lookups work on it.
Syntax
VALUE(text)| Argument | Required | Description |
|---|---|---|
text | Required | Text that represents a number. Anything that is not numeric returns an error. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=VALUE("102") | 102 | The result is a number, not text — ISNUMBER on it returns TRUE. |
=VALUE("12.5") | 12.50 | Shown to two decimal places, as a number in a cell is. |
=VALUE(" 7 ") | 7 | Surrounding spaces are tolerated, so TRIM is not needed first. |
=VALUE("1,234") | 1234 | A thousands separator is understood. |
=VALUE("abc") | #VALUE! | Not numeric, so there is nothing to convert. |
Numbers stored as text
A column imported from another system frequently arrives as text even though
every entry looks like a number. Sorting puts 10 before 9, SUM ignores it,
and comparisons behave strangely — all because the cells hold characters rather
than quantities.
VALUE converts one to the other:
=VALUE("102") → 102, a real numberISNUMBER is the quickest way to find out whether you have this problem:
ISNUMBER(A2) returning FALSE on something that looks like a number is the
whole diagnosis.
Converting the other way
TEXT goes from number to text. In VisiGrid it reliably converts the type,
which is what matters for making both sides of a comparison agree — but its
format codes are only partly implemented, so do not rely on it for thousands
separators, currency, leading zeros or dates yet.
You may not need it here
Most spreadsheets treat text "102" and the number 102 as different, which is
a top cause of failed lookups. VisiGrid’s lookup functions are currently more
forgiving than that — VLOOKUP, HLOOKUP, MATCH, COUNTIF and SUMIF match
across the two, while XLOOKUP does not.
That inconsistency is ours and is being resolved. Converting with VALUE so
both sides are genuinely the same type is the approach that works today, keeps
working whichever way it lands, and is also what Excel and Google Sheets
require. The detail is here.
Excel compatibility
Matches Excel, including tolerating surrounding whitespace and thousands separators.