How to Use Regexextract Function in Google Sheets and RE2 Regular Expressions

0
163
How to Use Regexextract Function in Google Sheets

Google Sheets Regex functions are a challenge to learn for even for its regular advanced users. So this time I dedicate my topic to Regexextract function in Google Sheets. This function is one among the three Regex functions in Google Sheets.

You cannot find enough resources or discussion connected with Google Sheets Regex functions anywhere. The reason, it’s bit complicated. I think users are skipping it mainly due to the lack of proper guidance anywhere.

There are three built in REGEX functions in Google Sheets. They are Regexextract, Regexmatch and Regexreplace. These functions are similar in use as the secret lies in the RE2 regular expressions. So learning any one of these function is enough to understand the rest. Here I will explain you how to use Regexextract function in Google Sheets.

I have selected Regexextract for my tutorial because instead of true or false, you can see the value as result in this.

You can use the REGEX function in combination with array, trim, transpose, query like other functions. Understanding Regex comes important at this point. First learn Regex and start using it with other functions.

Why Regex function seems complected? As already told it is purely connected with the RE2 expressions in it in use. If you go through the expressions you will not understand much. So with the help of few formulas I will try to explain the most frequently using RE2 expressions in Google Sheets Regexextract. It is also applicable to Regexmatch as well as Regexreplace.

How to Use Regexextract Function in Google Sheets

Google Sheets Regexextract function can only work with text. If any extracted value from the text is numeric you can use the value() function to convert it to numeric. As already told, you can use Regex function together with lots of other Google Sheets functions. I am not going to that part this time.

First let us begin with the syntax:

REGEXEXTRACT(text, regular_expression)

What are the two elements in the syntax?

text – The input text.

regular_expression – The first part of text that matches this expression will be returned.

Google products use RE2 for regular expressions. That part is the complicated one with the functions and it’s pathetic that there is no proper guide to use these expressions with in functions in Google Sheets.

Now it’s time to go through different scenarios.

Formulas to Learn How to Use Regexextract Function in Google Sheets

1. When you want to extract any plain text using regex, use that directly within quotes

Cell value in A2 is Info Inspired 2017, India

Formula: =REGEXEXTRACT(A2, “Info Inspired”)
Result: info inspired

2. To extract either of the character or word  (this or that)

Cell value in A3 is Info Inspired 2017, India

Formula =REGEXEXTRACT(A3, “y|o”)
Result: o
Formula =REGEXEXTRACT(A3, “blog|India”)
Result: India

Remember the syntax this time. What it says? “The first part of text that matches this expression will be returned.”

3. A single period extracts single character, two periods extract 2 characters and so on.

Cell value in A12 is Info Inspired 2017

Formula: =REGEXEXTRACT(A12, “….”)
Result: info
Formula: =REGEXEXTRACT(A12, “In..”)
Result: info

4. Google Sheets Regexextract function to extract decimal digits

Cell value in A6 is Info Inspired 2017 Blog

A single decimal digit, not the whole number.

Formula: =REGEXEXTRACT(A6, “(\d)”)
Result: 2

A whole number, the “+” make the difference.

Formula: =REGEXEXTRACT(A6, “(\d+)”)
Result: 2017

5. When you want to extract characters

Cell value in A7 is Info Inspired 2017 Blog

Formula: =REGEXEXTRACT(A7, “(\w)”)
Result: I
=REGEXEXTRACT(A7, “(\w+)”)
Result: Info

6. When you want to extract both characters and numbers

Cell value in A8 is Info Inspired 2017 Blog

Formula: =REGEXEXTRACT(A8, “(\w*)\s(\d+)”)
Result: Inspired in one cell, 2017 in another, horizontally.

RE2 expression in this formula “\s” used to match the white space between Inspired and 2017.

7. Regexextract function to extract the whole text

Cell value in A17 is Info Inspired 2017

Formula: =REGEXEXTRACT(A17, “(.+)”)
Result: Info Inspired 2017

8. Characters grouped in square bracket 

Cell value in A5 is Info Inspired 2017 Blog

Formula: =REGEXEXTRACT(A5, “[dxy]”)
Result: d

9. Expression [a-z] can extract any small case letters in a sequence

Cell value in A9 is info inspired 2017

Formula: =REGEXEXTRACT(A9, “[a-z]”)
Result: i
Formula: =REGEXEXTRACT(A9, “[a-z]+”)
Result: info

10. Expression [A-Z] can extract any upper case letters in a sequence

Cell value in A10 is INFO INSPIRED 2017

Formula: =REGEXEXTRACT(A10, “[A-Z]”)
Result: I
Formula: =REGEXEXTRACT(A10, “[A-Z]+”)
Result: INFO

11. [A-Za-z] Use this expression in Regexextract when you are not case sensitive

Cell value in A11 is Info Inspired 2017

Formula: =REGEXEXTRACT(A11, “([A-Za-z])”)
Result: I
Formula: =REGEXEXTRACT(A11, “([A-Za-z]+)”)
Result: Info

12. Expression [0-9] can match any number in a sequence from a text

Cell value in A19 is Info Inspired 2017

Formula: =REGEXEXTRACT(A19, “[0-9]”)
Result: 2
Formula: =REGEXEXTRACT(A19, “[0-9]+”)
Result: 2017

If you could learn the above use of RE2 expressions in Regexextract, you can continue to the few more advanced usage. The below examples are mix of the above expressions.

13. Extracted content which is between certain characters

Cell value in A21 is ?Content? between question marks

Formula:=REGEXEXTRACT(A21, “\?([A-Za-z]+)\?”)
Result: Content

Cell value in A22 is (Content) between question marks

Formula: =REGEXEXTRACT(A22, “\(([A-Za-z]+)\)”)
Result: Content

Cell value in A24 is email me <info@infoinspired.com>

Formula: =REGEXEXTRACT(A24, “<(.+)>”)
Result: info@infoinspired.com

The above are the most common RE2 expressions in Regex Google Sheet functions.

LEAVE A REPLY

Please enter your comment!
Please enter your name here