Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

Google Sheets Filter & Count cells that contain values

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:

Scenario 1:

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.

Scenario 2:

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.

Contents

 

The Formulas

Before we get started, here is a basic example. I have a list of snack. I want to extract all snacks that contain the word ‘Chips’.

Google Sheets Snacks

Top

Getting a list of items that contain certain values in each cell

Here we are going to use Google Sheet’s FILTER and REGEXMATCH functions.

FILTER

FILTER, as the name suggests, filters out any extraneous data you don’t want that is based on certain rules. With FILTER, you can grab a single column or a range of many columns and stack rules or conditions for one or more columns to filter down your results.

=FILTER(selected range, condition 1, condition 2, condition #, ...)

In our example, we just want to filter a single column based on a match of our regular expression.

REGEXMATCH

REGEXMATCH allows us to search through a piece of text and see if a particular condition exists using the ancient mystical secret codex of the regular expression…woooh.

The REGEXMATCH function takes two parameters. First, it takes a text or cell link to a text. The second parameter is then, the regular expression you will use to search for in your text.

=REGEXMATCH(text or cell ref to text, regular expression)

putting it together

We’ll place our REGEXMATCH function in the first condition of our filter. The FILTER function allows us to look through each row of the range we have selected. This means that when we apply our REGEXMATCH inside FILTER, we can give REGEXMATCH a range to search for instead of a single cell. It will iterate through each cell in the range completing the match for each.

Combined, it will look a little like this:

=FILTER(selected range, REGEXMATCH(text cell range, regular expression))

In our snack example, we want to get a list of cells that only contain the word “Chips”, so our code would look a little like this:

=FILTER(A2:A6,REGEXMATCH(A2:A6,"\bChips\b") = TRUE)

For now, don’t worry too much about what the \b thingy means. I’ll go into that later.

Your end result in Google Sheets should end up like this:

Google Sheets FILTER REGEXMATCH

Top

Counting a list of items that contain certain values in each cell

To achieve a count of how many cells in our column contain a certain value we will be using COUNTIF, ARRAYFORMULA and REGEXMATCH.

COUNTIF

COUNTIF takes two parameters. A range of data you want to count and the condition or rule you want to set to count.

=COUNTIF(range to count, condition)

For our example, our COUNTIF range would be the snack column and the condition would be our regular expression.

arrayformula

We can’t just plonk in the REGEXMATCH function for our COUNTIF range. If you recall, when we learnt how to create a list earlier in this tutorial, the REGEXMATCHs first parameter is a single cell or string of text. We could only change this to a range of cells when we put it inside FILTER.

This is why we use ARRAYFORMULA. The ARRAYFORMULA iterates its containing formulas over a selected range. So now, we can safely set the REGEXMATCH formula to a range and it will apply the function to each cell in the range.

It will look a little like this:

=ARRAYFORMULA(REGEXMATCH(text cell range, regular expression))

This will result in a list of TRUE or FALSE responses for each item in the range. The TRUE items being those that passed the REGEXMATCH condition.

pUTTING IT TOGETHER

Our COUNTIF function will take the REGEXMATCH function contained the ARRAYFORMULA as its first range parameter. COUNTIFs second condition parameter will be simply, TRUE.

=COUNTIF(ARRAYFORMULA(REGEXMATCH(text cell range, regular expression)),TRUE)

In our snack example, it will look a little like this on Google Sheets:

=COUNTIF(ARRAYFORMULA(REGEXMATCH(A2:A6,"\bChips\b")),TRUE)

Google Sheets COUNTIF REGEXMATCH ARRAYFORMULA

Now that we can count and filter cells that contain certain values in Google Sheets, let’s take a look a 9 cool* regular expressions you can use to search for cell items. We’ll use our two newly constructed formulas as our examples.

*I thought if I said cool here it would be more engaging. 

Top

The Regular Expressions

Regular expressions are tricky. They are the Ood of the tech world  – impossible to remember, but incredibly fashionable (That one is for the Doctor who fans).

via GIPHY

There are a number of variants to regular expressions, but Google Sheets uses the Google RE2 syntax.

For our example, we will use a list of 1,000 songs. Our goal is to list and count any song on the list that contains certain parameters.

list of songs in Google Sheets for regular expression search

Let’s get cracking with the examples.

Top

Contains “Love” on its own or part of a word

  • Regular Expression: "Love"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"Love") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"Love")),TRUE)

We can search for “Love” in our list by simply adding the term, “Love” to the criteria of our REGEXMATCH function. Any text you put into your regular expression is case-sensitive unless you deem it otherwise.

By simply adding the text you want to search for as your regular expression it will search for the word “Love” anywhere in the text on its own as a single word or as part of a larger word like “Lovely”.

regular expressions google sheet item contains text anywhere

Top

Contains “You” as its own word only

  • Regular Expression: "\bYou\b"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"\bYou\b") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\bYou\b")),TRUE)

Here, we only want to search our list of songs and find those that contain the word “You”. We don’t want “You” to be part of another word so we use the \b character on either side of the word. \b is an ASCII word boundary regular expression. It identifies the start or end of a word.

Let’s take a look a the results:

regular expressions google sheet item contains word only anywhere
GIF- May take a little while to load.

Top

Contains both “You” AND “Love” as their own words.

  • Regular Expression: "\bYou\s.*?\bLove\b|\bLove\s.*?You\b"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"\bYou\s.*?\bLove\b|\bLove\s.*?You\b") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\bYou\s.*?\bLove\b|\bLove\s.*?You\b")),TRUE)

In this example, we want to find the word “You” and “Love” in the text. The words can be anywhere in the text, but they must both be present.

This one looks infinitely more confusing than our last two examples but is simple once we break it down.

We need to tell the regular expression that “You” can be anywhere in front of “Love” in the text. Likewise, “Love” can be anywhere in front of “You” in the text.

First, we have our familiar \b character that determines a word boundary. Then we have the word “You” followed by the whitespace regular expression,\s.

\bYou\s

Now between “You” and our first “Love” (Ha!), we see this combo of regular expressions: .*?

  • . = the period represents any character.
  • * = the star represents zero or more repetitions.
  • ? = the question mark represents zero or one repetitions with a preference of one.

Combined, this regular expression set is saying that the text can contain any set of characters that is zero or greater with a preference of one more characters that can occur after “You ” and before “Love” appears.

“Love” has the boundary \b expressions on either side to ensure we are looking for a word.

So now we have half of the regular expression set:

\bYou\s.*?\bLove\b

Next is the or expression, |. This means that we are looking for text that contains “You” and “Love” somewhere in the text OR (|) “Love” and “You”.

The last “Love” and “You” portion after the or regular expression is the same as the first half, with the two words swapped around. So now it will search for both instances and if one of those instances exists, mark it as true.

\bYou\s.*?\bLove\b|\bLove\s.*?You\b

Google Sheets Regular expression for two words anywhere in text

Top

Contains any songs or singers and groups who have numbers in them

  • Regular Expression: "\d"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"\d") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\d")),TRUE)

In this example, we want to find any song, singer or group that has a number value. Basically, we are looking for the existence of a number anywhere in the text. To do this we use the \d regular expressions. Just think “d” for digit.

regular expressions google sheet item contains number anywhere
GIF – May take a little while to load

Top

Contains any day of the Week

  • Regular Expression: "Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday")),TRUE)

In this example, we are going to use the OR (|) regular expression so that we will accept any text that contains at least one of the days of the week.

If you had a long list of words you wanted to choose from you could possibly add a JOIN function and reference a row or column of data you wanted to choose from.

Imagine if you had a column of days of the week – one on each row on cell A1:A7. You could use join to make them one string:

=JOIN("|",A1:A7)

Then insert that into your main formula:

=FILTER(A:A,REGEXMATCH(A:A,JOIN("|",A1:A7)) = true)

Google Sheets Regular expression for OR statement

Top

Text starts with the letter “I”

  • Regular Expression: "^I"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"^I") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"^I")),TRUE)

Here, we want to find all the songs that start with the letter “I”. It can be “I” on its own or with other words.

To get the first letter or even a set of characters at the start of a sentence, we use the ^ regular expression.

regular expressions google sheet item contains character at start of text

Top

Text starts with the word “I”

  • Regular Expression: "^I\s"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"^I\s") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"^I\s")),TRUE)

This time around, we only want the word, “I”, if it is at the start of the text. To do this, we add the space \s regular expression. You could also use the word boundary  \b regular expression here too.

regular expressions google sheet item contains word at start of text

Top

Singer or Group name that contains a number in its name

  • Regular Expression: "\s–\s.*?\d"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"\s–\s.*?\d") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\s–\s.*?\d")),TRUE)

In this example, we only want cells that contain a digit in the singer or group’s name.

We know that our list has a special dash that separates the song name from the artist. We also know that there is a space either side of the dash. We want our regular expression to look for a digit somewhere after that dash.

First, we will add the dash and two spaces either side of the character.

\s–\s

Next, we will add our .*? combo we used earlier. Remember this combination says, that it’s okay to have any characters of any length that is zero or greater, but preferably greater than one.

\s–\s.*?

Finally, we add our digit regular expression to look for a number anywhere after the dash.

\s–\s.*?\d

regular expressions google sheet item contains digit after a certain character

Top

Singer or Group with only one name

  • Regular Expression: "\s–\s\S+$"
  • List formula: =FILTER(A:A,REGEXMATCH(A:A,"\s–\s\S+$") = true)
  • Count formula: =COUNTIF(ARRAYFORMULA(REGEXMATCH(A:A,"\s–\s\S+$")),TRUE)

In this last example, we want to find all the song that contains a singer or group with a single word name.

Again, we start off with our dash and spaces on each side.

\s–\s\

Next, we want to see a character that is not a space in the text. We use the capital “S” regular expression for this.

\s–\s\S

We then want to say that we need one or more of these non-space characters to follow. The plus sign + is used for this regular expression.

\s–\s\S+

Finally, we want any number of non-space characters to be the last thing in the cell or line. To do this we use the dollar sign $.

\s–\s\S+$

regular expressions google sheet item contains one word name at the end
GIF – May take a while to load.

Top

Conclusion

Hopefully, by now you have a pretty good grasp of how to get a list of rows based on cells that contain certain values along with their total count.

We also went over nine examples that you will most likely find useful in your day-to-day. The only way to get a grasp of these regular expressions is to apply them to your own projects.

Don’t worry if you can’t remember them, just bookmark this post and come back. Here is a summary you can use for quick reference:

Or you could make a copy of the file for an interactive version:

Counting and Filtering Data Where Data Contains Certain Values

Can’t get enough of Regular Expression? I get it. You can get some more here:

Google Sheets: Conditional Formatting with Custom Formula

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course. Sign up today!* 

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

One thought on “Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.”

Leave a Reply