Even though Excel’s built-in functions are excellent, there are moments when you need more advanced tools to handle multiple criteria and complex operations within large datasets.
Welcome Regex, a powerful pattern-matching language. And when it is combined with COUNTIF, it unlocks new possibilities.
This combination is beneficial when counting occurrences based on specific patterns to track inventory or evaluate sales performance without manually sorting through the entire dataset.
In this article, I’ll explain how to enable and create a regex function and then use it to find regular expressions with COUNTIF in Excel with easy-to-understand examples.
Understanding Regex and COUNTIF
Regular Expressions (regex) and COUNTIF are both powerful tools used in different contexts to search and manipulate data.
Regular Expressions, commonly known as regex or regexp, are a sequence of characters that define a search pattern. You use these patterns to process text and manipulate data. Regex is handy for tasks like data validation, parsing, and replacing text with predefined rules.
On the other hand, COUNTIF is a function in Microsoft Excel and Google Sheets. It counts the number of cells within a specified range that meet a given condition.
However, by integrating regexp with COUNTIF, you can extend its capabilities to handle more complex scenarios.
You can define flexible and complex search patterns with regular expressions and then use COUNTIF to count cells based on more sophisticated conditions.
So, when you’re dealing with datasets that require pattern-based matching instead of corresponding to the exact value, the combination of COUNTIF and regex is the ideal solution.
How to Count Regex Matches with COUNTIF in Excel
To get the occurrences regexp substring matches with the COUNTIF function in Excel, you need to utilize the helper function called RegExMatch, which returns a boolean value indicating whether a cell’s content matches a given regular expression.
Then, you can nest this function within COUNTIF to count the cells where the regular expression is true.
Adjust the regular expression according to your specific pattern-matching criteria.
For more details, check out the instructions below to tally the number of regular expressions using =COUNTIF().
Here are the steps to count regex matches with COUNTIF in Excel:
1. Prepare Your Data
Ensure that your data is organized to facilitate easy analysis in Excel. Each column should represent a category or field, and each row should contain different data.
Additionally, identify the column or field where you want to apply the regex and count the occurrences. This column should contain the text strings you want to analyze using the regex pattern.
2. Enable Regular Expressions
Excel, by default, doesn’t support regexp in its functions, including COUNTIF. So, to use regular expressions, you’ll need to enable them first from the VB References.
Here’s the process to enable regex in Microsoft Excel:
- Create an Excel Macro-Enabled Workbook (*.xlsm) file.
- Select File from the top left corner.
- Choose Options from the left menu.
- Go to the Customize Ribbon tab.
- Check the Developer option and click OK.
- Proceed to the Developer tab from the top bar and select Visual Basic.
- Select Tools > References on the VBA window.
- Check the Microsoft VBScript Regular Expressions option and click OK.
3. Construct the Regex Function Module
Now that you have turned on regexp in the spreadsheet, you can create modules with RegExMatch functions in the Microsoft Visual Basic for Applications tool.
Here’s how to construct a regex function module:
- Select Insert > Module from the VBA window.
- Enter the following function in the module and save it:
Public Function RegExMatch(input_reg_range As Range, reg_pattern As String, Optional reg_match As Boolean = True) As Variant
On Error Resume Next
Dim Wregex As Object
Set Wregex = CreateObject("VBScript.RegExp")
With Wregex
.pattern = reg_pattern
.Global = True
.MultiLine = True
.IgnoreCase = Not reg_match
End With
Dim cnt_Rows As Long, cnt_Cols As Long
cnt_Rows = input_reg_range.Rows.Count
cnt_Cols = input_reg_range.Columns.Count
Dim WX() As Variant
ReDim WX(1 To cnt_Rows, 1 To cnt_Cols)
Dim Input_Row As Long, Input_Col As Long
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 Input_Col
Next Input_Row
RegExMatch = IIf(Err.Number <> 0, CVErr(xlErrValue), WX)
On Error GoTo 0
End Function
The VBA code above code defines a function named RegExMatch that takes three parameters — input_reg_range (a range of cells), reg_pattern (a regular expression pattern), and an optional parameter reg_match (a boolean indicating whether to perform a case-sensitive match).
Here’s a breakdown of the code:
Error Handling
On Error Resume NextThis line instructs VBA to continue executing the code even if an error occurs, and it also sets up the code to check for errors using the Err object.
Create Regular Expression Object
Dim Wregex As Object
Set Wregex = CreateObject("VBScript.RegExp")This section creates a regular expression object, Wregex, using the VBScript.RegExp class.
Configure Regexp Object
With Wregex
.pattern = reg_pattern
.Global = True
.MultiLine = True
.IgnoreCase = Not reg_match
End WithHere, the properties of the regular expression object are set based on the input parameters.
The pattern is set to the provided regex, and the Global, MultiLine, and IgnoreCase properties are configured accordingly.
Process Input Range
Dim cnt_Rows As Long, cnt_Cols As Long
cnt_Rows = input_reg_range.Rows.Count
cnt_Cols = input_reg_range.Columns.CountThese lines determine the number of rows and columns in the input range.
Initialize Array for Results
Dim WX() As Variant
ReDim WX(1 To cnt_Rows, 1 To cnt_Cols)An array, WX, is initialized to store the results of the regular expression matching for each cell in the input range.
Loop Through Input Range
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 Input_Col
Next Input_RowThese nested loops iterate through each cell in the input range and use the Test method to check if the cell value matches partial strings/substrings with regular expressions. The results are stored in the WX array.
Handle Errors and Return Result
RegExMatch = IIf(Err.Number <> 0, CVErr(xlErrValue), WX)This line checks if an error occurred during the execution. If it did, it returns a #VALUE! Error in Excel. Otherwise, it returns the WX array containing the results of the regex matching.
Reset Error Handling
On Error GoTo 0This line resets the error handling to its default state. It allows any subsequent errors to interrupt the code execution.
4. Understand the COUNTIF Function
Before incorporating regular expressions, make sure you understand the basic usage of the =COUNTIF() function. As I’ve said before, COUNTIF counts the number of cells in a range that fulfill your specified criteria.
The syntax of the COUNTIF function is as follows:
=COUNTIF(range, criteria)
| Argument | Description |
|---|---|
| range | The range of cells you want to count. |
| criteria | The condition that must be met for a cell to be counted. |
After enabling regular expressions, preparing your data, and understanding the COUNTIF function, you can finally start counting the regex matches with COUNTIF in Excel.
You can also use SUMPRODUCT and COUNTIF with multiple criteria to count cells containing specific text regular expressions in Excel without VBA.
Simply use the following formula in the COUNTIF function:
=SUMPRODUCT(–(ISNUMBER(SEARCH(regex_pattern, range))))
Here, regex_pattern is your regular expression, and range is the range of cells you want to search within.
This formula returns the count of cells in the specified range that corresponds to the given regular expression. The SEARCH function checks for the regexp in each cell, and ISNUMBER converts the result into a Boolean array.
The double negative (–) is used to convert TRUE and FALSE to 1s and 0s, and SUMPRODUCT adds up these values to give the final count of regex matches.
Now, go to the section below, where I’ve provided practical examples to count the occurrences of regular expressions using =COUNTIF().
Examples of Using COUNTIF to Find Regexp in Excel
The examples I’ve described here will provide practical insights into the syntax and application of regex within the COUNTIF function. By showcasing real-world scenarios, these examples illustrate the versatility of COUNTIF with regex.
Here are examples of using COUNTIF to find regexp in Excel:
Count the Occurrences of Valid Email Addresses
Let’s create an Excel dataset with names and email addresses and then use regex and COUNTIF to count the number of proper email addresses.
In cell A1, enter “Name,” and in cell B1, enter “Email.” Then, fill in some sample data from A2:A6 and B2:B6.
Now, let’s use regex to identify valid email addresses. I’ll assume a simple pattern for emails: anystring@anystring.anystring.
In an empty cell, in this example, cell B10, enter the following regular expression to search for legitimate email addresses:
^[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}$
Now, create a new column labeled “Valid Email Address” in C1 and enter the following regexp formula in the C2 cell:
=RegExMatch(B2,$B$10)
Drag the formula down for the other cells in column C to apply the validation to the entire dataset.
This formula uses a regex pattern to check if the email is valid. If it is, it returns TRUE; otherwise, it returns FALSE.
Now, let’s use the COUNTIF function to count the valid email addresses.
In a new cell, B9, enter the following formula:
=COUNTIF(C2:C6, TRUE)
This formula counts the number of cells in column C with a TRUE value, indicating a legitimate email address.
In my example, the COUNTIF formula will return 4, as there are four TRUE email addresses.
Count the Occurrences of Valid Phone Numbers
Let’s create an Excel dataset and use the regexp function to check whether a phone number is legitimate, then apply =COUNTIF() to determine how many valid phone numbers are available in the spreadsheet.
For this example, I assume that a proper mobile no. is in the format XXX-XXX-XXXX, where X is a digit.
Make an Excel workbook where column A contains the names of individuals in cells A2:A6 and column B has their phone numbers in cells B2:B6.
In an empty cell, in this example, cell B10, enter the following regex to search for valid mobile numbers:
(\(\d{3}\)|\d{3})[-\.\s]?\d{3}[-\.\s]?\d{4}\b
Now, create a new column, name it “Valid Phone Number” in C1, and enter the following regexp formula in the C2 cell:
=RegExMatch(B2,$B$10)
This formula checks if the value in cell C2 matches the pattern of a valid phone number. If it does, it returns TRUE; otherwise, it returns FALSE.
Now, let’s use the COUNTIF function to count valid phone numbers. In a cell, let’s say B9, enter the following formula:
=COUNTIF(C2:C6, TRUE)
This formula counts the number of cells in column C where the value is TRUE. The result will be the count of legitimate phone numbers in your dataset.
In this example, the result should be four since there are four valid phone numbers and one invalid.
Final Thought
As you can see, by blending the versatility of regular expressions with COUNTIF, you’ll be able to tackle intricate data challenges. You can now isolate and quantify occurrences that align with your specified criteria.
