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

byMR

January 12, 2023

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

>Solution :

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.