Syntaxes=REGEXTRACT(text, regular_expression)
=REGEXREPLACE(text, regular_expression, replacement)
=REGEXMATCH(text, regular_expression)text – The input text.regular_expression – The regular expression to search for; the first match will be returned.replacement – The text to replace with.
Sample Usages=REGEXEXTRACT(A2, “\d+”)
//Returns the first matching integer from the string in cell A2.=REGEXREPLACE(proper(A10),"The","the")//Capitalises all the words in cell A10 and replaces “The” with “the”.=REGEXMATCH(A15,"Blue")
//Checks for the word “Blue” in A15 and returns TRUE if found, else returns FALSE.
In one of the articles, we discussed Regular Expressions Rules and Syntax. Now we will discuss a few RegEx Formulas in Google Sheets that you need to know about. There are primarily 3 inbuilt functions that you can use. All three of these serve different functionalities that can be used in different scenarios.
Need for RegEx formulas in Google Sheets
RegEx is an abbreviation for Regular Expressions. This article explains the use and need for RegEx. When you are working on a lot of data, then customized searching is necessary to perform niche searches. To achieve them we can use regular expressions. There are mainly three RegEx formulas in Google Sheets that you can use.
- REGEXEXTRACT
- REGEXREPLACE
- REGEXMATCH
These functions perform extract, replace and match, respectively. You can check the RegEx Rules and Syntax in an article written earlier. In this article, we would be focusing on the inbuilt Regex formulas in Google Sheets.
REGEXEXTRACT
If you want to search for a specific substring in a list of entries, you can use REGEXEXTRACT.
Syntax
=REGEXEXTRACT(text, regular_expression)- text- The text you want to search in
- regular_expression- The regular expression you want to search for, the first match will be returned.
Sample Usage
=REGEXEXTRACT(A2, “\d+”)Returns the first integer that it meets
Capture Groups
We can use capture groups to return more than one value. A capture group is a part of a pattern that can be enclosed in parentheses. If no capture groups are found, the function returns the entire match. For example, “REGEXEXTRACT(A4, “I love (\w+) and (\w+)”)”.
For a video tutorial, see this video.
REGEXREPLACE
For a video tutorial on this function, see this
Just like the name suggests, this function replaces text with another string.
Syntax
=REGEXREPLACE(text, regular_expression, replacement)- text- The text you want to search in
- regular_expression- The regular expression you want to search for, the first match will be returned.
- replacement- The text you want to replace with.
Sample usage
=REGEXREPLACE(proper(A10),"The","the")The proper function capitalizes each word. After capitalization, we removed the capitalization for the word “the” by using regex.
Here we have two examples to demonstrate, the first one removes the brackets and the second capitalizes except the articles. Hence you can perform very niche functions using regex.
REGEXMATCH
REGEXMATCH searches for values and returns true or false, based on if that pattern is present or not. The REGEXMATCH function returns a TRUE if it matches the pattern you provide anywhere in the text and FALSE if there are no matches in the text.
Syntax
=REGEXMATCH(text, regular_expression)- text- The text you want to search in
- regular_expression- The regular expression you want to search for, TRUE or FALSE will be returned based on the presence of the pattern
Sample Usage
=REGEXMATCH(A15,"Blue")Here we are checking if the blue word is present in the text, if present it would return True, else it would return False.
Conclusion
Hence we saw different RegEx formulas in Google Sheets and saw their sample usage. RegEx is a very powerful tool that can be properly utilized if you have a lot of practice. You can check out more rules and syntaxes in a previously written article.
See Also
Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Regular Expression rules and syntax in Google Sheets: Introduction to Regular Expression Rules and Syntax in Google Sheets.
Validate URLs in Google Sheets: We will use ISURL function to validate URLs in Google Sheets. We will also try to validate a URL using the Data Validation option in Google Sheets.