Function Problems

How to Filter Excel Rows Using Multiple Wildcard or Regex Criteria

Elise Williams Oct 1, 2026 869 views

Question details

The user needs a method to filter out and potentially delete rows based on multiple complex text patterns and wildcard criteria, specifically when standard VBA arrays fail.

How to Filter Excel Rows Using Multiple Wildcard or Regex Criteria

Product: Excel
Device & OS: not provided
Scenario: Filtering large datasets where values must match one of several specific string patterns, such as values beginning with specific codes.
Observed behavior: Standard VBA wildcard array criteria are restricted and do not process multiple wildcards correctly, requiring the use of regular expressions like REGEXTEST or customized VBA loops.

Before you start

Since filtering out and deleting rows based on advanced string matching can permanently alter your dataset, make a copy of your worksheet before testing any regular expressions or VBA macros.

Solution 1Recommended

Use the REGEXTEST Function for Multiple Criteria

Use the REGEXTEST formula in a helper column to flag rows that match complex patterns, which can then be easily filtered.

The REGEXTEST function (available in supported versions) evaluates text against a regular expression and returns TRUE or FALSE. The pipe character (|) acts as an OR operator, allowing you to test multiple patterns simultaneously.

Enter the regex formula

In an adjacent blank column, enter the formula `=REGEXTEST(A2,"P10101_abc_aws12|P102101_Wer_123w|P103101_asd_123")`. Adjust A2 to point to your target cell.

Match beginning values

If you want to filter anything beginning with a specific code, use the caret (^) symbol in your string, such as `=REGEXTEST(A2,"^P10101")`.

Apply formula to the column

Drag the fill handle down to copy the formula for all rows in your dataset.

Filter the results

Select the header row, go to the 'Data' tab, click 'Filter', and then filter the new helper column to show only TRUE values.

Use the REGEXTEST Function for Multiple Criteria

Testing Patterns: Always test your regular expression string on a small subset of data to ensure the logic accurately captures all intended variations before deleting any filtered rows.

Solution 2

Use VBA with Regular Expressions

Create a custom VBA script utilizing the VBScript_RegExp_55 object to evaluate and delete rows when a standard VBA wildcard array is insufficient.

Advanced Data Filtering

Effortlessly Filter Complex Data with WPS Spreadsheet

WPS Spreadsheet provides powerful filtering tools, advanced text matching, and robust macro compatibility to easily isolate and manage complex datasets.

1. Open your data: Launch WPS Spreadsheet and open your target workbook.
2. Enable AutoFilter: Highlight your dataset, navigate to the 'Data' tab on the ribbon, and click 'AutoFilter'.
3. Apply text filters: Click the filter dropdown arrow on your target column, select 'Text Filters', and choose 'Custom Filter'.
4. Set wildcard criteria: Select 'Begins with' or use wildcards (like P10101*) and use the 'Or' logic to combine up to two complex string matches instantly.

Seamlessly compatible with Microsoft Excel formulas, wildcards, and .xlsx file formats.Powerful built-in Custom AutoFilter supporting multiple wildcard queries.Fully supports VBA/Macros for customized data processing and automated row deletion.Lightweight, fast execution, and a user-friendly interface for easy data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my VBA AutoFilter array work with multiple wildcards?

The standard VBA AutoFilter method only supports a maximum of two wildcard criteria when using an array (e.g., Criteria1:=Array("A*", "B*")). Passing three or more wildcard criteria in the array will cause the filter to fail, requiring a workaround like RegExp or an advanced filter.

What does the caret symbol (^) do in a regular expression?

In regular expressions, the caret (^) is an anchor character that specifies the match must occur exactly at the beginning of the string. For example, '^P101' will match 'P10101' but will ignore 'abc_P101'.

Can I use standard Excel filters to find multiple wildcards without formulas?

Standard AutoFilter lets you use 'Custom Filter' to define up to two criteria using 'Or' logic (e.g., begins with 'P101' OR begins with 'P102'). If you have more than two patterns, you must use Advanced Filter, a formula-based helper column, or a VBA script.

Excel & Data Problems