Excel Function of the Week - VALUE

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

This week we are looking at a very simple but a very powerful function.

The VALUE function is very easy to use, just type =VALUE(text) in a cell where text is a cell reference is a valid cell address e.g. A1 or T45 or a cell name – e.g. my_cell or just some text. It must represent a number.


The uses of this function are wide. I use it regularly in the following scenarios:

  • converting values imported from a CSV file or TEXT file that are actually formatted as text or general. This allows you to use the numbers as actual values and sum them etc. Use =VALUE(A1).
  • converting a number string that has been extracted from a text string. E.g. you have a cell A1 with text in it like 1245NAME and you need to get the 1245 out. Use =VALUE(LEFT(A1,4)) to extract the first 4 characters as a text string and then convert it to a number.
  • converting a number constructed using CONCATENATE or joins to make. This is very useful for dates. Eg =DAY(TODAY())&"/"&MONTH(TODAY())&"/2020" gies us todays date in the year 2020. However it is not a value, but a text string. Adding VALUE like this =VALUE(DAY(TODAY())&"/"&MONTH(TODAY())&"/2020") turns it into a date serial number. This can then be formatted as a date, and used as a date in calculations.

the Excel help provides this example:

  • =VALUE("16:48:00")-VALUE("12:00:00") The serial number equivalent to 4 hours and 48 minutes, which is "16:48:00"-"12:00:00" (0.2 or 4:48)


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Zoran's picture

Fantastic post, just like

Fantastic post, just like your IF function post. Bookmarked this site and emailed it to a few friends, your post was that great, keep it up.online roulettepoker siteonline blackjackvideo pokerdownloading movies