Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets

One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace.

Find and Replace is the penicillin of the the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff.

Yeah! Yeah! We all know about Find and Replace.

I know! I thought so too, but then I started to really use and identify how I could use it to quickly:

  • Change template sheets.
  • Fix bulk errors in formulas.
  • Change parts of cells.
  • Replace values in the whole spreadsheet, one sheet or a selected range.

Before we get started, you need to know the short cut for the Find and Replace tool. This will come in handy in all sorts of programs and apps.

  • PC – Ctrl + H
  • Mac – CMD (⌘) + H

I’m going to go ahead and continue the examples using PC, because, you know, Mac.

Continue reading “Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets”

5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets

It’s time to get evangelical peep! Release yourself from the yolk of the menu bar and it’s insidious demands on you coordination and time!

Break free from the right click menu! For is it not anything more than a proxy menu bar?! A veritable wolf in sheep’s clothing with no other design but to bring you back to the fold of the menu-using, mouth-drooling reprobates  who stand in the way of efficient progress! Can I get an Amen! I said, can I get an Amen!

No? Fair enough…

Let’s just get on with it then, shall we? After all, this is about efficiency, right?

Here are the 5 main shortcuts that I use in Google Sheets each and every day to save me a tonne of time. I’m going to give you a bit more than just the Keyboard Shortcut, I’m going to demonstrate how I use it with some clear examples.

Continue reading “5 Easy Shortcuts That Will Save you Heaps of Time – Google Sheets”

Google Sheets – How to Separate the First Name from a Full Name Cell

left, right, find, length

Every academic quarter I receive a list of students by their full name in one cell that I need to split into a cell for the first name and then a cell for the middle and last names combined. 

This fairly simple process can be achieved with the Google Sheets formulas left, right and find. 

If you are in a hurry, here are the formulas below: 

First Names

To get the first name we do the following: 

=LEFT(A2,FIND(” “,A2))

Where ‘A2’ is the cell that we have our full name in – in our case Vasco Nunez de Balboa.

When you have multiple formulas in a cell it’s often best to go from the inside out. So let’s first look at what FIND does. 

FIND looks inside the cell for the first value that we want to search for. For us, it’s an empty space, ” “. Find then returns the numerical position of that found item.  To do this FIND takes two arguments: 

=FIND(the item we are searching for, the cell or string the item is in)

For example if we are searching for the location of the space in
 Vasco Nunez de Balboa  which is in cell A2,  we would do the following:

=FIND(” “,A2)

Which would give the result: 6

There are five letters in the first name Vasco the the space would be position 6. 

Now that we have the position  of the space, lest just grab everything in the cell to the left of that space. We do this with LEFT

LEFT also take two values. The first is cell location and the second is the number characters we want to take from the left hand side. 

LEFT(cell location, number of characters from the left)

Now that we know the first space is character 6, the formula would look like this:

LEFT(A2,6)

We then replace the 6 with our FIND formula and we are good to go.

Continue reading “Google Sheets – How to Separate the First Name from a Full Name Cell”

Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function

Google Apps Script, Custom Functions

Boy, are these titles getting longer. 

But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three thing in Google Sheets: 

  1. Get the current sheet name.  That’s the same sheet name as the cell you are working in.
  2. Get all the sheet names. A full list of all the sheet names. 
  3. Get the name of the Spreadsheet file. 
Get Sheet Names and Spreadsheet Names Only - Google Apps Script

The above picture is pretty self explanatory. If you type in:

=SHEETNAME(#)

Where “#”  is a number 0, 1 or 2 you will get the results displayed in the picture. Any other number will display an error. 

How do I add this amazing Custom Function To my Google Sheet?

First copy the code below. Then go to <Tools>>Script Editor> This will open the Google Apps Script Editor. 

Next Rename the Project to whatever you want. I usually name it the same as my sheet. 

Next delete all the code in the Code.gs file and paste in our code. 

<File><Save> in the Project and then return to your spreadsheet and give it a try. 

The Code

You can learn more about how to build a Custom Function by following my tutorial:

Application

SHEETNAME  can be used in conjunction with other built-in functions in Google Sheets. 

It can be used in part, or as a whole as a naming or titling tool. For example, imagine our Spreadsheet file title is 2018-2019 Top Stocks and our Sheet Name for the current tab is Tech. Perhaps when we duplicate this file each year we just want the title inside our Tech Sheet to automatically update:

Which would generate:

2018-2019 Top Stocks-Tech

Another options might be a referencing tool to quickly create formulas in other sheets. Take a look a this example: 

Get SheetName Example Google Apps Script

Conclusion

So, I would be interested to see what you use this custom function for, if you found it useful. 

Have you done anything interesting with the code? I would love to see how you have advanced the Google Custom Function.

~Yagi

Google Sheets – Remove The Lowest Grade for Each Student on a Course

Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF

In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course all the weekly assessment is then added together minus the lowest piece of assessment.

For lecturers with small course sizes this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.

Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students.  My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student. 

I use Google Sheets for this purpose for it’s ease of use and sharability.

This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test. 

List of 7 grades for each student - Google Sheets
Continue reading “Google Sheets – Remove The Lowest Grade for Each Student on a Course”

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program. 

Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. Finally copy and paste the top 2 ranked students of each group in a new sheet – print and hand to the boss. 

However, with 100 classes this was going to turn into a time consuming chore and one that could be prone to errors. 

To solve this problem, I took advantage of Google Sheets RANK.EQ and FILTER formulas. Let’s take a peek at the formula now and go through the steps in detail later.

Let’s move on to an example.

Continue reading “Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER”

Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script

Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or want to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.

Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.

Custom Function Decorators Google Apps Script

Let’s look at the creation process through a recent example I created:

Continue reading “Google Apps Script – How to make a Custom Function to Use in Google Sheets”

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions

Google Sheets, Google Apps Script

I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.

It goes a little like this. If I want to build a 5 digit random number I would:

RANDBETWEEN Google Sheets

But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?

via GIPHY

 

No…

So instead I decided to make a custom function with Google Apps Script to do this job for me…

Continue reading “Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions”

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post a Google Sheets user wanted to change a four digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, Google Apps Script

Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.

For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Continue reading “How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.”