How to Find and Replace RegEx Patterns in Excel (3 Examples)

Microsoft Excel gives users a variety of alternatives when it comes to replacing one text with another, including the Find and Replace tool and a few substitution methods. Nevertheless, why would we use regexes to add complexity to the situation? It’s because Excel’s built-in features can only process the exact strings that you provide. Regular expressions are essential for finding strings that fit a pattern and replacing them with different strings. In this article, I will show how to create a user-defined function with Excel VBA to find and replace RegEx patterns.

Download Practice Workbook

You can download the following practice workbook to practice along with it while reading this article.

Introduction to a User-Defined Function in Excel VBA That Will Find and Replace RegEx Patterns

Though MS Excel doesn’t have a RegEx function, the RegExp object is there in Excel VBA. So we can use it to create a function to find and replace regex patterns.

We all know that Excel functions consist of their names and the arguments inside brackets. In the same way, we will create a RegEx function.

To know how to create a user-defined function in Excel, check this article.

VBA Code:

Public Function Find_Replace_RegEx(main_txt As String, pat As String, replace_txt As String, Optional rep_replace As Integer = 0, Optional case_sense As Boolean = True) As String
Dim text_res, find_text As String
Dim match_SL, starting_pos As Integer
On Error GoTo ErrHandl
text_res = main_txt
Set Xregexp = CreateObject("VBScript.RegExp")
Xregexp.pattern = pat
Xregexp.Global = True
Xregexp.MultiLine = True
If True = case_sense Then
Xregexp.ignorecase = False
Else
Xregexp.ignorecase = True
End If
Set Xmatch = Xregexp.Execute(main_txt)
If 0 < Xmatch.Count Then
If (0 = rep_replace) Then
text_res = Xregexp.Replace(main_txt, replace_txt)
Else
If rep_replace <= Xmatch.Count Then
starting_pos = 1
For match_SL = 0 To rep_replace - 2
starting_pos = InStr(starting_pos, main_txt, Xmatch.Item(match_SL), vbBinaryCompare) + Len(Xmatch.Item(match_SL))
Next match_SL
find_text = Xmatch.Item(rep_replace - 1)
text_res = Left(main_txt, starting_pos - 1) & Replace(main_txt, find_text, replace_txt, starting_pos, 1, vbBinaryCompare)
End If
End If
End If
Find_Replace_RegEx = text_res
Exit Function
ErrHandl:
Find_Replace_RegEx = CVErr(xlErrValue)
End Function

We are grateful to ablebits for this code.

⦿ Purpose:

After matching a certain pattern in a string, it replaces the matched text with a specified text.

⦿ Syntax:

⦿ Arguments:

• main_txt = the major text string, where you will look to match a certain pattern.
• pat= the regex to match (regex means regular expression)
• replace_txt= after matching the pattern, the function will replace the matched text with this specified text.
• rep_replace= is the number of instances that indicates which instance of match the function will replace. We will make this argument optional which means if you don’t mention this argument inside the function, all the matched text will be replaced by default.
• case_sense=another optional argument (boolean) we are going to add. FALSE means the match is not case-sensitive. But if you type TRUE or omit this, the function is by default case-sensitive.

⦿ Available in:

You can create this VBA function in all versions of Excel 365 and Excel 2010 or later versions.

Read More: How to Find & Replace Text Using Regex in Excel

3 Examples to Find and Replace RegEx Patterns in Excel Using User-Defined VBA Function

Now, we will see 3 applications of the Find_Replace_RegEx function that we have built in Excel VBA.

1. Replace Strings That Match a Given RegEx Pattern

Look at the following image. We have some strings. We will now see if any of the parts of these strings match with a specific pattern. If so, then the function will replace that part with a given string.

• We have specified the pattern in cell B13. It is-

• The replacement text is in cell C13 which is- XY-XYZ-WXYZ
• The formula needed to perform the job is-

You can keep the last to commas or just omit them. Keeping them reminds you that there are two more arguments in this function which are optional but you can add them too.

Read More: How to Perform Pattern Matching in Excel (2 Suitable Methods)

2. Find and Replace All Numbers

In this example, we will replace all the numbers with this function.

• The pattern is-

• The replacement will be- ?
• Formula to apply-

Look at the following image to see the results.

Read More: How to Use REGEX without VBA in Excel (with Easy Steps)

3. Find and Replace Characters at a Specified Instance That Match a RegEx Pattern

The rep_replace argument is set to replace all the matches. But if you want, you can replace only a specified instance. For example, look at the following image. We have replaced the 3rd instance here.

• The formula will be:

Or,

Read More: How to Use REGEX to Match Patterns in Excel (6 Examples)

Some Common RegEx Patterns to Use in Excel

Here are some common RegEx patterns that you can use.

Conclusion

I hope you will find this article useful to find and replace regex patterns in Excel with the VBA function. If the function does not work in your Excel workbook, then go to the internet option and keep our site in the trusted list. If you face any problems, then please let us know in the comment box. Happy Excelling!

Related Articles

• How to Filter Using RegEx in Excel (with Simple Steps)
• Data Validation with RegEx in Excel (With Easy Steps)