VBA + RegEx + Range.Replace

Question

I have some regex knowledge from using it JavaScript but now I want to use it in Excel and VBA and I have some problems getting it work.

I want to replace >abc with <br>> abc.

In JavaScript I use >([a-zA-Z0-9]) and <br>> $1, but here I don't know how to do it.

Also I want to use it with Range.Replace function, like:

oRange.Replace What:=">abc", Replacement:="<br>> abc", MatchCase:=False

Any help will be appreciated.

[Here's a pretty great](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) thread on VBA and ReGex, which I think would be helpful to read through. — BruceWayne, Apr 15 '16 at 18:37
`Range.Replace` does not support regex based replacement. Why not just replace `>` with `>SPACE`? — Wiktor Stribiżew, Apr 15 '16 at 18:50

score -1 · Answer 1 · answered Apr 16 '16 at 07:45

Maybe something like this routine. This will search a specified range with the pattern and replace the contents...

Hope that helps.

Public Sub RegExMatchAndReplace(ByVal Pattern As String, ByVal Data As Range, ByVal Replace As String)


Dim oRegEx As New RegExp
Dim rPtr As Range

For Each rPtr In Data
    With oRegEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = Pattern
        rPtr.Value = oRegEx.Replace(rPtr.Value, Replace)
    End With
Next

End Sub

BTW, this requires a reference to the Reg Expressions library. — PaulG, Apr 16 '16 at 21:31

VBA + RegEx + Range.Replace

1 Answers1