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

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample dataset of COUNTIF Regex in Excel


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-
(\(\d{3}\)|\d{3})[-\.\s]?\d{3}[-\.\s]?\d{4}\b

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.

Using Formula to Find Regex and Count to COUNTIF Regex in Excel

  • Simply, click ENTER to get the output.

  • Now, pull the “Fill Handle” down to fill the column.

Using Formula to Find Regex and Count to COUNTIF Regex in Excel

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


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo