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:
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”)
Formula =REGEXEXTRACT(A3, “blog|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, “….”)
Formula: =REGEXEXTRACT(A12, “In..”)
The above formula will applicable to the first part of the text. When you want to extract certain number of characters from the end, use the formula as below.
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)”)
A whole number, the “+” make the difference.
Formula: =REGEXEXTRACT(A6, “(\d+)”)
5. When you want to extract characters
Cell value in A7 is Info Inspired 2017 Blog
Formula: =REGEXEXTRACT(A7, “(\w)”)
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]”)
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]”)
Formula: =REGEXEXTRACT(A9, “[a-z]+”)
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]”)
Formula: =REGEXEXTRACT(A10, “[A-Z]+”)
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])”)
Formula: =REGEXEXTRACT(A11, “([A-Za-z]+)”)
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]”)
Formula: =REGEXEXTRACT(A19, “[0-9]+”)
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
Cell value in A22 is (Content) between question marks
Formula: =REGEXEXTRACT(A22, “\(([A-Za-z]+)\)”)
Cell value in A24 is email me <email@example.com>
Formula: =REGEXEXTRACT(A24, “<(.+)>”)
The above are the most common RE2 expressions in Regex Google Sheet functions.