How can I replace the nth occurence using regex in VBA

0 votes

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
Jan 13 in Others by Kithuzzz
• 27,740 points
34 views

1 answer to this question.

0 votes

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.
answered Jan 13 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 51,240 points
123 views
0 votes
1 answer

How can I perform a reverse string search in Excel without using VBA?

This one is tested and does work ...READ MORE

answered Jan 5 in Others by narikkadan
• 51,240 points
49 views
0 votes
0 answers

how can I deploy a test in pytest framework to airflow using DAG?

I have established an automative testing framework ...READ MORE

Oct 4, 2021 in Others by Yuan
• 120 points
275 views
0 votes
0 answers

How can I re-download the pem file in AWS EC2?

I created a key pair pem file ...READ MORE

Apr 5, 2022 in Others by Kichu
• 19,040 points
1,110 views
0 votes
1 answer

Delimiters in Excel VBA Regex Patterns, filter something but not others

Change your code to this: Function RemoveTags(ByVal Value ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,240 points
45 views
0 votes
1 answer

Macro that deletes all asterisk signs from cells

Try this: Sub RemoveNonNumeric() Dim ...READ MORE

answered Jan 22 in Others by narikkadan
• 51,240 points
33 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

How can I find and replace text in Word using Excel VBA?

Try this code Option Explicit Const wdReplaceAll = 2 Sub ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 51,240 points
262 views
0 votes
1 answer

How should I show the Developer tab in Excel using Macros? (Excel VBA)

You can activate (mode=1) or deactivate (mode=0) ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 51,240 points
78 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP