How to Find & Replace Text Using Regex in Excel

In this article, I will describe the way we can find and replace text using Regex in Excel. As we know, Excel has some functions such as (REPLACE & SUBSTITUTE Functions) that we can use to find a specific text and replace it. But when we need to find the texts having a specific pattern, we need to use regex instead of plain text. But unfortunately, Excel doesn’t allow us to use regex directly to find text as it doesn’t have any built-in function to operate this. Here, I will describe how we can use regex to find texts having a particular pattern and then replace them with another piece of text. So let’s start our journey.

What Is Regex?

Before seeing how we can use regex, let’s first understand what regex is. Regex is the shorter form of Regular Expression. It is a sequence of characters or syntax that defines a pattern of text. For example, the a.b is a regular expression(regex) that matches acb, axb, or a pattern like this. a*b matches with any text that contains the letter a in the first and the letter b in the last(ab, axb, axxxb, etc). The regex color matches both “color” and “colour”. In this way, using these syntaxes, we can find all the texts that have a specific pattern.

Replace Text Using Regex in Excel: Step-by-Step Procedures

Here, we will create a custom function to use Regex and replace text in Excel. We will use the VBA editor to create the user-defined function. To do that, follow the steps below.

Step-1 Launching VBA Editor to Insert a New Module

First, Open an Excel File where we want to perform the Regex and replace task.
Now open the VBA Developer Window by pressing Alt+F11.

Then go to Insert > Module to create a new function.

Step-2 Creating User-Defined Function

Now, copy-paste the following formula into the new module window.

Public Function RegexReplace(AA_text As String, pattern As String, AA_text_replace As String, Optional AA_instance_num As Integer = 0, Optional AA_match_case As Boolean = True) As String
    Dim AA_text_result, AA_text_find As String
    Dim AA_matches_index, AA_pos_start As Integer
    On Error GoTo ErrHandl
    AA_text_result = AA_text
    Set AA_regex = CreateObject("VBScript.RegExp")
    AA_regex.pattern = pattern
    AA_regex.Global = True
    AA_regex.MultiLine = True
    If True = AA_match_case Then
        AA_regex.ignorecase = False
    Else
        AA_regex.ignorecase = True
    End If
    Set AA_matches = AA_regex.Execute(AA_text)
    If 0 < AA_matches.Count Then
        If (0 = AA_instance_num) Then
            AA_text_result = AA_regex.Replace(AA_text, AA_text_replace)
        Else
            If AA_instance_num <= AA_matches.Count Then
                AA_pos_start = 1
                For AA_matches_index = 0 To AA_instance_num - 2
                    AA_pos_start = InStr(AA_pos_start, AA_text, AA_matches.Item(AA_matches_index), vbBinaryCompare) + Len(AA_matches.Item(AA_matches_index))
                Next AA_matches_index
                AA_text_find = AA_matches.Item(AA_instance_num - 1)
                AA_text_result = Left(AA_text, AA_pos_start - 1) & Replace(AA_text, AA_text_find, AA_text_replace, AA_pos_start, 1, vbBinaryCompare)
            End If
        End If
    End If
    RegexReplace = AA_text_result
    Exit Function

ErrHandl:
    RegexReplace = CVErr(xlErrValue)
End Function

🎓How Does the Code Work?

In the first line, a public function RegexReplace is declared which has 3 mandatory arguments ( AA_text, AA_text, pattern, AA_text_replace) and 2 optional arguments( AA_instance_num and AA_match_case). Then 2 variables are declared as strings (AA_text_result and AA_text_find. Another 2 variables are declared as Integer (AA_matches_index, AA_pos_start). After that, An object is created named AA_regex with VBScript.RegExp. In the calculation loop, first, the case sensitivity is checked. We used the If Statement for this. Then the regex is matched using AA_regex.Execute. Then, according to the AA_instance_num, the matched texts are replaced by AA_text_replace. For error handling, an error case handler (ErrHandl) is declared. As a result, if any error occurs, Excel will display xlErrValue.

Now, save the file as Macro Enabled Excel (.xlsm).
This will create a user-defined function named
The syntax of the function is below.

RegexReplace(AA_text, AA_Pattern, AA_text_replace, [AA_instance_num], [AA_match_case)

As we can see, the RegexReplace function has 5 arguments. They are:
- AA_text (required) – This is the text within which the function will search for the regular expression (Regex)
- AA_Pattern (required) – This is the regular expression (Regex) with which the function will look to match.
- AA_Text_replace (required) – This is the replacement text for the matching case.
- AA_Instance_num (optional) -It is a sequential number that identifies which instance has to be changed. If we leave it empty, the function will swap out any matches it finds (default).
- AA_Match_case (optional) – This determines whether the text case should be matched or ignored. The search is case-sensitive if TRUE or omitted (the default); otherwise, it is case-insensitive.

Step-3 Application of User-Defined Function in Worksheet.

Now, we will apply the user-defined function in multiple examples. To illustrate the application, I have taken a dataset where we have some names and their birthday date.

Here, we will search the date part (eg: 08-03-1970) inside those texts and replace them with dd-mm-yyyy. So, instead of John Kits, DOB: 08-03-1970, we will get John Kits, DOB: dd-mm-yyyy.
In this case, the regular expression of the date will be \d{2}-\d{2}-\d{4}. Here, d indicate any numerical digit (0-9), and {2} indicate the number of time the digit appears in the text.
The replaced text will be dd-mm-yyyy.

Now, on cell C5, write down the following formula and press Enter.

=RegexReplace(B5,$C$12,$C$13)

As a result, you will see the desired result.

Now, to apply the formula to the rest of the cells, use Fill Handle. As a result, you will get the following result.

In another example, we have some names and their corresponding Physics and Maths marks. We wanted to replace the Marks with ##. The Regex was \d+. After applying the same formula, we get the following result.

If you wanted to replace only the 1st matched number(Physics Marks), you have to give 1 in the 4th argument of the function RegexReplace.

=RegexReplace(B5,$C$12,$C$13,1)

On the other hand, if you want to replace the 2nd matched number, you have to give 2 in the 4th argument of RegexReplace.

Things to Remember

To use regular expressions in Excel, we must know some basic regex expressions and how to formulate regex.
When you input regex directly in the formula, don’t forget to insert them inside the double quotation marks.
If we keep the 4th argument empty, the RegexReplace function will always replace all the matched results with regex. To replace a specific occurrence, we have to put the corresponding number in the 4th argument.
The RegexReplace function is by default case sensitive. Hence, in order to make it case insensitive, make the 5th argument False.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Regex Replace.xlsm

Conclusion

That is the end of this article regarding how we can find & replace text using regex. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

<< Go Back to RegEx in Excel | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!