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

Get FREE Advanced Excel Exercises with Solutions!

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.

how to enable regex in excel

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.

Enabling RegEx in Excel

  • From References – VBProject window, mark the Microsoft VBScript Regular Expressions 5.5 checkbox ⇒ Press OK.

The Microsoft VBScript Regular Expressions 5.5 checkbox marked from References - VBProject window.


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.

Creating 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

Find Strings That Match a Pattern with VBA

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.
([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})
  • 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.

Find Strings That Match a Pattern with VBA and FIND Function

  • 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

VBA code to find regex pattern in Excel

  • 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!")

Locating RegEx pattern of email in Excel

  • 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:
(https?)://(www)?.?(\\w+).(\\w+)/?(\\w+)?

Example: https://www.exceldemy.com/

  • For Phone Number:
.?(\\d{3}).*(\\d{3}).*(\\d{4})

Example: (555) 555-1234

  • For Date:
(\\d{2}).(\\d{2}).(\\d{4})

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Masum Mahdy
Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo