VALUE
Turn a number that arrived as text into a real number.
Turn a figure that arrived as text into a real number
The number the text was spelling
Syntax
=VALUE(text)
| Argument | Required | What goes here |
|---|---|---|
text | Required | The cell holding the figure that came in as text. |
How to use VALUE
VALUE converts text that looks like a number into a number that behaves like one.
Two things produce the text in the first place: an export that quoted its numeric columns, and your own formula, because everything LEFT, MID and RIGHT hand back is text even when every character is a digit.
The second case is the one that catches people out. A code pulled out of a reference will not match a numeric account column until VALUE has been round it, and the lookup fails with both sides looking identical on screen.
Convert once, at the point the value is extracted, rather than at the point it fails.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | Supplier name | Amount | Posted |
| 2 | GL-6100-8801 | brightwell paper | 1500 | Y |
| 3 | GL-6200-8802 | calder UTILITIES | 320 | y |
| 4 | GL-6100-8803 | meridian freight ltd | 2750 | |
| 5 | GL-6300-8804 | ashgrove catering | 184 | Y |
| 6 | GL-6200-8805 | THORNBURY print | 760 | |
| 7 | GL-6100-8806 | Brightwell Paper | 1120 | Y |
| Formula | Result |
|---|---|
=VALUE(C2)Text in, number out. It now right-aligns and totals. | 1500 |
=SUM(C2:C7)The column before anyone converts it. Every row is ignored and the total is nought. | 0 |
=VALUE(C2)+VALUE(C3)Converted, the same two figures add up. | 1820 |
=VALUE(MID(A2,4,4))The account code out of the reference, converted so it can be matched against a numeric account column. | 6100 |
Notes
- VALUE errors on text that is not a number, which is how you find the one row in a column that holds a note instead of a figure.
- A stray space or currency symbol can defeat it. TRIM first if the source is an import.
Related
A reference tells you what it does. A unit makes you use it.