How can I replace the nth occurence using regex in VBA?-CodePudding

E.g. I tried to replace the 2nd occurence of a number in "This 9 is 8 a 77 6 test" with "hello".

So I wanted the result to be "This 9 is hello a 77 6 test".

Instead, I'm getting "hellohello test".

I'm using:

=RegexReplace("This 9 is 8 a 77 6 test","(?:\D*(\d )){2}","hello")

where RegexReplace is defined below.:

Function RegexReplace(text As String, pattern As String, replace As String)

        Static re As Object

        If re Is Nothing Then
            Set re = CreateObject("VBScript.RegExp")
            re.Global = True
            re.MultiLine = True
        End If

        re.IgnoreCase = True
        re.pattern = pattern
        RegexReplace = re.replace(text, replace)
        Set re = Nothing

End Function

CodePudding user response：

You need to use

=RegexReplace("This 9 is 8 a 77 6 test","^(\D*\d \D )\d ","$1hello")

See the regex demo.

Details:

^ - start of string
(\D*\d \D ) - Group 1: zero or more non-digits one or more digits one or more non-digits (this value will be restored in the result using the numbered replacement backreference $1)
\d - one or more digits.

To replace the third number, you can refactor the pattern into ^(\D*(?:\d \D ){2})\d . Note the location of the capturing and non-capturing parentheses.