Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Excel Function of the Week - VALUE

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.

clip_image001

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)
jethro's picture

This Weeks Web Roundup

Time to unload the browser again. And I have some great stuff for you all this week. Starting with some geek humour as well as some new toys and news.

CB068324_LoResGeeks are Sexy post a YouTube video of the Big Bang Theory and Rock, Paper, Scissors, Lizard, Spock, their use of the extended version of Rock, Paper, Scissors. Check it out!

HP has suddenly become an Excel Tips website! These three pages were linked in their last newsletter.

Some great news on the Windows Live front.

First of all A note about Hotmail, SkyDrive and Photos storage space from LiveSide advising that there is now a massive 50GB o free space online available to you as follows:

jethro's picture

Excel function of the week INT

I am combining the function of the week with a request I had to write some VBA code to use it. (Note the VBA function is not always the same as the application function).

CBR001292_LoRes Here is the question I was asked:

I have two columns - One "AA" and the other "AB"
I want to fill down the column AB based on the results in "AA" but I'm
confused about how to get the individual data from AA2 into AB2 - for
example...
Dim AAcell As Range
Dim ABcell As Range
For Each AAcell In Range("AA:AA")
If IsEmpty(AAcell) Then
    ABcell.Value = ""
Else
    ABcell.Value = Int(AAcell.Value)
End Sub
I'm sure this isnt' right/correct - as it isn't working - LOL - but I'm not
sure how to put it...

Reading this through I understood that the reader wanted to be able to fill a column based on another column. If the first column had a blank cell he wanted that repeated in the new column. All other entries in the new column were to be the INTEGER of the number in the first column. I have provided the code used to make this work at the end of this article. Click Read More to continue.

jethro's picture

Excel function of the week - FIND and SEARCH

This week we will look at some text functions. FIND and SEARCH are essentially the same function with only minor differences.

magnifying-glass-778751

These functions both allow you to search through a text string (think sentence or word or phrase) in a cell and return the number of the starting character. So when cell A1 has The quick brown fox, =FIND(“fox”,A1) would return 17.

The following screenshots indicate the function arguments and explain the functions syntax.