While working in Microsoft Excel sometimes we need to find specific patterns using regular expressions. But regexes are not supported in Excel. Thus you might face difficulties. Today in this article, I am sharing with you how to count regex with COUNTIF in Excel.
Count Regex with COUNTIF in Excel: Detailed Steps
In the following, I have shared detailed steps to count regex with COUNTIF in Excel.
Step 1: Preparing Dataset for Regex Match
Suppose we have a dataset of some Contact List. Now we will use the COUNTIF function with regex to match in Excel.
As you can see we have a Contact List from which we will check whether it has Phone Number or not in the table.
Step 2: Preparing Customed Helper Function with VBA
In this part, we will use VBA code to custom helper function, the RegExMatch function. Follow the instructions below-
- First, while the workbook is opened press ALT+F11 to open the “Microsoft Visual Basic for Applications” window.
- Second, choose “Module” from the “Insert” option.
- Next, inside the module place the following code and save it-
Public Function RegExMatch(input_reg_range As Range, reg_pattern As String, Optional reg_match As Boolean = True) As Variant
Dim WX() As Variant
Dim Input_Row, Input_Col, cnt_Rows, cnt_Cols As Long
On Error GoTo ErrHandl
RegExMatch = WX
Set Wregex = CreateObject("VBScript.RegExp")
Wregex.pattern = reg_pattern
Wregex.Global = True
Wregex.MultiLine = True
If True = reg_match Then
Wregex.ignorecase = False
Else
Wregex.ignorecase = True
End If
cnt_Rows = input_reg_range.Rows.Count
cnt_Cols = input_reg_range.Columns.Count
ReDim WX(1 To cnt_Rows, 1 To cnt_Cols)
For Input_Row = 1 To cnt_Rows
For Input_Col = 1 To cnt_Cols
WX(Input_Row, Input_Col) = Wregex.Test(input_reg_range.Cells(Input_Row, Input_Col).Value)
Next
Next
RegExMatch = WX
Exit Function
ErrHandl:
RegExMatch = CVErr(xlErrValue)
End Function
- After that, we will place the following pattern inside the worksheet as regex is not supported in Excel-
Read More: How to Find RegEx Patterns in Excel
Step 3: Using Formula to Find Regex and Count
As we have established the RegExMatch function, this time we will apply the RegExMatch function to find a proper match from a string.
- Hence, choose a cell (C7) and apply the below formula down-
=RegExMatch(B7,$C$4)Here, the RegExMatch function matches whether any part of the string matches with the given expressions.
- Simply, click ENTER to get the output.
- Now, pull the “Fill Handle” down to fill the column.
- Thereafter, let’s count cells with phone numbers with the below formula in cell (C15)-
=COUNTIF(C7:C13,TRUE)Here, the COUNTIF function is used to count cells with the given condition “TRUE” from the given range “C7:C13”.
- Finally, hit ENTER key from the keyboard to get the result.
- Here, we have successfully extracted the output with the COUNTIF function and regex in Excel.
Read More: How to Perform Pattern Matching in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover almost all the methods to COUNTIF regex in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.
