The VALUE function converts a value entered in text format into a number.
This function is often used to process data imported from other systems, where numbers may be stored as text.
Syntax
VALUE(text)
Argument |
Description |
Permitted values |
|---|---|---|
text |
Text to be converted to a number |
Text string or reference to a cell containing text |
Examples of use
Basic conversion from a cell
Converting a number written as text in cell A1 to a number format:
=VALUE(A1)
If cell A1 contains the text string “123”, the function will return the number 123.
Calculating the age from the ID
In some document IDs, the first digits may indicate the year.
=YEAR(TODAY()) - VALUE(LEFT("19971234567", 4))
This function will select the first 4 characters of the identifier, convert them to a number, and subtract them from the year of today's date.
Result: 28.
Use in combination with the FIND function.
If cell A1 contains the text “5 kg”, the formula will find the position of the space, select all characters to the left of it (“5”) and convert them to a number. Then it will add 5.
=VALUE(LEFT(A1, FIND(" ", A1) - 1)) + 5
Result: 10.
Notes
–If the text argument cannot be recognized as a number, the function will return the #VALUE! error.
–Make sure that the number format in the submitted text matches the regional settings (for example, the decimal separator is a comma or a period).