Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts 