When it comes to finding one substring in another, Microsoft Excel offers users a number of options, including the Find and Replace tool and a few built-in functions like SEARCH and FIND. But why would we use regexes to make the problem more complicated? This is due to the fact that Excel’s built-in capabilities can only handle the exact strings that you supply. Finding all strings that match a pattern requires the use of regular expressions. In this post, I’ll demonstrate how to utilize Excel VBA to find RegEx patterns.
To enable the use of RegEx in Excel, follow the commands below.
- Press Alt+F11 ⇒ Go to the Tools tab of the VBA Editor window ⇒ Click on References.
- From References – VBProject window, mark the Microsoft VBScript Regular Expressions 5.5 checkbox ⇒ Press OK.
Find RegEx Patterns in Excel: 2 Ways
In the following sections, we will see 2 ways to find a specified RegEx pattern in Excel. The following dataset is going to be used.
Here, we have some sentences with email addresses somewhere within them. We will find the email address using RegEx patterns of emails and locate the positions.
1. Find Strings That Match a Pattern with VBA and FIND Function
In the first method, we will use a user-defined VBA function created with the VBA RegEx object to find a substring from a cell that matches a specific pattern, e.g. Email addresses in this example. After then, we will use the FIND function to know the position within the source string.
🔀 Steps:
- First off, press Alt+F11, and a VBA command module will open up.
- From the Insert tab, create a new module.
- Now, copy the following VBA code and paste it into the new module.
Function Find_Pattern(source_string As String, Pat As String, Optional instance_n As Integer = 0, Optional match_n As Boolean = True)
Dim source_string_matches() As String
Dim matchindex As Integer
On Error GoTo ErrHandl
Find_Pattern = ""
Set Xregex = CreateObject("VBScript.RegExp")
Xregex.pattern = Pat
Xregex.Global = True
Xregex.MultiLine = True
If True = match_n Then
Xregex.ignorecase = False
Else
Xregex.ignorecase = True
End If
Set matches = Xregex.Execute(source_string)
If 0 < matches.Count Then
If (0 = instance_n) Then
ReDim source_string_matches(matches.Count - 1, 0)
For matchindex = 0 To matches.Count - 1
source_string_matches(matchindex, 0) matches.Item(matchindex)
Next matchindex
Find_Pattern = source_string_matches
Else
Find_Pattern = matches.Item(instance_n - 1)
End If
End If
Exit Function
ErrHandl:
Find_Pattern = CVErr(xlErrValue)
End Function
Explanation of the Code:
Function Find_Pattern(source_string As String, Pat As String, Optional instance_n As Integer = 0, Optional match_n As Boolean = True)>> In this macro, we have declared a function with required and optional arguments.
Dim source_string_matches() As String
Dim matchindex As Integer>> This declares array variables.
On Error GoTo ErrHandl>> This statement makes the code move to the ErrHandl section when any error is found.
Set regex = CreateObject("VBScript.RegExp")>> This creates an object and stores it in a regex variable.
regex.pattern = Pat
regex.Global = True
regex.MultiLine = True>> These macros are defining the properties of regex.
If True = match_n Then
regex.ignorecase = False
Else
regex.ignorecase = True
End If>> This performs an IF Then Else operation.
Set matches = regex.Execute(source_string)>> This macro stores the value of matches.
If 0 < matches.Count Then
If (0 = instance_n) Then
ReDim source_string_matches(matches.Count - 1, 0)
For matchindex = 0 To matches.Count - 1
source_string_matches(matchindex, 0) = matches.Item(matchindex)
Next matchindex
Find_Pattern = source_string_matches
Else
Find_Pattern = matches.Item(instance_n - 1)
End If
End If>> Two IF Then Else statements are applied here.
- Next, save the file in XLSM format.
- Now, go to an Excel sheet.
- Write a pattern (in our case, an Email Address pattern) in cell C13.
- Then go to cell C5 and write the following formula:
=Find_Pattern(B5,$B$13)- Press CTRL+ENTER and drag the fill handle icon to copy the formula to the rest of the cells.
- So, you will find the email addresses within the main text.
- The code is designed in such a way that, if there is no email address, the UDF will return an empty string.
- If you want to know the location of the email string within the main strings, just add a new column and apply the following formula in cell D5.
=IF(C5<>"",FIND(C5,B5),"Not Found!")
Read More: How to Use REGEX to Match Patterns in Excel
2. Get the Location of the Pattern Directly
Another way to match and locate a pattern is by using the code described in this section. This code will return a #VALUE! error if no match is found. Hence, we will use this UDF together with the IFERROR function.
- The code is as follows:
Function Locate_Pattern(cell As String) As Long
With CreateObject("vbscript.regexp")
.Global = True
.pattern = "([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})"
Locate_Pattern = .Execute(cell)(0).firstindex + 1
End With
End Function
- In this method, you have to specify the pattern within the VBA code.
- To use this code, use the following formula in any suitable cell:
=IFERROR(Locate_Pattern(B5),"Not Found!")
- This means the positions of email addresses found in range B5:B10 are 1, 16, -, 16, 10, 21 from left.
- No email address is there in cell B7.
Read More: How to Find and Replace RegEx Patterns in Excel
Some Common RegEx Patterns to Use in Excel
Here, we have attached some more common RegEx patterns.
- For URL:
Example: https://www.exceldemy.com/
- For Phone Number:
Example: (555) 555-1234
- For Date:
Example: 11/14/2022 (Nov 14, 2022)
Download Practice Workbook
You can download the following practice workbook for self-practice while reading this article.
Conclusion
So, in this article, we have discussed 2 suitable ways to find RegEx patterns in Excel. If this helps you, then leave us a comment.
