Summary
The Excel VALUE function converts a text string that appears in a standard number, date, or time format into a numeric value. It is particularly useful for converting text to a number in a locale-independent way.
Syntax
=VALUE(text)
- text: The text string that you want to convert into a numeric value
Return value
A numeric value derived from the text string.
How to use
VALUE is used to convert text strings that represent numbers, dates, or times into actual numeric values. This function is especially useful when dealing with numeric data in text format, ensuring that it’s correctly recognized for calculations and statistical analysis.
Examples
Simple VALUE
Converting Text to a Number:
=VALUE("1234.56")
Converts the text “1234.56” to the numeric value 1234.56.
VALUE with Date String
Converting a Date String to a Number:
=VALUE("1/1/2022")
Converts the date string “1/1/2022” to its numeric equivalent in Excel’s date system.
VALUE with Time String
Transforming Time Text into a Numeric Value:
=VALUE("12:30 PM")
Converts “12:30 PM” to a numeric value representing the time in Excel.
VALUE in Data Import
Normalizing Imported Data:
=VALUE(A2)
If A2 contains a numeric value in text format, like “$1,234.56”, the formula converts it to 1234.56.
VALUE for Locale-Specific Formats
Adapting to Different Locale Number Formats:
=VALUE("1.234,56")
This converts a European-formatted number “1.234,56” to 1234.56.
Additional Notes
- VALUE is essential for data processing from various international sources with different numerical formats.
- Useful in data cleaning, especially when preparing data for calculations or statistical analysis.
- When importing data, VALUE can help ensure that numeric values are correctly recognized and formatted for use in Excel functions and formulas.