RegEx in Excel (For Matching, Extracting and Replacing)

Here’s an example of using a User-Defined Function to apply RegEx in Excel.

Download the Practice Workbook

What Is RegEx in Excel?

RegEx stands for Regular Expression. It refers to a series of characters that form a search pattern. Numerous programming languages, text editors, and tools make extensive use of regular expressions for operations like searching, validating, extracting data, and manipulating text.

How to Use RegEx Functions in Excel

Unfortunately, there is no RegEx function in Excel, but there is a library called Microsoft VBScript Regular Expression 5.5. Using this library, we can write custom or User-defined functions that can work with RegEx.

• Open the VBA Editor window by clicking Alt + F11, then go to Tools and choose References to open the reference library.

• Check Microsoft VBScript Regular Expressions 5.5 Reference and click OK.

How to Match Text Using Regex in Excel

To search for a specific pattern using RegEx, we created a custom function named RegEx_Match.

• Insert the following code in the Visual Basic Editor in Excel.

Function RegEx_Match( _
ByVal My_Text As Variant, _
ByVal Text_Pattern As Variant, _
Optional IfMatched As Variant = "Matched", _
Optional IFUnMatched As Variant = "Unmatched", _
Optional CaseMatch As Boolean = True)
Dim regEX As New RegExp
On Error GoTo ErrHndlr

    If TypeName(My_Text) = "Range" Then
        My_Text = My_Text.Value
    End If
    If TypeName(Text_Pattern) = "Range" Then
        Text_Pattern = Text_Pattern.Value
    End If
    
    If Text_Pattern <> "" Then
        With regEX
            .Global = True
            .MultiLine = True
            .pattern = Text_Pattern
            If CaseMatch = True Then
                .IgnoreCase = False
            Else
                .IgnoreCase = True
            End If
        End With
        
        If regEX.Test(My_Text) Then
        RegEx_Match = IfMatched
        Else
            RegEx_Match = IFUnMatched
        End If
    Else
    RegEx_Match = "Invalid Pattern"
    End If
    Exit Function
    
ErrHndlr:
    RegEx_Match = "Value Error"
End Function

Function RegEx_Match(ByVal My_Text As Variant,ByVal Text_Pattern As Variant,Optional IfMatched As Variant = "Matched",Optional IFUnMatched As Variant = "Unmatched",Optional CaseMatch As Boolean = True)

Dim regEX As New RegExp

On Error GoTo ErrHndlr

If TypeName(My_Text) = "Range" Then
My_Text = My_Text.Value
End If
If TypeName(Text_Pattern) = "Range" Then
Text_Pattern = Text_Pattern.Value
End If

If Text_Pattern <> "" Then
    With regEX
        .Global = True
        .MultiLine = True
        .pattern = Text_Pattern
        If CaseMatch = True Then
            .IgnoreCase = False
        Else
            .IgnoreCase = True
        End If
    End With

        If regEX.Test(My_Text) Then
        RegEx_Match = IfMatched
        Else
            RegEx_Match = IFUnMatched
        End If

 Else
    RegEx_Match = "Invalid Pattern"
    End If
    Exit Function

ErrHndlr:
    RegEx_Match = "Value Error"
End Function

Now, we will apply the RegEx_Match function on nine different occasions. Let’s explore them one by one.

How to Use RegEx in Excel

Here are a few ways to use RegEx in Excel:

1. Matching Valid Email Addresses
2. Matching SKU Codes
3. Checking Social Security Number
4. Validating Phone Numbers
5. Applying Excel IF Function with RegEx

Method 1 – Use RegEx for Matching Valid Email Addresses in Excel

• A mail address usually starts with alphanumeric characters, which is the local part, then there is a @ sign and finally a dot(.) and the top-level domain (TDL) at the end. Hence, the RegEx of an email address can be written as ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$. Here,
  • ^ states the starting point of the string.
  • [a-zA-Z0-9._%+-]+ matches single or multiple instances of an alphanumeric character, dot (.), underscore (_), percent (%), plus (+), or hyphen (–). This section represents the part of the email address that comes before the @
  • @ matches the literal @
  • [a-zA-Z0-9.-]+ matches single or multiple instances of any alphanumeric character, dot (.), or hyphen (–). This part refers to the email address’s domain name.
  • \. corresponds to the literal dot (.) character. Because dot (.) is a special metacharacter in RegEx, it must be escaped with a backslash (\).
  • [a-zA-Z]{2,} matches two or more occurrences of any alphabetic character. This part represents the email address’s top-level domain (TLD).
  • $ asserts the end of the string.
• In the following dataset, we’ll verify whether cell C5 contains the RegEx of a valid email address (C16).
• Use the following formula to detect valid email addresses:

We set the IfMatched argument to “Valid” so that when the address matches with the RegEx, it results in “Valid”. For the same reason,  we set the IfUnmatched argument to “Invalid”.

Read More: Use REGEX without VBA

Method 2 – Use RegEx for Matching SKU Codes

We have a bunch of SKU codes for products in a shop. Every category item has its own SKU pattern. By matching a specific SKU pattern, we can determine which category a product is in.

• We need to identify the codes that match a certain pattern, COM-\d{3}-[A-Z]{3}$ which is the SKU pattern of the Computer category.
• Use the following formula:

The bottom two SKU codes do not match the RegEx, hence we get the “Unmatched” result.

Read More: Perform Pattern Matching in Excel

Method 3 – Use RegEx for Checking Social Security Numbers

The social security number in the U.S. has a unique pattern that contains nine numbers.

• The RegEx pattern of an SSN is \d{3}-\d{2}-\d{4}$.
• To verify whether a given number is actually a valid SSN or not, we can use our custom function RegEx_Match (see above).
• In the dataset below, we checked whether the given numbers are of SSN pattern.
• The SSN RegEx pattern is in cell C16. We have used the following formula to verify the pattern in cell C5:

Here we can see that the numbers in cells C8 and C10 are not valid SSNs. So, we got the result “Unmatched”.

Method 4 – Validate Phone Numbers Using Regex

Every country has its own unique country code for phone numbers within that country.

• Let’s assume the phone number in the USA starts with 555. We can use the RegEx ^\(555\) \d{3}-\d{4}$ to check whether a number is from the USA.
• We verified the number in cell C5 using the RegEx in cell C16 with the following formula:

Read More: Use REGEX to Match Patterns

Method 5 – Combine Excel IF Function with RegEx

• In the dataset below, we have a list of people with their respective addresses.
• We need to find out who is from outside the state of Texas.
• We can combine the IF function with our user-defined RegEx_Match function.
• The RegEx in this context is ^(?!.*Texas).*$.
• The combined formula is as follows:

When the RegEx_Match function returns “Matched”, the IF function will return “Yes” indicating that the address is from Texas. Otherwise, the IF function returns “No”.

Read More: How to Find RegEx Patterns in Excel

Method 6 – Use RegEx to Lookup Unmatching String

• In the dataset below, we’ll find people who don’t live in Texas using the IfMatched and IfUnmatched arguments.
• The formula that we have used in this case is as follows:

Here, we set the IfMatched argument to “No” so that when the address matches with the RegEx, it results in “No”. For the same reason, we set the IfUnmatched argument to “Yes”.

Method 7 – Use the COUNTIF Function with RegEx

• We have calculated how many people were from outside Texas.
• We used the following formula:

As there were a total of 7 “Yes” in the D5:D14 range, we got the result 7.

Method 8 – Use RegEx to Lookup Unmatching Character

• The RegEx ^[^\+]*$ matches any strings that don’t have the “+” at the beginning.
• Using this RegEx, we can verify whether a phone number contains the Country Code or not as a phone number with a country code will always start with a “+” sign.
• We have used the following formula to verify phone numbers that don’t begin with a + sign:

The strings that don’t have the “+” sign at the beginning resulted in “No” as expected.

Method 9 – Perform Case-Insensitive Matching with RegEx

Our custom function RegEx_Match is by default a case-sensitive function, as the optional argument CaseMatch has the default value of “TRUE”. But if we assign “FALSE” to the CaseMatch argument, it will perform Case Insensitive matching.

• In the dataset of the second method, we altered the case of a character in the first Product SKU cell.
• By keeping the same RegEx, we used the following formula to perform case-insensitive matching:

We can see that the result for the first cell comes out to be “No” as the SKU is not in the same case as compared to the RegEx.

How to Extract Strings Using RegEx in Excel

To extract the matched portion, we write another VBA custom function named RegExtract.

• The VBA code for this function is as follows:

Function RegExtract(My_Text As Variant, _
Text_Pattern As Variant, _
Optional Inst_Num As Variant = 0, _
Optional CaseMatch As Boolean = True)
Dim regEX As New RegExp
Dim Txt_Match_Array() As String
Dim match_indx_num As Integer
On Error GoTo ErrHndlr
If TypeName(My_Text) = "Range" Then
    My_Text = My_Text.Value
End If
If TypeName(Text_Pattern) = "Range" Then
    Text_Pattern = Text_Pattern.Value
End If
If Text_Pattern <> "" Then
    With regEX
        .Global = True
        .MultiLine = True
        .pattern = Text_Pattern
        If CaseMatch = True Then
            .IgnoreCase = False
        Else
            .IgnoreCase = True
        End If
    End With
    Set matches_list = regEX.Execute(My_Text)
    If matches_list.Count > 0 Then
      If (Inst_Num = 0) Then
        ReDim Txt_Match_Array(matches_list.Count - 1, 0)
        For match_indx_num = 0 To matches_list.Count - 1
          Txt_Match_Array(match_indx_num, 0) _
          = matches_list.Item(match_indx_num)
        Next match_indx_num
        RegExtract = Txt_Match_Array
      Else
        RegExtract = matches_list.Item(Inst_Num - 1)
      End If
    End If
End If
Exit Function
ErrHndlr:
RegExtract = "Value Error"
End Function

• In the example below, we have a dataset containing some names of employees in a company and their Unique ID/Code.
• The unique code is formatted in such a way that it contains their first and last names’ initial letters, their date of joining (DoJ), and their period of service (PoS).
• In the unique code, the RegEx pattern of Date of Joining can be written as \d{2}-\d{2}-\d{4}.
• We’ll extract the joining date part from the unique code. We can use the following formula:

Function RegExtract(My_Text As Variant, _
Text_Pattern As Variant, _
Optional Inst_Num As Variant = 0, _
Optional CaseMatch As Boolean = True)

Dim regEX As New RegExp

Dim Txt_Match_Array() As String
Dim match_indx_num As Integer

On Error GoTo ErrHndlr

If TypeName(My_Text) = "Range" Then
    My_Text = My_Text.Value
End If
If TypeName(Text_Pattern) = "Range" Then
    Text_Pattern = Text_Pattern.Value
End If

If Text_Pattern <> "" Then
    With regEX
        .Global = True
        .MultiLine = True
        .pattern = Text_Pattern
        If CaseMatch = True Then
            .IgnoreCase = False
        Else
            .IgnoreCase = True
        End If
    End With

    Set matches_list = regEX.Execute(My_Text)

    If matches_list.Count > 0 Then

      If (Inst_Num = 0) Then
        ReDim Txt_Match_Array(matches_list.Count - 1, 0)
        For match_indx_num = 0 To matches_list.Count - 1
          Txt_Match_Array(match_indx_num, 0) _
          = matches_list.Item(match_indx_num)
        Next match_indx_num
        RegExtract = Txt_Match_Array
      Else
        RegExtract = matches_list.Item(Inst_Num - 1)
      End If
    End If
End If
Exit Function

ErrHndlr:
RegExtract = "Value Error"
End Function

How to Replace or Remove Strings Using RegEx in Excel

• We have created a custom function named RegReplace. The VBA code of this function is given below.

Function RegReplace(My_Text As Variant, Text_Pattern As Variant, _
Replaced_Text As String, Optional Inst_Num As Variant = 0, _
Optional CaseMatch As Boolean = True) As Variant
    Dim regEX As New RegExp
    Dim matches_list As Object
    Dim match_indx_num As Integer
    Dim replaced_text_result As String
    On Error GoTo ErrHndlr
    If TypeName(My_Text) = "Range" Then
        My_Text = My_Text.Value
    End If
    If TypeName(Text_Pattern) = "Range" Then
        Text_Pattern = Text_Pattern.Value
    End If
    If Text_Pattern <> "" Then
        With regEX
            .Global = True
            .MultiLine = True
            .pattern = Text_Pattern
            If CaseMatch = True Then
                .IgnoreCase = False
            Else
                .IgnoreCase = True
            End If
        End With
        Set matches_list = regEX.Execute(My_Text)
        If matches_list.Count > 0 Then
            replaced_text_result = My_Text
            For match_indx_num = 0 To matches_list.Count - 1
                replaced_text_result _
                = regEX.Replace(replaced_text_result, Replaced_Text)
            Next match_indx_num
            RegReplace = replaced_text_result
        Else
            RegReplace = My_Text
        End If
    Else
        RegReplace = My_Text
    End If
    Exit Function
ErrHndlr:
    RegReplace = "Value Error"
End Function

• In the example below, we have the same dataset with employee names and their corresponding unique codes of a special pattern.
• We’ll encrypt some parts of the unique code that match a certain format or pattern: the date of joining(DoJ) and years of service(YoS) parts.
• The RegEx of this pattern is \d{2}-\d{2}-\d{4}-PoS-\d. We want to replace it with the following RegEx: xx-xx-xxxx-PoS-x.
• We used the following formula:

Function RegReplace(My_Text As Variant, Text_Pattern As Variant, _
Replaced_Text As String, Optional Inst_Num As Variant = 0, _
Optional CaseMatch As Boolean = True) As Variant

    Dim regEX As New RegExp
    Dim matches_list As Object
    Dim match_indx_num As Integer
    Dim replaced_text_result As String

    On Error GoTo ErrHndlr

    If TypeName(My_Text) = "Range" Then
        My_Text = My_Text.Value
    End If
    If TypeName(Text_Pattern) = "Range" Then
        Text_Pattern = Text_Pattern.Value
    End If

    If Text_Pattern <> "" Then
        With regEX
            .Global = True
            .MultiLine = True
            .pattern = Text_Pattern
            If CaseMatch = True Then
                .IgnoreCase = False
            Else
                .IgnoreCase = True
            End If
        End With

        Set matches_list = regEX.Execute(My_Text)

        If matches_list.Count > 0 Then

            replaced_text_result = My_Text
            For match_indx_num = 0 To matches_list.Count - 1
                replaced_text_result _
                = regEX.Replace(replaced_text_result, Replaced_Text)
            Next match_indx_num
            RegReplace = replaced_text_result

    Else
        RegReplace = My_Text
    End If
    Exit Function

ErrHndlr:
    RegReplace = "Value Error"
End Function

Use a Regular Expression Formula Without VBA in Excel

• We’ll the same dataset that we used in the matching SKU method.
• The SKU codes have a certain pattern. It has a total of 11 characters, of which the first three are uppercase letters, followed by a hyphen (–) and 3-digit numbers. The codes finish with another hyphen (–) and three uppercase letters.
• We need to create two named lists in Excel, which we will use later in the formula.
• We used the Named Range feature to name the list of numbers (0-9) as Numbers and the list of uppercase characters (A-Z) as Letters. Go to the Formula tab in the ribbon and select the Name Manager option.

• Click on the New option from the pop-out dialog box.

• Write the Name and its elements on their corresponding boxes.
• Click OK.
• For the Letters list, the element will be all the uppercased alphabets (A-Z). For Numbers, it will be “1234567890”.

• Click on Close to go back to the worksheet.

• The formula that we used here to match the format is below:

• Without using any regular expression directly, we matched each SKU code with this format using the above code. The codes in cells C10 and C14 were incompatible with the format.

RegEx Cheat Sheet for Excel

Which Things to Remember While Using RegEx in Excel?

• You have to save the Excel file as a macro-enabled file (.xlsm) to use the newly created custom functions after reopening the file. Otherwise, you will lose your written functions while closing the file.
• You have to be careful enough to use the correct RegEx format, otherwise, you can get incorrect results.
• In all three custom functions(RegEx_Match, RegExtract, and RegReplace), there are multiple optional arguments that you can use to get results according to your specific requirements.

Frequently Asked Questions

Can we use RegEx in VBA?

Yes, we can use RegEx by calling a library called Microsoft VBScript Regular Expression 5.5 from the reference library.

Can I use RegEx in Excel to replace the matched string?

Yes, you can use the provided RegReplace function to replace the matched substring with another substring.

Can I perform Case Insensitive RegEx operation in Excel?

In our provided custom functions, you can set the CaseMatch argument to False for performing case-insensitive operations.

RegEx in Excel: Knowledge Hub

• Filter Using RegEx in Excel
• Find & Replace Text Using Regex
• Find and Replace RegEx Patterns
• Count RegEx with COUNTIF
• Data Validation with RegEx

<< Go Back to String Manipulation | Learn Excel