Google Sheets REGEX functions are an essential tool for manipulating and analyzing text. Among these functions, REGEXMATCH is particularly useful for performing logical tests on text values. In this article, we will delve into the intricacies of using REGEXMATCH in Google Sheets, empowering you to unlock its full potential.
Google Sheets REGEXMATCH Function
Before we dive into the practical examples, let’s understand the purpose and syntax of the REGEXMATCH function.
Purpose of REGEXMATCH Formula
The purpose of the REGEXMATCH function is to determine whether a piece of text matches a given regular expression. By employing this function, you can effortlessly perform complex searches and pattern matching operations.
Syntax
The syntax for the REGEXMATCH function is as follows:
REGEXMATCH(text, regular_expression)text: The text to be tested against the regular expression.regular_expression: The regular expression used to test the text.
Now that we have covered the basics, let’s explore some practical examples to demonstrate the power of REGEXMATCH.
Example of REGEXMATCH Function
1. Matching Plain Text
Suppose we have the text “Info Inspired 2017, India” in cell A2. We can use the following formula to check if the text contains the phrase “Info Inspired”:
=REGEXMATCH(A2, "Info Inspired")The result will be TRUE. However, if we change the text to “InfoInspired 2017, India” in cell A3, the formula will return FALSE. Similarly, if we change the text to “Info inspired 2017, India” in cell A4, the formula will still return FALSE.
2. Matching Single Characters
To match the presence of either of the single characters in a word, phrase, or sentence, use the vertical bar “|” symbol. For example, if we have the text “Info Inspired 2017, India” in cell A4, we can use the following formula to check if the text contains either the letter “y” or “o”:
=REGEXMATCH(A4, "y|o")The result will be TRUE. However, if we change the formula to check for the presence of the letters “b” or “z”, the result will be FALSE.
3. Matching Multiple Words
If you want to match either of the words in a sentence, use the vertical bar “|” symbol. For example, if we have the text “Info Inspired 2017, India” in cell A8, we can use the following formula to check if the text contains either the word “blog” or “India”:
=REGEXMATCH(A8, "blog|India")The result will be TRUE.
4. Matching Specific Number of Characters
To match whether the text contains a specific number of characters, use the period “.” symbol. For example, if we have the text “Info Inspired 2017, India” in cell A9, we can use the following formula to check if the text contains three characters:
=REGEXMATCH(A9, "...")The result will be TRUE. Similarly, if we want to match the presence of six characters, we can use the formula:
=REGEXMATCH(A9, "......")However, if we change the text to “Cloud” in cell A9, the formula will return FALSE.
5. Matching Decimal Digits
To match the presence of any number of decimal digits in the text, use the letter “d” inside parentheses. For example, if we have the text “Info Inspired 2017, India” in cell A11, we can use the following formula to check if the text contains any decimal digits:
=REGEXMATCH(A11, "(d)")The result will be TRUE.
6. Matching Specific Decimal Digits
To match the presence of any specific decimal digits in the text, use the square brackets “[ ]”. For example, if we have the text “ABC6” in cell A12, we can use the following formula to check if the text contains any decimal digit from 5 to 9:
=REGEXMATCH(A12, "[5-9]")The result will be TRUE.
7. Matching Specific Characters
To match any of the specific characters in a text, use the square brackets “[ ]”. For example, if we have the text “Info Inspired 2017, India” in cell A13, we can use the following formula to check if the text contains the characters “d”, “x”, or “y”:
=REGEXMATCH(A13, "[dxy]")The result will be TRUE.
8. Matching Small Case Letters
To match the presence of any lowercase letters in a text, use the square brackets “[ ]”. For example, if we have the text “D” in cell A14, we can use the following formula to check if the text contains any lowercase letters from “a” to “c”:
=REGEXMATCH(A14, "[a-c]")The result will be FALSE.
9. Matching Uppercase Letters
To match the presence of any uppercase letters in a text, use the square brackets “[ ]”. For example, if we have the text “D” in cell A15, we can use the following formula to check if the text contains any uppercase letters:
=REGEXMATCH(A15, "[A-Z]")The result will be TRUE.
10. Matching Content Between Question Marks
To match any content between question marks, use the following formula. If we have the text “Can you find this ?Content? between question marks” in cell A16, we can use the following formula:
=REGEXMATCH(A16, "?([A-Za-z]+)?")The result will be TRUE.
11. Matching Content Between Brackets
To match any content between brackets, use the following formula. If we have the text “HI (Content) between question marks” in cell A17, we can use the following formula:
=REGEXMATCH(A17, "(([A-Za-z]+))")The result will be TRUE.
12. Matching Email Addresses
To match any email address, use the following formula. If we have the text “email me [email protected]” in cell A18, we can use the following formula:
=REGEXMATCH(A18, "<(.+)>")The result will be TRUE.
13. Exact Matching a String
To perform an exact match of a string, use the following formula. If we have the text “student 19” in cell A19, we can use the following formula:
REGEXMATCH(A19,"^student 1$")The result will be FALSE. However, if we change the string to “student 1”, the formula will return TRUE.
Conclusion
Congratulations! You have learned how to unleash the power of the REGEXMATCH function in Google Sheets. If you want to explore more Google Sheets functions, be sure to check out our ultimate Google Sheets Function guide at Crawlan.com. Have fun transforming your data and unlocking new possibilities with REGEXMATCH!
