How to Find RegEx Patterns in Excel (2 Suitable Methods)

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.

Download Practice Workbook

You can download the following practice workbook for self-practice while reading this article.

2 Ways to Find RegEx Patterns in Excel

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

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

Set regex = CreateObject("VBScript.RegExp")

regex.pattern = Pat
regex.Global = True
regex.MultiLine = True

If True = match_n Then
regex.ignorecase = False
Else
regex.ignorecase = True
End If

Set matches = regex.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

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

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

Read More: How to Use REGEX to Match Patterns in Excel (6 Examples)

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

• We are grateful for this code to this forum: ExcelForum.
• 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:

• 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 (3 Examples)

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)

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. Also, don’t forget to visit our blog for more informative articles on MS Excel. Happy Excelling!

Related Articles

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