# How to Find & Replace Text Using Regex in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. Read More: How to Use REGEX without VBA in Excel

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  