Replace/Delete a specific pattern within a string using VBA

soccer4ward

New Member
Joined
Aug 11, 2020
Messages
8
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello,

I'm looking for help in locating and replacing/deleting the following pattern within a string in a cell:

"xxx.xxxxx.xxx.xxxxx.xxxxxxxxxxx" where x represents a numerical character

i just learned how to use regExp...but can't figure out for the life of me how to tell it to look for the specific pattern above using its arguments. Once found, i need it to delete that pattern or replace it with a space. Each string could potentially have multiple of these patterns. All of them need to be deleted/replaced

Any help is appreciated!

thank you,
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Here's a function I use all the time:
VBA Code:
Function RegexReplace(ByVal original_Text As String, _
                      ByVal pattern_Text As String, _
                      ByVal replacement_Text As String, _
                      Optional ByVal ignore_case As Boolean = False) As String

    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")

    RE.IgnoreCase = ignore_case
    RE.Pattern = pattern_Text
    RE.Global = True
    RegexReplace = RE.Replace(original_Text, replacement_Text)

End Function
Your regex code is "\d{3}\.\d{5}\.\d{3}\.\d{5}\.\d{11}"

You can use the function above using your code like this:
VBA Code:
ActiveCell.Value = RegexReplace(activecell.Value, "\d{3}\.\d{5}\.\d{3}\.\d{5}\.\d{11}","")
These are two resources I refer to often:
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top