Regex: replace the 3rd occurrence

[Nelson78]

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).

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

 

[pgc01]

Hi

Try:

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

 

[pgc01]

simpler, if all in the same line:

    .Pattern = "(^.*?a.*?a.*?)a"

 

[Nelson78]

simpler, if all in the same line:

    .Pattern = "(^.*?a.*?a.*?)a"

Yes, correct.

Thank you above of all for "$1b" instead of "b".

 

[pgc01]

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?