RegEx in Excel (For Matching, Extracting and Replacing)

In this Excel tutorial, you will learn how to use RegEx in Excel.  We will first learn about RegEx and then explore how we can use different VBA user-defined functions that use RegEx in Excel for various kinds of applications, such as finding strings and numbers, extracting matched strings or texts, and replacing matched texts with other texts.

Here, we prepared the dataset using the Microsoft 365 version of Excel. However, you can also use older versions of Excel, starting from Excel 2007,  to use the functions and features shown in this article.

RegEx is very useful when it comes to matching patterns, extracting data, and replacing strings. Unfortunately, Excel doesn’t have any built-in functions that can use RegEx. Hence, we need to use some user-defined functions to utilize the power of RegEx in Excel.

Download 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.

Are There 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. To enable the library, follow the steps below.

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

• Now click on the Microsoft VBScript Regular Expressions 5.5 Reference and then click OK.

Now you can use the RegExp type object in your code, which will allow us to create RegEx in custom functions.

How to Match Text Using Regex in Excel?

To search for a specific pattern using RegEx, we created a custom function named RegEx_Match. To create the function, write down 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 Many Ways Can We Use RegEx in Excel?

There are at least nine different applications of RegEx in Excel. Some of them are:

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

1. How to Use RegEx for Matching Valid Email Addresses in Excel?

Sometimes we need to verify whether a cell contains a valid mail address or not. To do so, we can use the RegEx pattern of email address.

• 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 verify whether cell C5 contains the RegEx of a valid email address (C16). We use the following formula to detect valid email addresses:

Click here to enlarge the image

Here, 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

2. Can We Use RegEx for Matching SKU Codes?

Sure. In this example, we have a bunch of SKU codes for products in a shop. As we know, every category item has its own SKU pattern. By matching a specific SKU pattern, we can determine which category a product is in.

• Here, 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.
• In this situation, we use the following formula:

As we can see, the bottom two SKU codes do not match with the RegEx, hence we get the “Unmatched” result.

Read More: Perform Pattern Matching in Excel

3. How to Use RegEx for Checking Social Security Numbers?

Different countries provide their citizens with a unique number that can be used to identify them and provide different kinds of services. In the USA, the unique number is called the Social Security Number (SSN). The social security number has a unique pattern that contains a total of nine numbers.

• The RegEx pattern of a SSN is \d{3}-\d{2}-\d{4}$.
• Now, to verify whether a given number is actually a valid SSN or not, we can use our custom function RegEx_Match.
• In the dataset below, we checked whether the given numbers are of SSN pattern.
• The SSN RegEx pattern is in cell C16. I 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”.

4. Is It Possible to Validate Phone Numbers Using Regex?

Yes, it is easily possible. But to do that, we need to write the RegEx of valid phone numbers. As we know, every country has its own unique country code for phone numbers within that country.

• For example, 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 or not.
• In the dataset below, I verified the number in cell C5 using the RegEx in cell C16. To do that, I have used the following formula:

Read More: Use REGEX to Match Patterns

5. Can We Combine Excel IF Function with RegEx?

Certainly. The IF function allows us to set the custom returning values for both TRUE and FALSE cases. Using this function, we can customize the returning value of the RegEx_Match function.

• In the dataset below, we have a list of people with their respective addresses.
• Now 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:

In this formula, when the RegEx_Match function returns “Matched”, the IF function will return “Yes” dictating that the address is not from Texas. Otherwise, the IF function returns “No”.

Read More: How to Find RegEx Patterns in Excel

6. How to Use RegEx to Lookup Unmatching String?

In the previous example, instead of using the IF function to show who is from outside the state of Texas, we could use the optional arguments of the RegEx_Match function: IfMatched and IfUnmatched. These two arguments allow us to set the custom return value for both Matched and Unmatched cases.

• In the dataset below, I have again found out who is outside the Texas state but 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”.

7. When to Use Excel COUNTIF Function with RegEx?

We can use the COUNTIF function with RegEx when we need to count anything that meets a certain criterion.

• For example, in the below dataset, I have calculated how many people were from outside Texas.
• Here, I used the following formula:

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

8. How to Use RegEx to Lookup Unmatching Character?

In RegEx, we can use the “[^ ]” sign to match any string that does not contain any of the characters that are inside the square bracket after the ^ sign.

• For example, 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.
• In the dataset below, I have used the following formula to verify phone numbers that don’t begin with a + sign:

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

9. How to 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 can assign “FALSE” to the CaseMatch argument, then it will perform Case Insensitive matching.

• In the dataset of the 2nd example, we altered the case of a character in the first Product SKU cell.
• Then, by keeping the same RegEx, we used the following formula to perform case-insensitive matching:

As a result, 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?

On occasion, we not only need to check whether a string matches a pattern but also extract the matched portion of the string. To extract the matched portion, we write another VBA custom function named RegExtract. The VBA code for this function is as follows:

Click here to enlarge the image.

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}.
• Now, our target is to extract the joining date part from the unique code.
• To do that, we can use the following formula:

As a result, we were able to extract the joining date successfully.

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?

On many occasions, we not only need to detect strings with a specific pattern but also to replace those matched parts with another substring. To do this, we have created a custom function named RegReplace. The VBA code of this function is given below.

Click here to enlarge the image.

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.
• Now, our target is to encrypt some parts of the unique code that match a certain format or pattern. We are actually concerned about encrypting 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. And we want to replace it with the following RegEx: xx-xx-xxxx-PoS-x.
• For that, we used the following formula:

As a result, we can see that we have successfully encrypted the unique codes.

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

Can We Use Regular Expression Formula Without VBA in Excel?

Yes, we can. In this example, we will see an alternative way of matching strings using RegEx without VBA in Excel.

• For illustration,  I have taken the same dataset that we used in the matching SKU example.
• As we know, the SKU codes have a certain pattern. It has a total of 11 characters, of which the first three are uppercase letters. Then there is a hyphen (–) and 3-digit numbers. The codes finish with another hyphen (–) and three uppercase letters.
• Before using a formula, we need to create two named lists in Excel, which we will use later in the formula.
• For that, first, we need to use the Named Range feature in Excel to name the list of numbers (0-9) as Numbers and the list of uppercase characters (A-Z) as Letters.
• To do that, go to the Formula tab in the ribbon and select the Name Manager option.

• Now, click on the New option from the pop-out dialogue box.

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

• After creating two lists, we need to 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. As we can see, the codes in cells C10 and C14 came out to be incompatible with the format.

RegEx Cheat Chart 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

1. Can we use RegEx in VBA?

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

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

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

3. Can I perform Case Insensitive RegEx operation in Excel?

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

Conclusion

In this article, we gave a brief idea of RegEx and provided 3 VBA codes of user-defined functions to match, extract, and replace strings using RegEx. We also gave multiple examples of using the functions in different circumstances such as validating phone numbers, matching SKU codes, performing case-sensitive searches, etc. In addition to that, we also showed how to use RegEx without VBA in Excel. Lastly, we presented a valuable cheat sheet of RegEx. I hope that, after going through the article, you built a solid foundation for using RegEx in Excel. If you like our article, you can share it with your friends and colleagues. Moreover, feel free to share your thoughts and suggestions in the comment section of this post. Goodbye!

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