How to Use REGEXMATCH Function in Google Sheets

GiGiKing·March 18, 2024·0 comments

Imagine being able to unlock the power of regular expressions in your Google Sheets. Well, you can with the REGEXMATCH function! This nifty function allows you to find whether a piece of text matches a regular expression. In this article, we’ll explore the ins and outs of using REGEXMATCH in Google Sheets, so you can take your spreadsheet skills to the next level.

Google Sheets REGEXMATCH Function: A Closer Look

Before we dive into examples, let’s first understand the purpose and syntax of the REGEXMATCH formula.

Purpose of REGEXMATCH Formula

The purpose of the REGEXMATCH function is to determine whether a piece of text matches a regular expression. This function returns a Boolean value of TRUE or FALSE, making it perfect for logical tests and filtering data.

Syntax

The syntax for the REGEXMATCH function is as follows:

REGEXMATCH(text, regular_expression)
  • text: The text that you want to test against the regular expression.
  • regular_expression: The regular expression to test the text against.

Now that we have the basics covered, let’s explore some practical examples to showcase the power of the REGEXMATCH function.

Examples of Using REGEXMATCH in Google Sheets

1. Matching Plain Text

Let’s say you want to check if a certain phrase exists in a cell. You can use the following formula:

=REGEXMATCH(A2, "Info Inspired")
  • Cell A2 contains the text: “Info Inspired 2017, India”
  • Result: TRUE

2. Matching Single Characters

To match the presence of either of the single characters in a cell, you can use the pipe symbol |. For example:

=REGEXMATCH(A4, "y|o")
  • Cell A4 contains the text: “Info Inspired 2017, India”
  • Result: TRUE

3. Matching Words in a Sentence

If you want to match either of the words in a sentence, you can use the following formula:

=REGEXMATCH(A8, "blog|India")
  • Cell A8 contains the text: “Info Inspired 2017, India”
  • Result: TRUE

4. Matching Number of Characters

To match whether a cell contains a specific number of characters, use the following formula:

=REGEXMATCH(A9, "...")
  • Cell A9 contains the text: “Info Inspired 2017, India”
  • Result: TRUE

5. Matching Decimal Digits

To match the presence of any number of decimal digits in a cell, use the following formula:

=REGEXMATCH(A11, "d")
  • Cell A11 contains the text: “Info Inspired 2017, India”
  • Result: TRUE

6. Matching Specific Decimal Digits

To match the presence of any specific decimal digits in a cell, use the following formula:

=REGEXMATCH(A12, "[5-9]")
  • Cell A12 contains the text: “ABC6”
  • Result: TRUE

7. Matching Specific Characters

To match any of the specific characters in a cell, use the following formula:

=REGEXMATCH(A13, "[dxy]")
  • Cell A13 contains the text: “Info Inspired 2017, India”
  • Result: TRUE

8. Matching Small Case Letters

To match the presence of any lowercase letters in a cell, use the following formula:

=REGEXMATCH(A14, "[a-c]")
  • Cell A14 contains the text: “D”
  • Result: FALSE

9. Matching Uppercase Letters

To match the presence of any uppercase letters in a cell, use the following formula:

=REGEXMATCH(A15, "[A-Z]")
  • Cell A15 contains the text: “D”
  • Result: TRUE

10. Matching Content Between Question Marks

To match any content between question marks in a cell, use the following formula:

=REGEXMATCH(A16, "\?([A-Za-z]+)\?")
  • Cell A16 contains the text: “Can you find this ?Content? between question marks”
  • Result: TRUE

11. Matching Content Between Brackets

To match any content between brackets in a cell, use the following formula:

=REGEXMATCH(A17, "\(([A-Za-z]+)\)")
  • Cell A17 contains the text: “HI (Content) between question marks”
  • Result: TRUE

12. Matching Email Addresses

To match any email address in a cell, use the following formula:

=REGEXMATCH(A18, "<(.+)>")

13. Exact String Match

To match an exact string in a cell, use the following formula:

=REGEXMATCH(A19,"^student 1$")
  • Cell A19 contains the text: “student 19”
  • Result: FALSE

Now that you have a grasp on the REGEXMATCH function, the possibilities are endless! You can use it in combination with other functions like FILTER to perform complex logical tests and extract specific data from your Google Sheets.

Conclusion

Congratulations! You’ve learned the ins and outs of using the REGEXMATCH function in Google Sheets. Regular expressions can be a powerful tool for manipulating and analyzing data in your spreadsheets. If you’re hungry for more Google Sheets tips and tricks, feel free to explore Crawlan.com, your ultimate source for all things Google Sheets.

So go ahead and unleash the power of REGEXMATCH in your Google Sheets. Happy spreadsheeting!

GiGiKing

Related posts