Search and Replace using Backrefrence in vbscript for excel 2007

Question

Hi, I'm using excel 2007 and i'm adding a macro that looks something like this :

Function S(Value As String, Pattern As String, ReplaceWith As String, Optional IgnoreCase     As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    r.Global = True
    S = r.Replace(Value, ReplaceWith)
End Function

I can use the Search and Replace function like this in the sheet :

=s("Say Hello","Hello","HI",FALSE)

works fine.

What I actually want to do with this is something like this:

Search capital letters from the text : "MyName"
Result : "My Name"

I use [A-Z] to search can't seem to replace it with the space and with the original characters intact.

I know i'd need to use backrefrence but can't figure out a way to do this as i'm new to both vbscript and regex.

Thanks for your help guys! : )

Answer 1

See here for how to use backreferences.