The REGEXEXTRACT Function in Google Sheets is useful if you want to extract a certain text string within a given data.
Table of Contents
The 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”.
Using the 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.
Additionally, the 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:
=REGEXEXTRACT(text, regular_expression)
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, thetext
andregular_expression
to make it work correctly.text
is the cell where you want to extract a certain word. In our example above, F2.regular_expression
is 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:
- The
REGEXEXTRACT
function mainly and solely works for texts. If you want a numeric output, you have to pair it with aVALUE()
function. If you want to input numbers, then you must convert it first using theTEXT()
function. - The
REGEXEXTRACT
function may or may not be paired with other functions. It would depend on your desired output. - The
regular_expression
must be enclosed in a quote-unquote symbol “”. - If you want to add more than one
regular_expression
, you have to separate eachregular_expression
with a pipe or bar “|“. This connotes “or”. - The
regular_expression
may 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:
=REGEXEXTRACT(F2,"Unlocked|Locked")
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,
REGEXEXTRACT
. - 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_expression
which 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
text
from our next attribute which will be ourregular_expression
. Remember, our function takes two attributes:text
andregular_expression
.
- Next, enclosed by a quote-unquote symbol “”, type in the first
regular_expression
we want to look for, which is “Unlock“. Then followed by a pipe “|” since we have more than oneregular_expression
.
- Still in an enclosed quote-unquote symbol, after the pipe, type the second
regular_expression
which 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. 🙂
