Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)
Okay, wait! Stop!…
…I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt to piece it all together.
I promise to give you some clear examples with an explanation of each to you can apply it to your project.
Take a deep breath, mate, and let’s get cracking.
We are going to look at two related scenarios:
Imagine that you have a huge list of items. You have a hunch that some of the cells contain certain values of interest for you. You want to build a new list with only those values in them.
Imagine that you have a list of full names, and you want to use Google Sheets to create a new list of full names that only contain John.
You have that same huge list again, but this time you only want the total count of all the rows that contain certain values in each cell.
Imagining that list of full names again, you are now going to get a total count of all full names that contain John in it.
We’ll first go through how to create these formulas and then provide a number of clear examples on some common criteria for searching cell for values using REGEXMATCH and regular expressions.
Continue reading “Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.”
Google Sheets – TEXT, TODAY, WEEKDAY
If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is niggling the back of your mind saying, “You can do this better!”
But the day-to-day race to get things done takes over and you move on to more pressing matter.
Until the next time you have to look at that Google Sheet and it starts bugging you again. Well, until you have a moment to fix it.
For me each week I had a sign-up sheet for a makeup test for students. The coordinators who would add students to the sheet required that in the header rows, the date of the next Makeup Test be added for each week.
The makeup test was always at the same time each week: Monday at 15:30. I would then need to prefix this with the day and month. So it would look something like this:
Monday 28 Jan at 15:30
My original approach then was to open the sheet and change the date manually each week. Okay, I admit that on not just one occasion I forgot to change the date much to the glee of the most persnickety of the coordinators who could happily call me out on my failure. Grrr.
I knew I could make this more efficient. I knew I could probably automate this process. Fortunately, the day finally came where I found myself with 15 minute s of free time and this issue in my mind.
This post is the solution to the problem.
Continue reading “How to display a date for one day in a week that automatically changes weekly on Google Sheets”