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.