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.

excel regex filter


Step 1 – Initiate the VBA Editor

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

Initiate VBA Editor to filter RegEx patterns in Excel

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.

Final output of step 01 to filter RegEx patterns in Excel


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

Input VBA Code to filter RegEx patterns in Excel

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_rowscount_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.

Apply Newly Created RegEx Function to filter RegEx patterns in Excel

  • 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.

Here’s the output.

Final output of step 03 to filter RegEx patterns in Excel

Read More: How to Use REGEX to Match Patterns in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet so you can test the code.

Practice section to filter RegEx patterns in Excel


Download the Practice Workbook


Related Articles


<< Go Back to RegEx in Excel | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo