VALUE
Turns text that looks like a number into a real number so you can calculate with it: prices from exports, digits from codes.
VALUE(text)- text
- Text with a number: a cell or the result of another function.
Return value
The number from text that looks like a number: "12.5" becomes 12.5. If the text can't be read as a number, you get #VALUE!.
Examples
Prices from an export
fx
A
B
C
1
2
3
4
5
6
7
Price from export
Price, $
1290 USD
5990 USD
490 USD
12500 USD
Sheet1
Details
Sometimes a number in a cell is stored as text: it sits on the left edge, and SUM skips it. VALUE turns such text into a number.
VALUE is most often wrapped around other text functions. LEFT, MID and SUBSTITUTE always return text, and after VALUE you can calculate with the result.
If the text still has extra characters, for example "USD", VALUE returns #VALUE!. Remove them first with SUBSTITUTE.
Common mistakes
- The text still has letters or extra spaces, and VALUE returns #VALUE!. Remove them with SUBSTITUTE or TRIM.
- VALUE was forgotten, and the digits you cut out stayed text: SUM gives 0, and comparing with a number gives FALSE.