Excel VALUE Function: Cultivate Your Values
The Excel VALUE Function is another Text manipulation function that will convert any numerical text in a cell to an actual number. It is yet another one of the most used functions that is great to know, when you need to use the values of the numbers in your text.
Excel VALUE Function:
Syntax of the Excel VALUE Function:
- text (Required) - The text enclosed in quotation marks or a reference to a cell containing the text you want to convert to a value.
- text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
- You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.
- =VALUE("$123,456.78") = 123456.78 (Money? No problem!)
- =VALUE("25%") = 0.25 (Percentages? That's a big can do!)
- =VALUE("3.00E-05") = 0.00003 (Exponents? I gotchu Fam!)
- =VALUE("3.00E+05") = 300000 (Yep, it even swings the other way!)
- =VALUE("10:30:00") = 0.4375 (Time? It's on your side!)
- =VALUE("11/29/2016") = 42703 (Date? Sorry, I'm married! 😉
An Explanation On How The Date and Time Formulas Work Above
Here is Microsoft's explanation of how Dates and Times are represented:
In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.
Where did they come up with THAT format?
I think the above description, from Microsoft, is pretty clear when it comes to Time, but it leaves the Date format a little unclear. Basically, if 01/01/1900 equals 1, then 01/02/1900 equals 2 and so on. Today is 10/31/2016 (Happy Halloween!), so today's number is 42674. Today has been exactly 42674 days, since 01/01/1900. So it is really just a sequential number that started at the turn of the last Century.
Download The Example Spreadsheet Here:
Why use the Excel VALUE Function?
Truth be told, you really don't need to, unless you want to open up the spreadsheet in another program, other than Excel. This function is provided for backward's compatibility as well.
Even though it is not necessary, some people may want to use it for the sake of clarity. When you see this function, you know what it does, whereas, it may take you a moment to realize what the calculation is doing, if it looks like text is involved. It is truly up to you.
Share, Comment, Ask a Question or Rip Me a New One!
If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!
P.S. - Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topics, products and/or services.