If you are trying to filter RegEx patterned values in Excel, then this article will serve the purpose. RegEx is the acronym for “Regular Expression”. Generally, RegEx defines a sequence of characters that matches a search pattern in a text string. In this article, we will learn three simple yet efficient steps to filter RegEx patterns in Excel. So, let’s start this article and explore these steps.
Filter Using RegEx in Excel: 3 Simple Steps
In this section of the article, we will learn three easy steps to filter RegEx patterns in Excel. Let’s say we have the Inventory Data of ABC Automobiles as our dataset. In the dataset, we have the Store Location of different cars in different stores. Here, the Serial Number of the cars follow 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 these RegEx patterned Serial Numbers. Now, let’s follow the steps mentioned below to do this.
Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
Step 01: Initiate VBA Editor
In the first step, we need to start the VBA editor. Let’s use the instructions outlined below to do this.
- Firstly, go to the Developer tab from Ribbon.
- After that, select the Visual Basic option from the Code group.
As a result, the Microsoft Visual Basic window will open on your worksheet.
- Now, in the Microsoft Visual Basic window, go to the Insert tab.
- Then, choose the Module option from the drop-down.
Consequently, a blank Module will be created as shown in the following image.
Step 02: Input VBA Code
At this stage, we will write down the VBA code in the newly created Module. Now, let’s use the following steps.
- Firstly, write down 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
- Firstly, 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.
- Then we declared an optional argument named case_match as Boolean inside the function.
- After that, we introduced the result_array variable as Variant.
- Following that, we declared three variables named index_current_row, index_current_column, count_input_rows, count_input_columns as Long.
- Then, we assigned the result_array variable into the regex_match array.
- Next, we used the Set statement to assign the VBScript.RegExp property to the regex variable.
- After that, we assigned the Pattern variable in regex.Pattern, and True value to regex.Global, and regex.MultiLine respectively.
- Then, we used an IF statement to specify ignore cases.
- Following that, we assigned the count of rows and columns into the count_input_rows and count_input_columns variables respectively.
- Then, we redefined the array size of the result_array.
- Afterward, we initiated a For Next loop starting from index_current_column = 1 to count_input_rows.
- Next, we introduced another For Next loop starting from index_current_column = 1 To count_input_columns.
- Subsequently, we assigned the regex.Test value into the result_array.
- After that, we closed both the For Next loops.
- Then, we assigned the result_array into the regex_match variable.
- Next, we defined the error handling parameters.
- Finally, we ended the function.
- After writing the code, click on the Save option as marked in the image below.
Step 03: Apply Newly Created RegEx Function
This is the final step, where we will apply the newly created function to filter regex patterns in Excel.
- Firstly, use the keyboard shortcut ALT + F11 to open the worksheet.
- Then, create a table as marked in the following picture.
- Now, write down the Serial Number based on which you want to filter. In this case, we used 457-AXS-4LPQ9.
- After that, apply the following formula in cell C18.
=FILTER(C5:C15,regex_match(B5:B15,B18))Here, 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
- Firstly, 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}.
- Now, 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”}.
- Now, press ENTER.
Consequently, you will have the following outputs, as demonstrated in the following picture.
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. Please practice it yourself.
Download Practice Workbook
Conclusion
So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to filter RegEx patterns in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment in the section below.
