Find and Replace using RegEx, without user input

Question

I am trying to find every instance of a pattern in a range of cells and replace those instances with a string literal.

Sub poBoxRegEx(myRange As Range)
    Dim myRegEx As New VBScript_RegExp_55.RegExp
    myRegEx.Pattern = "^[p|P][ |\.]*[o|O|0][\.]* ([b|B]ox[ ]*)"
    myRegEx.ignoreCase = True
    myRegEx.Global = True
    myRegEx.MultiLine = True

    myRange.Value = myRegEx.Replace(myRange.Value, "PO Box ")

End Sub

Now every other topic and use case I find online here and else where is creating a function accessible via the function bar. I don't need that because I am not creating something that parses the text in column A and outputs the result to column B. No, I just want this find and replace to replace the original text in the original cell.

Can you provide the context you're originally trying to find? — hwnd
– hwnd, Commented Jul 16, 2014 at 1:46
I receive data for mailing lists, which is never consistent. One day I may get a data set with 13 columns, another 50 columns. I never know where "PO Box" (or rather a mangled variant of it) is going to appear. I am looking to replace every instance of inconsistent cases, spaces and dot delimited ("P.O.") with a string literal ("PO Box"). — Kevin Scharnhorst
– Kevin Scharnhorst, Commented Jul 16, 2014 at 1:52
So is your question about your regex or how to do the replacement? — hwnd
– hwnd, Commented Jul 16, 2014 at 1:54
Your pattern should be ^[pP][ .]*[oO0][.]* ([bB]ox *) . don't need to give an | inside char class. — Avinash Raj
– Avinash Raj, Commented Jul 16, 2014 at 2:04

RowanC · Accepted Answer · 2014-07-16 02:08:15Z

1

You need a loop if your range is greater than one cell.

Sub poBoxRegEx(myRange As Range)
    Dim myRegEx As New VBScript_RegExp_55.RegExp
    Dim cell As Range
    myRegEx.Pattern = "^[p|P][ |\.]*[o|O|0][\.]* ([b|B]ox[ ]*)"
    myRegEx.ignoreCase = True
    myRegEx.Global = True
    myRegEx.MultiLine = True

    for each cell in myRange
        cell.Value = myRegEx.Replace(cell.Value, "PO Box ")
    next
End Sub

answered Jul 16, 2014 at 2:08

RowanC

1,65911 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Scharnhorst Over a year ago

Good job! Hours of searching and tinkering... you nailed it in much less time.

Collectives™ on Stack Overflow

Find and Replace using RegEx, without user input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related