Standard data filtering in Google Sheets using the FILTER function is straightforward when checking for exact values, numeric thresholds, or basic wildcard patterns. However, real-world data auditing frequently requires matching complex string structures—such as extracting specific domain emails, validating UK postal codes, isolating SKU codes following strict prefixes, or filtering phone number formats. By pairing FILTER with REGEXMATCH, spreadsheet builders can query datasets using the full power of regular expressions.
Understanding the Syntax of FILTER and REGEXMATCH
To combine these two functions effectively, let us review how each operates individually:
1. The FILTER Function
=FILTER(range, condition1, [condition2, ...])Extracts rows from range that evaluate to TRUE across all specified condition arrays.
2. The REGEXMATCH Function
=REGEXMATCH(text, regular_expression)Evaluates whether a text string contains a match for the specified regular expression, returning TRUE or FALSE.
Combining REGEXMATCH with FILTER
Normally, REGEXMATCH expects a single scalar text cell. However, when passed inside the condition argument of FILTER, Google Sheets evaluates the regular expression across every row in the target column, generating a boolean array that drives the filter.
The standard integration formula looks like this:
=FILTER(A2:D100, REGEXMATCH(B2:B100, "pattern"))Practical Example 1: Filtering Specific Email Domains
Suppose you have user registration records in columns A through C, with email addresses located in column B (range B2:B100). To extract all records for users with .ac.uk or .edu academic email addresses:
=FILTER(A2:C100, REGEXMATCH(B2:B100, "(?i)\.(ac\.uk|edu)$"))Explanation of the pattern:
(?i): Flag enabling case-insensitive matching.\.: Escapes the literal dot.(ac\.uk|edu): Alternation matching either academic extension.$: Anchors the match strictly to the end of the email address.
Practical Example 2: Extracting Valid SKU Code Formats
Consider an inventory table where column A contains product codes. Valid internal codes must begin with two uppercase letters, followed by a hyphen, and end with four digits (e.g., PR-4091, TX-8820). To filter out invalid or malformed SKU entries:
=FILTER(A2:C100, REGEXMATCH(A2:A100, "^[A-Z]{2}-[0-9]{4}$"))Practical Example 3: Multi-Criteria Regex Filtering
You can combine multiple regex criteria using standard logical multiplication (AND) or addition (OR) within FILTER:
=FILTER(A2:D100, REGEXMATCH(B2:B100, "^(UK|EU)-") * (D2:D100 >= 500))This formula returns all records where the region code in Column B starts with UK- or EU- and the order value in Column D equals or exceeds £500.
Common Regex Patterns for Spreadsheet Auditing
| Data Target | Regular Expression Pattern | Example Match |
|---|---|---|
| Valid Email Address | ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$ |
[email protected] |
| UK Postcode Baseline | ^[A-Z]{1,2}[0-9][A-Z0-9]? [0-9][A-Z]{2}$ |
SW1A 1AA, EC1A 1BB |
| Only Numbers / Digits | ^\d+$ |
94021 |
| Contains Word Boundary | (?i)\b(urgent|critical)\b |
"System critical alert" |
Pairing REGEXMATCH with FILTER transforms Google Sheets into a flexible query engine, enabling users to isolate complex text patterns without writing custom Google Apps Script functions.