06. Functions

A “Function” is essentially a pre-written sequence of computer code that can be used in a formula to do something to your data. If you can think it, then it’s more than likely, there is a function you can use that will do it.

SEARCH
In our spreadsheet, we’d like to find the comma that is in the student’s name so that we can grab all the letters before it. In order to start our formula we need to use the “SEARCH” function. Try typing the following into Cell C8 of the “Summary” worksheet:

=SEARCH(",",gv.E5)

Once you have it typed you will need to press the “Enter” key or click on the formula complete button (pictured below):

You can’t just click somewhere else because clicking on a cell or a worksheet while typing a formula will incorporate the item/range you select into the formula you’re creating!

So now in Cell C8 you should have the number 7. This may not seem all that useful, but what it tells us is where, in that string of characters, the comma is located. Now that we know where the comma is we can use another function to extract the part of the student’s name we require.

MID
Start by going back into Cell C8 and just after the equals sign we need to utilise a new function called “MID”:

=MID(gv.E5,1,SEARCH(",",gv.E5)-1)

Notice that we’re using the results of the SEARCH function inside the MID function. We already saw earlier that the SEARCH function returned the number 7, well the MID function needs to know when to start and stop grabbing characters, the point at which we’d like it to stop is one character before the comma:

So there we have the last name being extracted out of the student’s full name. It’s important to make sure you understand how those functions work, take note of a few things in particular:

  1. Functions can have parameters, those parameters are always separated by a comma.
  2. Functions can be placed inside other functions, provided the inner function returns the correct/appropriate data for the outer function.
  3. When you click on a cell or range of cells while typing a formula, that cell/range becomes part of the formula.

There is no way you will be able to remember or learn all available functions, if you require one, the best thing to do is hit Google and see what you can find. So as a final task, see if you can CAPITALISE the surname before moving on to the next lesson…

Leave a Comment

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>