About the REGEXMATCH Function
Overview of REGEXMATCH
| Pattern Matching Using Regular ExpressionsGoogle Sheets Function | ||
|
=REGEXMATCH( text, regex ) Summary The REGEXMATCH function determines whether a given text matches a specified regular expression. It returns TRUE if the text matches, and FALSE otherwise. |
||
|
When to Use REGEXMATCH
- To check if a string matches a specific pattern (e.g., email or date format).
- To extract only data that meets certain conditions.
- To validate the format of input during data entry.
How to Use REGEXMATCH
The following table demonstrates the basic usage of the REGEXMATCH function.
| A | B | |
|---|---|---|
| 1 | Text | Matches? |
| 2 | hello123 | =REGEXMATCH(A2, “[0-9]+”) |
| 3 | abcdef | =REGEXMATCH(A3, “[0-9]+”) |
Results
- In cell B2, the string hello123 contains numbers, so TRUE is returned.
- In cell B3, the string abcdef does not contain numbers, so FALSE is returned.
Regular Expressions Reference
Google Sheets Regular Expressions: Examples with REGEXREPLACE Function
By using the REGEXREPLACE function in Google Sheets, you can easily perform text replacement based on regular expressions. This article explains nearly all comm...
programming-cafe.com
Advanced Examples
REGEXMATCH can be used for more sophisticated condition matching.
Example 1: Validate Email Format
Check if a string is in email format.
| A | B | |
|---|---|---|
| 1 | Text | Is Email? |
| 2 | user@example.com | =REGEXMATCH(A2, “^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$”) |
| 3 | not_an_email | =REGEXMATCH(A3, “^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$”) |
- In cell B2, TRUE is returned, while in cell B3, FALSE is returned.
Example 2: Extract Matching Data
Extract only the data that matches a specific condition.
| A | B | |
|---|---|---|
| 1 | Data | Matches? |
| 2 | Item-123 | =REGEXMATCH(A2, “Item-[0-9]+”) |
| 3 | Data-XYZ | =REGEXMATCH(A3, “Item-[0-9]+”) |
- In cell B2, TRUE is returned, while in cell B3, FALSE is returned.
Points to Note
- If the text does not match the regex, FALSE is returned.
- Ensure the regex syntax is correct to avoid errors.
- For case-insensitive matching, add
(?i)to the regex.
Conclusion
- The REGEXMATCH function is an extremely useful tool for determining if text matches a specific pattern.
- It can be utilized for data validation and conditional processing.
- When combined with other functions, it enables flexible and advanced data handling.