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.
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.
After matching a certain pattern in a string, it replaces the matched text with a specified text.
RegExpReplace(main_txt, pat, replace_txt, [rep_replace], [case_sense])
- 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.
- This function will return the original text if no pattern is found to match.
- Use double quotes to supply regex patterns in a formula directly.
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:
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.
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!