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

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

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

⦿ Purpose:

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

⦿ Syntax:

RegExpReplace(main_txt, pat, replace_txt, [rep_replace], [case_sense])

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

Note:

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.

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

Example 1 – Replace Strings That Match a Given RegEx Pattern

We have some strings. We will now see if any of the parts of these strings match with a specific pattern.

We have specified the pattern in cell B13:

\d{2}-\d{3}-\d{4}

The replacement text is in cell C13 which is- XY-XYZ-WXYZ
The formula needed is:

=Find_Replace_RegEx(B5,$B$13,$C$13,,)

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.

Example 2 – Find and Replace All Numbers

The pattern is:

\d+

The replacement will be a question mark.
Here’s the formula that accomplishes that.

=Find_Replace_RegEx(B5,$B$13,$C$13,3,FALSE)

B13 and D13 hold the regex and the replacement, respectively.

Example 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. We have replaced the third instance here.