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.

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

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.

Replace Strings That Match a Given RegEx Pattern

Read More: How to Perform Pattern Matching in Excel


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.

Find and Replace All Numbers

Read More: How to Use REGEX without VBA in Excel


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.

Find and Replace Characters of a Specified Instance That Match a RegEx Pattern

  • The formula will be:
=Find_Replace_RegEx(B5,"\d+","?",3,FALSE)

OR

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

Read More: How to Use REGEX to Match Patterns in Excel


Some Common RegEx Patterns to Use in Excel

Here are some common RegEx patterns that you can use.


Download the Practice Workbook


Related Articles


<< Go Back to RegEx in Excel | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo