Regex: replace the 3rd occurrence

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
525
Office Version
  1. 2007
Hello everybody.

I've been manipulating some strings.
Now, I'm in a deadlock.

I mean: in a specific string, I want to replace only the third occurrence of character "a" with character "b".

How can I build the pattern (see in red below).


Code:
Sub substitute()

Dim lr As Long
Dim Rng As Range, Cell As Range
Dim replacement  As Object

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A1:A" & lr)

Set replacement = CreateObject("vbscript.regexp")

With replacement
    [COLOR="#FF0000"].Pattern =[/COLOR]      
    .Global = True
    .IgnoreCase = True
End With

For Each Cell In Rng
        Cell.Value = replacement.Replace(Cell.Value, "b")    
Next Cell

End Sub
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi

Try:

Code:
Sub substitute()

Dim Rng As Range, rC As Range
Dim regex  As Object
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:A" & lr)

Set regex = CreateObject("vbscript.regexp")

With regex
    .Pattern = "(^([^a]*a){2}[^a]*)a"
    .IgnoreCase = True
End With

For Each rC In Rng
        rC.Value = regex.Replace(rC.Value, "$1b")
Next rC

End Sub
 
Last edited:
Upvote 0
You're welcome

... of all for "$1b" instead of "b".

As a side note:

You could use your "b" if the ms regex implementation was more up to date with regard to the lookaround features. We can use lookaheads but we cannot yet use lookbehinds, although they have been around for many years and are already available/part of many other languages/solutions. Maybe in the next version?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top