RegEx Formulas In Google Sheets | Easy Guide

RegEx Formulas In Google Sheets | Easy Guide
Reading Time: 3 minutes
How to use REGEX functions in google sheets || Complete Guide 2022

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+)”)”.

REGEXEXTRACT examples
Figure 1: REGEXEXTRACT examples

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.

REGEXREPLACE examples
Figure 2: REGEXREPLACE examples

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.

REGEXMATCH examples
Figure 3: REGEXMATCH examples

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.