How can I replace the nth occurence using regex in VBA

Question

I attempted to substitute "hello" for the second occurrence of a number in the phrase "This 9 is 8 a 77 6 test." I, therefore, desired the outcome to read "This 9 is hello a 77 6 test."
I'm receiving "hellohello test" instead.

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

Answer 1

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.