How to use the VALUE function
What is the VALUE function?
The VALUE function converts a text string that represents a number to a number.
Why is the VALUE function needed?
This function is included for compatibility with other software. Imported data from other software may cause values like numbers, dates, and times to be misidentified as text values in Excel.
The VALUE function is rarely needed in Excel formulas, since text is automatically converted to numbers where required.
What other functions in Excel are included for compatibility with other software?
T function - returns a text value if the argument is a text value.
N function - returns a numerical value if the argument is a number.
VALUETOTEXT function - returns a value in text form.
ARRAYTOTEXT function - concatenates values from a given cell range or array using the comma and/or semicolon as delimiters, the result is a text string.
Why convert date, time and number values from text to numerical values?
Converting to the appropriate data types makes it easier to calculate with, visualize, analyze, and store efficiently in Excel. Keeping numbers, times, and dates as text severely limits Excel's full capabilities.
- Enables calculations, text strings prevent any computations.
- Allows date/time functions to work properly. Valid date serial numbers are required, not text strings.
- Avoids sorting issues. Converting to numbers allows correct sort order.
- Saves storage space. Numeric formats are more compact than text formats, especially for large data sets.
- Enhances performance. Formulas with value conversions slow down spreadsheets.
Table of Contents
1. VALUE function Syntax
VALUE(text)
The VALUE function has only one argument.
2. VALUE function Arguments
text | Required. The number stored as a text string you want to be converted to a number. |
3. VALUE function example
Cell B3 contains a date identified as a text value, this may cause problems if we try to perform calculations to this date. Excel needs dates as numbers in order to properly perform date calculations.
Formula in cell C3:
The formula in cell C3 converts the text date string "1/1/2000" into a numeric date serial number in Excel. Dates in Excel are stored as serial numbers representing the number of days since January 1st, 1900. The date serial number for January 1st, 2000 is 36526 (the 36,526th day since 1900-01-01).
4. VALUE function not working
The VALUE function can only convert text formatted as standard number, date or time strings that Excel can identify. Any other text format will result in a #VALUE! error.
Cell B3 contains a date that Excel can't recognize, the VALUE function returns a #VALUE! error.
The VALUE function returns a #SPILL! error if cells below are not empty, delete the cell contents and the VALUE function should work properly.
Can the VALUE function handle arrays?
Yes, it can handle arrays.
Useful resources
VALUE function - Microsoft support
What is the VALUE Function?
Functions in 'Text' category
The VALUE function function is one of 30 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form