In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are more often used.
Refer to the following table to see what all the major topics going to be covered in this article.

Index : Topics covered under this Article

1. What is Regex

2. How to use RegEx in Excel VBA

3. VBA to validate a string against a RegEx pattern

4. VBA to Search based on a RegEx pattern

5. VBA to replace strings with specific string which matches with given RegEx pattern

6. Usage of RegEx in Excel VBA

7. 13 most commonly used RegEx Patterns

8. Regex Tester – Free Excel Tool Download

What is RegEx – Regular Expression ?

RegEx is basically short form of Regular Expression. It is also called as RegExp. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.

These are very useful in many ways…few of them are here…

1. Wild Search: You can search a specific types of string which follows a specific pattern rather than providing the exact word to search.
2. Validation: You can validate any input or output – if it has a specific pattern
3. Extraction: Extract specific kind of words or phrases which follows specific pattern.
….
To know more about Regular Expressions, you can also read this Wiki page – https://en.wikipedia.org/wiki/Regular_expression

How to use RegEx in Excel VBA

VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use.

To use this built-in feature, you can use early binding you need to add reference in your VBA project – VBScript.RegExp
or else you can create Object of type VBScript.RegExp before you use it.

Methods of VBScript.RegEx using in VBA

It provides basically three main methods:
1. .Execute
2. .Replace
3. .Test

1# RegEx Execute Method in VBA

– In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.

Function executeMethodRegEx(regPattern As String, regString As String)
'***************************************************************
' Example of Execute Method of RegEx
' Function to retrieve all the matches
' found in a given string  which matches
' the regex Pattern string
' Note: Returns an object holding all the matches
' found like an array.
'***************************************************************
Dim rgx As Object
Set rgx = CreateObject("VBScript.RegExp")
Dim allMatches As Object
With rgx
    .Pattern = regPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With

' AllMatches object stores all the matches
' returned by execute method of RegEx
Set allMatches = rgx.Execute(regString)

'Loop to read all the matches found
For Each Item In allMatches
    Debug.Print Item.Value
Next
End Function

2# RegEx Replace Method in VBA

– Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.

Function rePlaceMethodRegEx(regPattern As String, origString As String)
'***************************************************************
' Example of Replace Method of RegEx
' Function to replace all the matches
' found in a given string  which matches
' the regex Pattern - with a given string
' Note: It return a new string where all the matches
' are replace with the provided string
'***************************************************************
Dim rgx As Object
Set rgx = CreateObject("VBScript.RegExp")

With rgx
    .Pattern = regPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With

' Replace method returns a new string
' with all the matched string replaced
' with your provided string
Debug.Print rgx.Replace(origString, "***abc@email.com***")
End Function

3# RegEx Test Method in VBA

– This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.

Function testMethodRegEx(regPattern As String, regString As String) As Boolean
'***************************************************************
' Example of Test Method of RegEx
' Function to check if Provided String
' matches with the regex pattern provided
' Note: It return as boolean - True or False
'***************************************************************
    Dim rgx As Object
    Set rgx = CreateObject("VBScript.RegExp")
    With rgx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = regPattern
    End With
    ' Function returns a boolean - true/false
    ' based on match or no match
    testMethodRegEx = rgx.test(regString)
End Function

Usage of RegEx in Excel VBA

Though as you must have already got an idea that this can be used in many ways in Excel VBA.
Here are some quite obvious usage of RegEx in Excel VBA:

Usage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text

Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.

Function listEmailIDsAndPhoneNumbers(regString As String)
'***************************************************************
' Combined two patterns with pipe sign (|) = OR
' Pattern1 = To validate Telephone number with Country code
' Pattern2 = Email ID
' This function returns the string if any one
' of the pattern matches
'***************************************************************
Dim rgx As Object
Set rgx = CreateObject("VBScript.RegExp")
Dim allMatches As Object
Dim emailRegPattern As String
Dim phoneNumberRegPattern As String
emailRegPattern = "([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.])"
phoneNumberRegPattern = "([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})"

With rgx
    .Pattern = phoneNumberRegPattern & "|" & emailRegPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With

' All emailIDs adn telephone numbers would be
' listed in this Object
Set allMatches = rgx.Execute(regString)

'Loop to read all the matches found
For Each Item In allMatches
    Debug.Print Item.Value
Next
End Function

Usage 2# Restrict user to type a valid format of email ID in your Excel Form

For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.

In this example, I have put this code in TextBox Change Event.
As soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.

Private Sub txtEmailID_Change()
    If Not (testMethodRegEx("^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.])", txtEmailID.Text)) Then
        txtEmailID.BackColor = RGB(255, 0, 0)
        Else
        txtEmailID.BackColor = RGB(0, 255, 0)
    End If
End Sub

Usage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet

This is an example code. You can always change the RegEx pattern etc. according to your need.
In following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc.

Function maskEmailIDsAndPhoneNumbers(origString As String) As String
'***************************************************************
' Combined two patterns with pipe sign (|) = OR
' Pattern1 = To validate Telephone number with Country code
' Pattern2 = Email ID
' This function returns the string if any one
' of the pattern matches
'***************************************************************
Dim rgx As Object
Set rgx = CreateObject("VBScript.RegExp")
Dim allMatches As Object
Dim emailRegPattern As String
Dim phoneNumberRegPattern As String
emailRegPattern = "([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.])"
phoneNumberRegPattern = "([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})"

With rgx
    .Pattern = phoneNumberRegPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    origString = .Replace(origString, "+11-111111111")
End With

With rgx
    .Pattern = emailRegPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    origString = .Replace(origString, "xyz@email.com")
End With
Set rgx = Nothing
' return the masked string
maskEmailIDsAndPhoneNumbers = origString

End Function

13 most commonly used RegEx Patterns:

I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.

Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file.

1. Email Validation RegEx

^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$

2. Password validation:

Strong Password: at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long

^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\$%\^&\*])(?=.{8,})

Medium password: One capital, one small letter and one numeric field and total 6 chars long

^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))

Simple password: [alphanumeric]

At least one alphabet and one numeric field and total 5 chars long

^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))

4. RegEx Pattern for Alphanumeric Only

^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))

5. RegEx Pattern for Website URL

(https?:\/\/(?:www\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\.[^\s]{2,}|www\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\.[^\s]{2,}|https?:\/\/(?:www\.|(?!www))[a-zA-Z0-9]\.[^\s]{2,}|www\.[a-zA-Z0-9]\.[^\s]{2,})

6. RegEx Pattern for Positive numbers

^([+]?\d+([.]\d+))

7. RegEx Pattern for Negative numbers

^(\-\d+([.]?\d+))

8. RegEx Pattern for 10 digit telephone number [not starting with Zero]

[1-9]{1}[0-9]{9}$

9. RegEx Pattern for Phone with a country code format

[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$

10. RegEx Pattern for Year between 1900 and 2018

(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$

11. RegEx Pattern for Date format : DD/mm/yyyy

^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\/(0[1-9]|[1-9]|1[0-2])\/([12]\d{3}))

12. RegEx Pattern for NL Postal Code

\d{4}[ ]*([aA-zZ]{2})$

13. RegEx Pattern for US Postal Code format

^\d{5}(?:[-\s]\d{4})?$