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.
Table of Contents
1. Introduction
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.
2. Syntax
VALUE(text)
The VALUE function has only one argument.
| text | Required. The number stored as a text string you want to be converted to a number. |
3. Examples
The VALUE function attempts to convert text that represents numbers in a recognized format into actual numbers. It works with various formats including dates, times, percentages, and currency. When it encounters a format it doesn't recognize or can't convert, it returns a #VALUE! error.
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 D3:
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).
Formula in cell D4:
The source data in cell B4 is "12:00 PM" and the result is 0.5 The function converts the time string to its decimal representation (fraction of a day).
Formula in cell D5:
The source data in cell B5 is "1/1/2025" and the result in cell D5 is: 45658 The function converts the date string to its numeric representation.
Formula in cell D6:
The source value in cell B6 is "14:45", and the result in cell D6 is: 0.614583333 The function converts the time string to its decimal representation.
Formula in cell D7:
The source data in cell B7 is "$1.45" and the result in cell D7 is: 1.45 Explanation: Removes the currency symbol and converts the string to a number.
Formula in cell D8:
The source data in cell B8 is "$1.45" and the result is 1.45 Explanation: Same as D7, removes currency symbol and converts to number.
Formula in cell D9:
The source data in cell B9 is "13/24" and the result is #VALUE! Explanation: Error because "13/24" is not recognized as a valid number or date format.
Formula in cell D10:
The source data in cell B10 is "Wednesday, March 14, 2012" and the result is: 40982 Explanation: Converts the long date string to its numeric representation.
Formula in cell D11:
The source data in cell B11 is "Wednesday, March 14, 2012" and the result is: #VALUE! Explanation: Error, because this specific long date format is not recognized by the VALUE function in this Excel setup.
4. Example 2 - filter converted values
The formula in cell D3 shown in the image above filters converted values from cell range B3:B11, in other words, values are filtered if the VALUE function converts text values to their numerical representation.
Cell range B3:B11 contains values, some are text values and some are regular numbers formatted as dates, times or currency.
Formula in cell D3:
The formula in cell D3 extracts values in cells B3, B4, B5, and B7 because they are text values that the VALUE function successfully converts to their numerical representation.
Explaining formula
Step 1 - Convert values in B3:B11 to their numerical equivalents
VALUE(B3:B11)
becomes
VALUE({"1/1/2000"; "12:00 PM"; "1/1/2025"; 0.614583333333333; 1.45; "$1.45"; "13/24"; 40982; "Wednesday, March 14, 2012"})
and returns
{36526; 0.5; 45658; 0.614583333333333; 1.45; 1.45; #VALUE!; 40982; #VALUE!}
Step 2 - Value to value comparison
The less than and larger signs allow you to check if a value is not equal to another value. The result is a boolean value TRUE or FALSE which the FILTER function in step 4 can use to extract the appropriate values.
VALUE(B3:B11)<>B3:B11
becomes
{36526; 0.5; 45658; 0.614583333333333; 1.45; 1.45; #VALUE!; 40982; #VALUE!}<>{"1/1/2000"; "12:00 PM"; "1/1/2025"; 0.614583333333333; 1.45; "$1.45"; "13/24"; 40982; "Wednesday, March 14, 2012"}
and returns
{TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; #VALUE!; FALSE; #VALUE!}
These values contain error values, we need to convert the errors to boolean value FALSE since we don't want the filtered.
Step 3 - Handle errors
The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.
Function syntax: IFERROR(value, value_if_error)
IFERROR(VALUE(B3:B11)<>B3:B11,FALSE)
becomes
IFERROR({TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; #VALUE!; FALSE; #VALUE!},FALSE)
and returns
{TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}
Step 4 - Filter values based on boolean array
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(B3:B11,IFERROR(VALUE(B3:B11)<>B3:B11,FALSE))
becomes
FILTER(B3:B11, {TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE})
and returns
{"1/1/2000"; "12:00 PM"; "1/1/2025"; "$1.45"}
5. 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 29 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