What is the Excel VALUE Function?
The Excel VALUE function converts text that appears in a recognize format (it could be a number, date or time, according to the current regional setting format) to a number.
VALUE Syntax
VALUE(text)
text, text value to convert to a number.
How to Use VALUE Function in Excel
For example, there is data as shown below. Column A contains several texts, appear as numbers, dates and even hours. What is the result of the VALUE function in column B?
VALUE Function #1
=VALUE(A2)
Nothing special, cell A2 contains alphanumeric without any sign.
The result is 1234.
VALUE Function #2
=VALUE(A3)
Cell A3 contains numbers with thousands separators and decimal separators that correspond to the current regional setting (United State Format).
The result is 1234.45.
VALUE Function #3
=VALUE(A4)
The result is a #VALUE! error. Why? Thousands separators with a period may be used in several countries, but the current regional setting is a united state format, the thousands separator is a comma, applies to decimal separators too.
Use Excel NUMBERVALUE function to convert text with thousands separators or decimal separators that do not match the current regional setting.
VALUE Function #4
=VALUE(A5)
In addition to the thousands separator and decimal separator that must match. If the currency symbol matches the current regional setting, the VALUE function returns no error.
The result is 123.25
VALUE Function #5
=VALUE(A5)
Not only converts text that appears as a number. The VALUE function able to convert text that appears as a time.
The result is 0.5
VALUE Function #6
=VALUE(A6)
The VALUE function able to convert text that appears as a date, with one condition, the text format is by the current regional setting.
The result is 36526
Why the conversion result for text that appears as date and time is a number?
Excel stores data date and time in the form of numbers. For a more detailed explanation, read the following article:
VALUE Function #7
=VALUE(A6)
The result is a #VALUE! Error because the dd/mm/yyyy format is unrecognized in United States format.
Please see the image below for the complete result of VALUE function in column B.
