Need a macro or formula to number occurrences of a defined string in the cell sequentially Example inside

[Tardisgx]

In the below example "]Engine Oil: is the defined string.

Input

[TABLE="width: 710"]
<tbody>[TR]
[TD="class: xl63, width: 710"]Quantities: ]Engine Oil:10W-40 oder 10W-50 SAE]Engine Oil:SH API]Engine Oil:MA JASO]Engine Oil with Filter:4,0 Litre]Brake fluid:DOT 4 specification]Transmission Oil:75W/90 SAE]Cooling system:2,8 Litre]Fork Oil:4W SAE]Fork oil fill left / right:710/710 cm³]Fork - left/right:103/103 mm]Spring travel in front:190 mm]Setting of the gas-closing puller, free travel forward against closing spring:194 mm]

Desired output[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 710"]
<tbody>[TR]
[TD="class: xl63, width: 710"]Quantities: ]1Engine Oil:10W-40 oder 10W-50 SAE]2Engine Oil:SH API]3Engine Oil:MA JASO]Engine Oil with Filter:4,0 Litre]Brake fluid:DOT 4 specification]Transmission Oil:75W/90 SAE]Cooling system:2,8 Litre]Fork Oil:4W SAE]Fork oil fill left / right:710/710 cm³]Fork - left/right:103/103 mm]Spring travel in front:190 mm]Setting of the gas-closing puller, free travel forward against closing spring:194 mm

[/TD]
[/TR]
</tbody>[/TABLE]
Even if a formula only changed up-to 3 occurrences that would be sufficient as a 4th occurrence would be left alone and thus distinct from the first 3

 

[AlphaFrog]

Try this. The code will bold all occurences of the string. The so-called forth occurrence in your example has the word "with" within the string so it's not bolded.

[color=darkblue]Sub[/color] Oil_Change()
    
    [color=darkblue]Dim[/color] found [color=darkblue]As[/color] Range, firstfound [color=darkblue]As[/color] [color=darkblue]String[/color], strSearch [color=darkblue]As[/color] [color=darkblue]String[/color], lStart [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strSearch = "]Engine Oil:"
    
    [color=darkblue]Set[/color] found = ActiveSheet.UsedRange.Find(strSearch, , xlValues, xlPart, 1, 1, 0)
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] found [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        firstfound = found.Address
        [color=darkblue]Do[/color]
            lStart = InStr(1, found.Text, strSearch, 1)
            found.Characters(lStart, Len(strSearch)).Font.Bold = [color=darkblue]True[/color]
            [color=darkblue]Do[/color]
                DoEvents
                lStart = InStr(lStart + 1, found.Text, strSearch, 1)
                [color=darkblue]If[/color] lStart = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
                found.Characters(lStart, Len(strSearch)).Font.Bold = [color=darkblue]True[/color]
            [color=darkblue]Loop[/color]
            [color=darkblue]Set[/color] found = ActiveSheet.UsedRange.FindNext(After:=found)
        [color=darkblue]Loop[/color] [color=darkblue]Until[/color] found.Address = firstfound
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MickG]

Try this to number & bold "Engine Oil" in cell "A1".

[COLOR="Navy"]Sub[/COLOR] MG24Jun13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Pos [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 1 To 2
Pos = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1")
    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] InStr(Pos, Rng, "Engine Oil", vbTextCompare) > 0
        c = c + 1
        Pos = InStr(Pos, Rng, "Engine Oil", vbTextCompare)
        [COLOR="Navy"]If[/COLOR] n = 1 [COLOR="Navy"]Then[/COLOR]
            Rng.Value = Left(Rng, Pos - 1) & c & Mid(Rng, Pos)
        [COLOR="Navy"]Else[/COLOR]
            Rng.Characters(Pos - 1, 11).Font.Bold = True
        [COLOR="Navy"]End[/COLOR] If
        Pos = Pos + 10
    [COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick