Spy Journal 3.0 Filtering the web for you

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)