How to Filter Using RegEx in Excel (with Simple Steps)

We have the Store Location of different cars in different stores. The Serial Number follows a RegEx pattern but there are some cars that have the same Serial Number. We will create a user-defined function using the VBA Macro feature of Excel to filter the RegEx-patterned Serial Numbers.

Step 1 – Initiate the VBA Editor

Go to the Developer tab.
Select the Visual Basic option from the Code group.

The Microsoft Visual Basic window will open on your worksheet.

Go to the Insert tab.
Choose the Module option from the drop-down.

A blank Module will be created as shown in the following image.

Step 2 – Input VBA Code

Insert the following code in the blank Module.

Public Function regex_match(range_of_input As Range, Pattern As String, _
Optional case_match As Boolean = True) As Variant
Dim result_array() As Variant
Dim index_current_row, index_current_column, count_input_rows, _
count_input_columns As Long
On Error GoTo ErrHandl
regex_match = result_array
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = Pattern
regex.Global = True
regex.MultiLine = True
If True = case_match Then
regex.ignorecase = False
Else
regex.ignorecase = True
End If
count_input_rows = range_of_input.Rows.Count
count_input_columns = range_of_input.Columns.Count
ReDim result_array(1 To count_input_rows, 1 To count_input_columns)
For index_current_row = 1 To count_input_rows
For index_current_column = 1 To count_input_columns
result_array(index_current_row, index_current_column) = _
regex.Test(range_of_input.Cells(index_current_row, index_current_column).Value)
Next
Next
regex_match = result_array
Exit Function
ErrHandl:
regex_match = CVErr(xlErrValue)
End Function

Code Breakdown

We initiated a function named regex_match and declared its output as Variant.
Inside the function arguments, we declared two variables named range_of_input as Range, and Pattern as String.
We declared an optional argument named case_match as Boolean inside the function.
We introduced the result_array variable as Variant.
We declared three variables named index_current_row, index_current_column, count_input_rows, count_input_columns as Long.
We assigned the result_array variable into the regex_match array.
We used the Set statement to assign the VBScript.RegExp property to the regex variable.
We assigned the Pattern variable in regex.Pattern, and True value to regex.Global, and regex.MultiLine, respectively.
We used an IF statement to specify ignore cases.
We assigned the count of rows and columns into the count_input_rows and count_input_columns variables respectively.
We redefined the array size of the result_array.
We initiated a For Next loop starting from index_current_column = 1 to count_input_rows.
We introduced another For Next loop starting from index_current_column = 1 To count_input_columns.
We assigned the regex.Test value into the result_array.
We closed both the For Next loops.
We assigned the result_array into the regex_match variable.
We finished with some error handling parameters.

Click on the Save option.

Step 3 – Apply the Created RegEx Function

Use the keyboard shortcut ALT + F11 to open the worksheet.
Create a table as marked in the following picture.

Insert the Serial Number based on which you want to filter. We used 457-AXS-4LPQ9.

Apply the following formula in cell C18.

=FILTER(C5:C15,regex_match(B5:B15,B18))

The range of cells C5:C15 indicates the cells of the Store Location column, the range B5:B15 refers to the cells of the Serial Number column, and cell B18 represents the selected Serial Number.

Formula Breakdown

The regex_match function will return the relative positions of the specified lookup value.
- Output → {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}.
In the FILTER function:
- C5:C15 → It is the array argument.
- regex_match(B5:B15,B18) → This indicates the include argument.
- Output → {“Texas”;”Utah”;”Montana”;”Utah”}.
Press ENTER.