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.


Description of the Excel VALUE Function

the Excel VALUE Function converts a text string that represents a number to an actual number that can be used in your calculations.


Excel VALUE Function:

Syntax of the Excel VALUE Function:

  • VALUE(text)
  • 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.

Examples:

  • =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!  ðŸ˜‰
Excel VALUE Function

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:

The Excel VALUE Function


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.

Conclusion

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​.
Joe Austin
 

Joe Austin is the founder of Potentials Unleashed. He is currently an IT Professional Manager in the Financial Industry and has been using computers since 1978. While mostly self-taught, he is proficient in several computing areas, such as Programming , Web Development, most Microsoft Office programs, Internet Marketing, Spyware / Malware Removal and even 3D Animation & Modeling.

Click Here to Leave a Comment Below 0 comments
%d bloggers like this: