Replace/Delete a specific pattern within a string using VBA

[soccer4ward]

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,

 

[shknbk2]

Here's a function I use all the time:

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:

ActiveCell.Value = RegexReplace(activecell.Value, "\d{3}\.\d{5}\.\d{3}\.\d{5}\.\d{11}","")

These are two resources I refer to often:

Regex Cheat Sheet

Regular Expressions Syntax Reference. Includes tables showing syntax, examples and matches.

www.rexegg.com

regex101: build, test, and debug regex

Regular expression tester with syntax highlighting, explanation, cheat sheet for PHP/PCRE, Python, GO, JavaScript, Java, C#/.NET, Rust.

regex101.com