The REGEXEXTRACT Function in Google Sheets is useful if you want to extract a certain text string within a given data.
Table of Contents
REGEXEXTRACT function is one of the most advanced formulas in Google Sheets. Many users try to avoid this function because of its complexity. It becomes complex if partnered with another function.
It is important to understand the
REGEXEXTRACT function first, before combining it with other functions. Therefore, we created this guide.
Let’s take an easy example first.
For instance, we have a client who would like us to work on a task. He wanted us to check if there is the word “red” in the list of hair color products provided on Google Sheets.
To check this, we will use the
REGEXEXTRACT function. We will add attributes such as the cells where we want to get our data from, and the text that we are trying to extract, which is, in this case, the word “red”. As easy as pie!
Let’s take another example and level it up!
Say another client of ours is selling cellphone units. He or she wants us to check if the Amazon URLs contain the brand words “Samsung”, “Apple”, and “Xiaomi”.
REGEXEXTRACT function, we can easily input the range of cells where the URLs are added, and input the texts “Samsung”, “Apple”, and “Xiaomi”. Then, drag the formula down for it to work on the series of cells. Easy, right? Here’s how it looks like on Google Sheets:
As you can see in the example above, we used the
REGEXEXTRACT function, added the attributes needed, then inputted the words Samsung, Apple, and Xiaomi. In the example, the formula generates the words that we are trying to extract. If it doesn’t contain the words that we asked for, it will return an #N/A value.
REGEXEXTRACT function is extra useful, especially when we are working on large files that are full of information. Instead of checking every cell, and spending much time in moving from one cell to another, the
REGEXEXTRACT function comes in as a handy tool that helps you in those instances.
The Anatomy of the REGEXEXTRACT Function
So, the way we write the
REGEXEXTRACT Function is:
Let’s break this down to make the explanation simpler.
=the equal sign is just how we start any function in Google Sheets.
REGEXEXTRACT()is our function. We need to add two attributes, namely, the
regular_expressionto make it work correctly.
textis the cell where you want to extract a certain word. In our example above, F2.
regular_expressionis the word that you want to be extracted from a given text. In our example, “Samsung”, “Apple”, and “Xiaomi”.
⚠️ Now a few notes before using the REGEXEXTRACT Function:
REGEXEXTRACTfunction mainly and solely works for texts. If you want a numeric output, you have to pair it with a
VALUE()function. If you want to input numbers, then you must convert it first using the
REGEXEXTRACTfunction may or may not be paired with other functions. It would depend on your desired output.
regular_expressionmust be enclosed in a quote-unquote symbol “”.
- If you want to add more than one
regular_expression, you have to separate each
regular_expressionwith a pipe or bar “|“. This connotes “or”.
regular_expressionmay not be in an arranged manner. Meaning you can arrange in any way you like as the order does not give any precedence.
Now it may look like there’s a lot to know, especially with everything noted above. Rest assured, we will go through it and subsequently practice applying it. 🙂
A Real Example of Using REGEXEXTRACT Function
Take a closer look at the example below to see how the
REGEXEXTRACT function is used in Google Sheets.
In this example, we use the
REGEXEXTRACT function to determine which Amazon URLs have the words “Locked” and “Unlocked” in it.
Why have I chosen this example? Well, in a business setting, a client may want to know how many of his or her cellphone items are carrier-locked and unlocked.
To achieve this, we used this formula:
Here’s what this example does:
- We selected the cell G2 because this is where we would like to write our formula.
- Secondly, we started our formula with an equal sign ‘=‘ and the function,
- We opened a parenthesis and selected F2, our
text. This is where our extracted output will come from.
- Fourthly, in an enclosed quote-unquote symbol, we type the
regular_expressionwhich in our case are the words “Unlocked“, followed by a pipe “|“, then another word, “Locked“.
- We closed the parenthesis and hit the Enter key to get the result.
Viola! It’s easy, right?
You may make a copy of the spreadsheet using the link I have attached below:
Have a feel on how to work with this formula. Try it out for yourself.
How to Use REGEXEXTRACT Function in Google Sheets
- Simply click on any cell where you want to write down your formula. I will be choosing G2.
- Begin your function with an equal sign ‘=‘, then followed by the name of our function,
REGEXEXTRACT, then an open parenthesis ‘(‘.
- At this point, you should see the name of our function,
REGEXEXTRACT, and an auto-suggest box where you can see an example and a summary. This will serve as your extra guide in working with the formula.
- Column F contains the list of URLs that we are going to check. We will select the cell F2, as this is the start of the list. Furthermore, we have to add a comma ‘,‘ to separate the
textfrom our next attribute which will be our
regular_expression. Remember, our function takes two attributes:
- Next, enclosed by a quote-unquote symbol “”, type in the first
regular_expressionwe want to look for, which is “Unlock“. Then followed by a pipe “|” since we have more than one
- Still in an enclosed quote-unquote symbol, after the pipe, type the second
regular_expressionwhich we want to look for which is “Locked“.
- Close the formula with a close parenthesis ‘)‘.
- Finally, just hit your Enter key. Once you’ve hit the Enter key, drag the formula down to G11 by clicking on the little square on the bottom-left of the cell and dragging it down. This will apply the function on all the cells in the range from F3 to F11.
- After following the steps above, your output should look something like this.
That’s it. Well done! 👏🏆
You can now use the REGEXEXTRACT function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂