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, the`text`

and`regular_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 a`VALUE()`

function. If you want to input numbers, then you must convert it first using the`TEXT()`

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 each`regular_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 our`regular_expression`

. Remember, our function takes two attributes:`text`

and`regular_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 one`regular_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. 🙂

**Don’t forget to share this post!**