Have you ever found yourself needing to perform advanced substring comparison in Google Sheets Query? Look no further! The Matches regular expression match in Google Sheets Query is here to save the day. In this article, we will explore how to use this powerful feature to conquer complex text matching tasks.
Understanding Matches Regular Expression Match in Query
The Matches regular expression match in Query function allows you to filter rows based on exact text matches using regular expressions. Unlike the match feature in the REGEXMATCH function, Matches requires the entire string to match the given regular expression in the WHERE clause of the Query. Let me elaborate with an example.
Suppose you want to find cells in column A that contain the text “Info Inspired”. Using the REGEXMATCH function, you can achieve a global match by using the formula =REGEXMATCH(A1,"Info Inspired"). This formula would return TRUE if cell A1 contains the text “Info Inspired,” “Info Inspired Blog,” “New Info Inspired,” and so on.
However, if you want to filter rows that exactly match the text “Info Inspired” using the Matches regular expression in the Query’s WHERE clause, you would use the formula =query(A1:A, "Select A where A matches 'Info Inspired' ").
If you desire a partial match similar to the Regexmatch formula, you can wrap the regular expression with ‘.‘, like this: =query(A1:A, "Select A where A matches '.</em>Info Inspired.*' ").
Matches Regular Expression Match in Query: Formula Examples
To help you become more familiar with the Matches regular expression match in Google Sheets Query, let’s explore some formula examples:
1. Matching Either of the Texts (This or That) in Sheets
If you want to match either the text “India” or “Russia” in column B, you can use the formula:
=query(A1:B, "Select * where B matches 'India|Russia' ")
This formula filters the rows that contain either “India” or “Russia”. It’s a convenient way to replace the OR logical operator within the Query.
2. Matching a Substring Anywhere in a Text String
Suppose you want to match a substring containing the text “India” anywhere within it. Here are three Query formulas that achieve different matching scenarios:
=query(A1:B, "Select * where B matches '.*India.*' ")
=query(A1:B, "Select * where B matches 'India.*' ")
=query(A1:B, "Select * where B matches '.*India' ")
Each formula targets a specific position of the substring within the text. You can observe the results in a sample sheet screenshot provided in the original article.
3. Matching a Text String Containing Numbers in Query
To filter alphanumeric characters in a column using Query, you can use the formula:
=query(A1:B, "Select * where B matches '.*(d).*' ")
This formula comes in handy when you want to filter passwords that contain alphanumeric characters.
4. Matching Content Between Question Marks/Open Clause Brackets
In some cases, you might need to match the contents within brackets in a text. The following formula achieves this:
=query(A1:B, "Select * where B matches '.*?([A-Za-z]+)?.*'")
You can replace the question mark with open-close brackets to match the contents within brackets. For instance:
=query(A1:B, "Select * where B matches '.*(([A-Za-z]+)).*'")
These formulas allow you to be more specific when matching text strings that contain information within brackets.
5. Matching Rows That Contain First, Second, or Last Name Together
To filter rows that contain first, middle, and last names together, you can use the following formula:
=query(A1:B, "Select * where B matches '(?:S+ ){1}(S+)'")
If you want to filter only the names that contain the first name, middle name, and last name, you can replace {1} with {2} in the formula.
6. Filtering Rows with a Certain Number of Characters in a Column
If you need to filter rows with a specific number of characters in a column, you can use the following formula:
=query(A1:B, "Select * where B matches '..'")
By increasing the number of dots (periods), you can adjust the number of characters to match.
7. Matching Texts Containing Vowels/Consonants
To filter rows that contain texts with vowels or consonants, you can use the following formulas:
Formula 1:
=query(A1:B, "Select * where B matches '.*[aeiou].*'")
Formula 2:
=query(A1:B, "Select * where B matches '.*[^aeiou].*'")
These formulas enable you to filter rows based on the presence of vowels or consonants in the text.
Conclusion
In this article, we have explored the Matches regular expression match in Google Sheets Query. By understanding its capabilities and utilizing the provided formula examples, you can handle complex text matching tasks with ease. Google Sheets Query is a powerful tool that allows you to unleash your creativity and achieve advanced substring comparison. If you want to delve deeper into the world of Google Sheets and learn more exciting features, visit Crawlan.com. Happy querying!
