The VALUE function
A number stored as text becomes a number again. Handy inside a formula; for a whole column, there are simpler ways.
Syntax
=VALUE(text)
In a French Excel: =CNUM(texte).
Arguments
- text: the text to convert, in your Excel's number format.
An example
=VALUE(SUBSTITUTE(A2,"$",""))
Removes the dollar sign, then converts "$1,234.50" to 1234.5.
Pitfalls
- The other country's format: "1.234,50" in an English Excel returns #VALUE!; NUMBERVALUE sets the separators.
- A trailing sign (1,234.56-) is not read.
Going further
- Convert text to numbers: a whole column, every format.
- Guide: convert text to numbers.
Frequently asked questions
- Simpler than VALUE for a column?
- Multiplying by 1 (
=A2*1) also works, with the same limits. On a whole file, the conversion tool does the rest.