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.

PatternDescriptionSamples
^jackbegins with "jack"jack-of-all-trades, jack's house
jack$ends with "jack"hijack
^jack$is exactly "jack"jack
colo[u]{0,}rcan include "u" at least 0 timescolour, color (not colur)
col[o|u]rincludes either "o" or "u"color, colur (not colour)
col[^u]raccepts 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

NameTypeDescription
GlobalBooleanSet True to find all cases that match with the pattern.
Set False to find the first match.
IgnoreCaseBooleanSet True to not make case-sensitive search.
Set False to make case-sensitive search.
MultilineBooleanSet True if your string has multiple lines and you want to perform the search in all lines.
PatternStringThe regular expression pattern you want to search.

 

Methods

NameArgumentsDescription
ExecutesourceString As StringReturns an array that contains all occurrences of the pattern matched in the string.
ReplacesourceString As String replaceVar As VariantReturns a string which all occurrences of the pattern in the string are replaced with the replaceVar string.
TestsourceString As StringReturns 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.