Excel Value Function
Ideally, Microsoft Excel recognizes the number values entered as string or text and automatically converts them into the numerical format. But sometimes, when we import the data from a different platform to Excel, data comes in an unrecognizable format, and the numeric data can be left as strings making computations impossible. In such conditions the right call will be to convert the values to numbers and therefore the inbuilt Value function can be a great help.
What is a Value Function?
"The Excel VALUE function converts the string data to numbers. It can identify numeric strings, dates and times and converts them to numbers".
To understand this better, we have taken a series of datasets wherein we have mentioned the total cost of different products in the text format. For instance, in the below image, the values in Column C are in a text format.
NOTE: The text or String values are always aligned towards the left side of any cell, whereas the numeric data type is aligned at the right corner.
Now, if we want to convert to number, we can use the following formula in cell D2:
As a result, the value is converted in a numeric format.
Although the VALUE function is not used much in Excel, it automatically converts text to numbers when required. But users were facing problems as this practice was incompatible with other spreadsheet applications. Therefore to combat this situation, Microsoft has introduced the VALUE function in Excel 2007 version, and it is available in all later versions.
Text (required): This parameter represents a text string confined in quotation marks or a reference to a cell containing the text you want to change to a number data type.
The Value function returns a numerical data after converting the text string value to number.
In most situations, Excel automatically converts the string values to numbers. However, you need to explicitly tell Excel to do so in many cases when it can't take the values as numbers. Let's cover some examples to check how this function works in Excel worksheets.
Example 1: Convert to Number
Question: We have given a series of the cost of different products. But since we have exported it from other software, it is in a text format. Using the VALUE function, convert it to a number.
Following are the steps to convert the given text values to number:
Example 2: Extract number from string values
Many times in Excel, we require to extract only some characters from the beginning of the given text, or sometimes we need to extract the characters towards the ends of the text. Typically, we use Excel's inbuilt LEFT, RIGHT and MID functions to achieve the characters based on their position.
The output received using these functions is always text, even if you are extracting numbers. Though Excel automatically converts the text to numbers, but in many critical cases, it treats the extracted number characters as text strings, not numbers.
For instance, in the below image, we try to add the extracted numbers using the SUM function. Because the extracted number characters are text values, therefore the SUM function is not able to add up the values and is returning 0 as output.
The best method to resolve the above problem is by using the value function and converting the text to number. Follow the below steps to quickly get it done!
Example 3: Convert text to dates and times using VALUE function
The Value function can convert data and time text strings into a serial number representing the date or/and time in the internal Excel system. Therefore, it helps to restore the given date into integer value and time into decimal value. Later, if you want the output to appear in date format, you can easily apply the Date function and change the number format to Date format for the formula cells.
Following are the steps to quickly convert the Date into integer value using the VALUE function:
The following screenshot shows possible outputs:
NOTE: After applying the operation to your integer value, you can convert it to date format using the DATEVALUE function.
Excel VALUE function returns #VALUE error
While working with the Value function, it often returns a #VALUE error. It mainly occurs when the specified string is in a format not recognized by Microsoft Excel.
For instance, in the below image, we are converting the currencies into number format. But since in the source string the symbol '$' is present, that is not recognized by Excel. Therefore it will throw the #VALUE error.
To catch the unwanted errors in the strings, we can incorporate the IFERROR function along with the VALUE function.
=IFERROR(VALUE(B2), "ERROR, use only numbers")
Things to Remember