VALUE
VALUE(text)
The number a piece of text is, such as a crew count kept in a text attribute ("07") or a floor number cut out of a name. Text never turns into a number by itself, so [Crews] <= 14 on a text attribute is refused; VALUE([Crews]) <= 14 compares the number.
Arguments
| Argument | What to give it |
|---|---|
| text | Text that is a number written plainly: an optional sign, digits and an optional decimal point, with spaces around it if you like: "07", "-2.5", ".5". Blank gives blank. |
Anything else is blank, with the reason "not a number", so a guardrail skips the row rather than reading a wrong number:
| Text | VALUE |
|---|---|
"07" | 7 |
" -2.5 " | -2.5 |
"Level 07" | blank: cut the number out first, VALUE(TEXTAFTER([Name], "Level ")) |
"1,200" | blank: see below |
"2E5", "50%" | blank: no exponents, no percent signs |
No thousands separators. "1,200" is twelve hundred in Houston and one and a fifth in Bécancour, so VALUE never guesses. Where you know your commas separate thousands, take them out first: VALUE(SUBSTITUTE([Quantity], ",", "")).
Examples
Formula=VALUE("07")
=VALUE(TEXTAFTER("Level 07", "Level ")) >= 5
As a guardrail on a floor: TRUE from the fifth floor up. On a schedule, read the floor out of the WBS or the name: VALUE(TEXTAFTER([WBS], "Level ")).
=VALUE(TEXTBEFORE("2.010", "."))
The phase at the front of a code like 2.010: 2, and 10 for 10.020, so phases compare as numbers ("10" sorts before "9" as text).
Formula=VALUE(SUBSTITUTE("1,200", ",", ""))
1200: the separator taken out, where you know it is one.
Reach for something else when
| You want | Use |
|---|---|
| A number shown as text | TEXT |
| The digits out of a longer text | TEXTAFTER or TEXTBEFORE, then VALUE |
| A blank read as 0 | COALESCE(VALUE([Crews]), 0) |
Common questions
Why is VALUE of a number refused?
It is already a number; use it as it is.
The editor refuses this=VALUE([% complete])