How to Count Regex with COUNTIF in Excel (with Easy Steps)

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Detailed Steps to Count Regex with COUNTIF in Excel

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 (2 Suitable Methods)

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-

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)-

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 (2 Suitable Methods)

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. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.

Related Articles

• How to Filter Using RegEx in Excel (with Simple Steps)
• Data Validation with RegEx in Excel (With Easy Steps)
• How to Find and Replace RegEx Patterns in Excel (3 Examples)
• Find & Replace Text Using Regex in Excel
• How to Use REGEX without VBA in Excel (with Easy Steps)
• Use REGEX to Match Patterns in Excel (6 Examples)