The VALUE function
Converts text that looks like a number, such as "1,250" or "15%", into a real number you can calculate with.
Syntax
VALUE(text)- text
- The text to convert, or a cell that holds it. Numbers, percentages, dates and times written as text all work.
Examples
C1=VALUE(A2)
| A | B | C | |
|---|---|---|---|
| 1 | Amount as text | 1250 | |
| 2 | 1,250 |
B1=VALUE("15%")
| A | B | |
|---|---|---|
| 1 | 0.15 |
B1=VALUE(MID("SKU-0042",5,4))
| A | B | |
|---|---|---|
| 1 | 42 |
Questions
Why does VALUE return
The text is not a number Excel can read, for example because it holds letters or a stray space, or uses a decimal separator your Excel does not use. Clean it first with TRIM or SUBSTITUTE.
Is there a quicker way to turn text into numbers?
Any math on the text converts it, so =A2*1 and =--A2 do the same as VALUE. For a whole column, Data > Text to Columns > Finish converts in place.