You can use text functions to manipulate text strings in Excel. However, you can’t use them with regular expressions. As of writing this article VBA is your only option. In this guide, we’re going to show you how to use regular expressions in Excel.

Download Workbook

What is a regular expression?

A regular expression (also known as regex or regexp shortly) is a special text string for specifying a search pattern. They are like wildcards. Instead of specifying the number of characters, you can create patterns to find a specific group of characters, like searching between “b” to “o”, using OR logic, excluding some characters, or repeating values.

Regular expressions are commonly used for text parsing and replacing operations for all programming languages. To use regular expressions in Excel, we will be using VBA.

Pattern Description Samples
^jack begins with “jack” jack-of-all-trades, jack’s house
jack$ ends with “jack” hijack
^jack$ is exactly “jack” jack
colo[u]{0,}r can include “u” at least 0 times colour, color (not colur)
col[o|u]r includes either “o” or “u” color, colur (not colour)
col[^u]r accepts any character except “u” color (not colur or colour)

How to use regular expressions

Let’s start using regular expressions in Excel by opening VBA. Press Alt + F11 keys to open VBA (Visual Basic for Applications) window. Add a module to enter your code.

Next step is to add regular expression reference to VBA. Click Tools > References in the VBA toolbar. Find and check Microsoft VBScript Regular Expressions 5.5 item in the References window. Click OK to add the reference.

How to use regular expressions in Excel 02

Using the VBScript reference, we can create a regular expression object, which is defined as RegExp in VBA. A RegExp object has 4 properties and 3 methods:

Properties

Name Type Description
Global Boolean Set True to find all cases that match with the pattern.
Set False to find the first match.
IgnoreCase Boolean Set True to not make case-sensitive search.
Set False to make case-sensitive search.
Multiline Boolean Set True if your string has multiple lines and you want to perform the search in all lines.
Pattern String The regular expression pattern you want to search.

 

Methods

Name Arguments Description
Execute sourceString As String Returns an array that contains all occurrences of the pattern matched in the string.
Replace sourceString As String replaceVar As Variant Returns a string which all occurrences of the pattern in the string are replaced with the replaceVar string.
Test sourceString As String Returns True if there is a match. Otherwise, False.

Code Samples

A function that returns TRUE/FALSE if the pattern is found in a string

Public Function RegExFind(str As String, pat As String) As Boolean

‘Define the regular expression object

Dim RegEx As New RegExp

‘Set up regular expression properties

With RegEx

.Global = False ‘All occurences are not necessary since a single occurence is enough

.IgnoreCase = True ‘No case-sensitivty

.MultiLine = True ‘Check all lines

.Pattern = pat ‘pattern

End With

RegExFind = RegEx.Test(str)

End Function

How to use regular expressions in Excel 04

After writing the code, you can use this function as a regular Excel function.

A function that replaces the pattern with a given string

Public Function RegExReplace(str As String, pat As String, replaceStr As String) As String

‘Define the regular expression object

Dim RegEx As New RegExp

‘Set up regular expression properties

With RegEx

.Global = False ‘All occurences are not necessary since a single occurence is enough

.IgnoreCase = True ‘No case-sensitivty

.MultiLine = True ‘Check all lines

.Pattern = pat ‘pattern

End With

RegExReplace = RegEx.Replace(str, replaceStr) ‘Return the modified string with replacement value

End Function

How to use regular expressions in Excel 06

The following sample shows how to replace strings that start with “col”, continue with 0 or 1 occurrences of “o” and single “u”, and finally ends with an “r” character with “Color” string.