How to Find & Replace Text Using Regex in Excel

In this article, we will explain how to find and replace text with a particular pattern using Regex in Excel. Excel doesn’t have any built-in functions for working with regex, but we can make one with VBA code.

What Is Regex?

Regex is short for Regular Expression. It is a sequence of characters or syntax that defines a pattern of text. For example, a.b is a regular expression (regex) that matches a pattern like acb, axb, etc. a*b matches text that contains the letter a as the first character and the letter b as the last (eg ab, axb, axxxb, etc). The regex color matches both “color” and “colour”. Using regex syntax, we can find all the instances of text that has a specific pattern.

Replacing Text Using Regex in Excel: Step-by-Step Procedure

We will use the VBA editor to create a custom user-defined function to use Regex and replace text in Excel.

Step 1 – Launching the VBA Editor to Insert a New Module

Open an Excel File in which to perform the Regex and Replace operation.
Open the VBA Developer Window by pressing Alt+F11.

Go to Insert > Module to create a new function.

Step 2 – Creating the User-Defined Function

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?

A public function RegexReplace is declared which has 3 mandatory arguments (AA_text, AA_text_pattern and AA_text_replace) and 2 optional arguments(AA_instance_num and AA_match_case).
2 variables are declared as strings (AA_text_result and AA_text_find), and another 2 variables are declared as Integer (AA_matches_index and AA_pos_start).
An object is created named AA_regex with VBScript.RegExp.
In the calculation loop, first the case sensitivity is checked using an If Statement. Then the regex is matched using AA_regex.Execute. Depending on the AA_instance_num, the matched text is 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.

Save the file as file-type Macro Enabled Excel (.xlsm).

We have created a user-defined function named RegexReplace.

The syntax of the function is:

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

The arguments are:
- AA_text (required) – The text in which the function will search for the regular expression (Regex).
- AA_Pattern (required) – The regular expression (Regex) with which the function will look for matches.
- AA_Text_replace (required) – The replacement text in case of a match.
- AA_Instance_num (optional) – A sequential number that identifies which instance has to be changed. If left empty, the function will replace all matches, the default.
- AA_Match_case (optional) – 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 – Applying the User-Defined Function in a Worksheet

Now, we will apply the user-defined function in a few examples.

Consider the following dataset containing some names and birthdays combined into strings.

We will find the dates (eg 08-03-1970) within these strings and replace them with dd-mm-yyyy. So, instead of John Kits, DOB: 08-03-1970, we will have John Kits, DOB: dd-mm-yyyy.

The regular expression for the date is \d{2}-\d{2}-\d{4}, where d indicates any numerical digit (0-9), and {2} indicates the instance of the match.

The replaced text will be dd-mm-yyyy.

In cell C5, enter the following formula and press Enter:

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

The desired result is returned.

To apply the formula to the rest of the cells, drag the Fill Handle down.

The result is as follows:

As another example, we have some names and their corresponding Physics and Math marks.

To replace the Marks with ##, the Regex is \d+.

After applying the same formula, the following result is returned:

To replace only the 1st matched number (Physics Marks), use 1 as the 4th argument of the function RegexReplace as follows:

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

In the same way, to replace the 2nd match, use 2 as the 4th argument of RegexReplace.

Things to Remember

To use regular expressions in Excel, some knowledge of basic regex expressions and how to formulate regex is required.
When inputting regex directly into the formula, don’t forget to encase it in double quotation marks.
If the 4th argument is left empty, the RegexReplace function will replace all the matched results with the specified regex. To replace a specific occurrence, put the corresponding number in the 4th argument.
The RegexReplace function is by default case sensitive. To make it case insensitive, insert False for the 5th argument.