VALUE Function
It converts a number represented as a text string into its numerical counterpart
What is the VALUE Function?
The VALUE function in Excel has a really simple task, convert a number represented as a text string into its numerical counterpart.
Sometimes, there will be numbers in Excel that get stored as text strings. After the decimal, a function even rounds up the number to a particular digit and then represents the value as a text string.
As a result, it is inevitable to have a function that reverses this particular phenomenon so that the user can store the numerical value as a number. And this purpose is fulfilled by the VALUE function.
This article will guide you on the VALUE function and how to use it in Excel to extract its maximum potential.
Key Takeaways
- The VALUE function is believed to provide compatibility with other spreadsheet software and returns the text to its numerical counterpart.
- The function converts even the date and time values into their numerical counterparts.
- Excel does the text-to-number conversion by default; however, some spreadsheet functions might not be able to perform a similar task. This is when the function can be really handy.
- The TYPE function can determine whether the text has been converted to a number.
Understanding the VALUE Function
The VALUE is categorized as a Text function that changes a number from being stored as a text string to a numerical value.
The function changes a text string to its numerical counterpart but is only valid for the data types stored as numbers. This includes the numbers along with date and time values.
where,
- text - (required) reference to the cell containing the text value, which will be converted to the numerical counterpart.
The function is extremely useful when the data must be compatible with other spreadsheet software.
That’s everything you need to know about using the function and what it usually does! In the subsequent section, we will see a couple of examples of how to use the function and even check whether the function works or not.
How to use the VALUE Function in Excel?
For the VALUE function to work, the numerical value must be represented as a text string so that it can be reverted to its original data type, i.e., numbers.
For example, suppose you have the data as illustrated below:
At first glance, all of these look like numbers. However, that is not the case. Excel’s TYPE function can identify the data type for the referenced value.
For example, if the result is 1, the cell contains a number. 2 will be text strings, 4 will be a logical value, 16 will be an error, and so on.
Thus, when we use the formula as =TYPE(B3), which surprisingly gives the result
We know that these are numbers and not text strings, so we can infer that these values are stored as text strings.
However, we do not want them as a different data type, i.e., we need them back in the number format. This is where the VALUE function comes in, wherein the formula in column C3 will be rewritten to =VALUE(B3) and dragged down to cell C6 to give:
Now, let’s check the values again using the TYPE function but with a major difference: the referenced number will be from column C, not column B.
The formula will be =TYPE(C3) in cell D3 to cell D6, giving the result as 1, meaning all these are now numbers.
All the values are now stored as numbers confirmed by the TYPE function.
Note
If you reference a text string as an input, the function returns the #VALUE! Error.
Date and Time Values
If you reference the date and time values in the function, it will return the numerical counterpart as a result. This is because we know that Excel stores the date and time values as numbers.
The date is stored as serial numbers from 1st January 1900, whereas the 24-hour clock will have the time stored as a decimal number between zero and one.
For example, if the date is 14th January 2023, the corresponding serial number is 44940. Similarly, the time 11:56 PM will be stored as 0.997222.
If you do not know what the corresponding numbers for date and time values are, the VALUE function works great.
Suppose you have the data as illustrated below:
When we use the formula =VALUE(B3) in cell C3 and drag it down till cell C5, we get
The time 10:56 AM is stored as 0.455556, while the date 11th November 2023 is stored as 45241. We also have a number formatted with the dollar symbol in cell B5, which removes the formatting after using the VALUE function.
FIXED Function
Although FIXED and VALUE are not directly related, the common trait that exists amongst either is converting text to a number or vice versa.
If VALUE can convert the text-formatted numerical value into number formatting, you can probably estimate what the FIXED function does.
However, the major task the FIXED function accomplishes is rounding numbers and returning the text counterpart followed by commas between the numbers.
Suppose you have the data as illustrated below:
We will use the formula =FIXED(B3,1,FALSE) in cell C3 and drag it down to cell C5, which gives the result:
All these results have one three in common: they are rounded to the first digit after the decimal, separated by the commas (for thousands and millions), and then finally, they are represented as text.
The final part can be confirmed using the TYPE function wherein the formula in cell D3 will be =TYPE(C3). After dragging it down to cell D5, we get
When the result is 2, it confirms that the data is stored as a text string.
or Want to Sign up with your social account?