Updated August 16, 2023
Excel VALUE Formula
- Usually, excel recognizes the numeric data type when you input the same and stores it as numeric data. Excel recognizes and changes the data type to numeric even if the numbers are stored in text format. However, there are cases when the numbers are stored in a format unrecognizable to Excel. It fails to store those values as numbers and can be left as strings only. In such cases, the VALUE Formula in Excel comes to your rescue and efficiently converts the values into numbers.
- The Excel VALUE function comes under the Text Functions category, which can convert numeric strings, such as numbers, dates, time, etc., to numbers.
Syntax
An argument in VALUE Formula
text – This is a required argument. That specifies numeric strings such as numbers, dates, time, etc., which Excel has not recognized as numeric values and hence stored as strings.
How to Use Excel VALUE Formula in Excel?
Excel VALUE Formula is very simple and easy. Let’s understand how to use the Excel VALUE Formula with some examples.
Example #1 – Convert Text into Numeric Value
Suppose you have a value stored as text in cell A2 of the Excel sheet as below.
By looking at its alignment, you can recognize value is being stored as text. If the value is left-aligned, it is stored as text. Its value is right-aligned; it is stored as a number.
We will see how this string can be converted into a number using the VALUE function.
Start typing VALUE Formula in cell B2.
Give the text string as an argument that you want to be stored as a numeric value. The string is stored in cell A2. Therefore, use A2 as an argument under the VALUE function.
Complete the formula by closing the parentheses and press Enter Key.
You can see that the value in cell B2 is right-aligned. Which means it’s been converted into a number.
Example #2 – Convert Date into Numeric Value
Suppose you have a data value stored as a string in cell A2, as shown in the screenshot below.
You can see that the value in cell A2 is stored as a text string through the number pane above. Now, we will see how to convert this string into a numeric value.
Start typing VALUE Formula in cell B2.
Use cell A2 as an input to the cell VALUE function. It is a text value you want to convert into a numeric value.
Complete the formula by closing the parentheses and pressing Enter Key.
Here, the value is stored as a sequential number, which is nothing but the distance in terms of days from January 1, 1900. It means that May 30, 2019, is 43615 days after January 1, 1900.
Example #3 – Converting Time Stored as Text into Numerical Value
Suppose you have a well-formatted time value in your Excel sheet. You can convert it to numeric value as well using the VALUE function.
Suppose you have a well-formatted time value stored in cell A6 of the Excel sheet.
Start typing the VALUE formula in cell B2.
The value you need to convert is stored in cell A2. Give A2 as an argument to the VALUE function.
Complete the formula by closing the parentheses and pressing Enter Key.
You can see the converted time value in cell B2 of the Excel sheet. However, you will not frequently use this part to convert date values to numeric ones. But, it is good to have an idea of the same as it is useful to manipulate the time values (like, when you need to subtract the two-time values, this conversion will be helpful).
Example #4 – VALUE Formula to do Mathematical Operations
As with actual numbers, mathematical operations are also possible with the text-formatted numerical values using the VALUE formula. Let’s see how we can do this.
Suppose you have two text-formatted numeric values, as shown in the cells below.
Start typing VALUE Formula in Cell B3.
Use B1 as an input argument to value function and close the parentheses to complete the formula.
Use additional operators, which we use to add two or more numbers after the first VALUE Formula.
Use the VALUE formula again after the additional operator, and this time give cell B2 as input to it.
Press Enter Key to see the output for this expression.
Things to Remember About VALUE Formula in Excel
- Excel VALUE function converts the text string into numeric values
- It can also convert the formatted text as Date or Time into numeric Values.
- If the text you are about to convert into a numeric value has not been stored explicitly (in any Excel cell). You should ensure it is enclosed in double or single quotations while using an argument under the VALUE function. Because, if not done, it causes #NAME! Error in the VALUE formula.
- This formula can convert only those strings to numeric, which are mistakenly stored as texts. It would be best if you did not use any alphabetical letters that are not convertible into numeric values as an argument to the VALUE function. If you give the alphabet as an argument, the formula will give the #VALUE! Error.
- Microsoft Excel automatically identifies the type of data while you input it. Therefore, the VALUE function is not explicitly required.
- This function is ideal when you have programs compatible with Excel spreadsheet data.
- It can process any negative or positive numeric value stored as a string.
- It can convert the fraction values into numeric values, which are stored as text by mistake.
Recommended Articles
This is a guide to VALUE Formula in Excel. Here we discuss How to use the VALUE Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
