Microsoft 365 formules pour remplacer les caractères "st" et "ste" en début de chaine de texte ou dans la chaine si séparés par 1 espace avant et 1 espace après

=SUPPRESPACE(SUBSTITUE(SUBSTITUE(" "&A6;" ste ";" sainte ");" st ";" saint ")&REPT(" ("&TEXTE(B6;"00000")&")";B6<>""))

Option Explicit
Sub test()
Dim Rgn As Range
     Set Rgn = Range(Cells(6, 3), Cells(Cells(65536, 3).End(xlUp).Row, 3))
Dim cel As Range
Dim NewCommune As String

Dim Match As Object
Dim Matches As Object
Dim StrPattern As String
Dim reg As Object
        Set reg = CreateObject("VBScript.RegExp")
    StrPattern = "(^\s(ste)\s)|(^(ste)\s)|(\s(ste)\s)|(\s(ste)\s$)|(^\s(st)\s)|(^(st)\s)|(\s(st)\s)|(\s(st)\s$)"
    reg.Pattern = StrPattern
    reg.MultiLine = True: reg.IgnoreCase = False: reg.Global = True
    For Each cel In Rgn
        Set Matches = reg.Execute(cel.Value)
        If reg.Execute(cel.Value).Count >= 1 Then
            For Each Match In Matches
                NewCommune = reg.Replace(cel.Value, " saint ")
                cel.Offset(, 2).Value = Trim(NewCommune)
            Next Match
        Else
            cel.Offset(, 2).Value = Trim(cel.Value)
        End If
    Next cel
 
' libération d'objets
    Set Matches = Nothing
    Set Match = Nothing
    Set reg = Nothing
    Set Rgn = Nothing
    Set cel = Nothing
    NewCommune = Empty
End Sub

Option Explicit
Sub test()
Dim Rgn As Range
     Set Rgn = Range(Cells(6, 3), Cells(Cells(65536, 3).End(xlUp).Row, 3))
Dim cel As Range
Dim NewCommune As String
Dim Str As String

Dim Match As Object
Dim Matches As Object
Dim StrPattern As String
Dim reg As Object
        Set reg = CreateObject("VBScript.RegExp")
    StrPattern = "(^\s(ste)\s)|(^(ste)\s)|(\s(ste)\s)|(\s(ste)\s$)|(^\s(st)\s)|(^(st)\s)|(\s(st)\s)|(\s(st)\s$)"
    reg.Pattern = StrPattern
    reg.MultiLine = True: reg.IgnoreCase = False: reg.Global = True
    For Each cel In Rgn
        Set Matches = reg.Execute(cel.Value)
            If reg.Execute(cel.Value).Count >= 1 Then
                For Each Match In Matches
                    If Trim(Match.Value) = "ste" Then
                        Str = "sainte"
                    ElseIf Trim(Match.Value) = "st" Then
                        Str = "saint"
                    End If
                        NewCommune = reg.Replace(cel.Value, " " & Str & " ")
                        cel.Offset(, 2).Value = Trim(NewCommune)
                        Str = Empty
                Next Match
            Else
                cel.Offset(, 2).Value = Trim(cel.Value)
            End If
    Next cel
 
' libération d'objets
    Set Matches = Nothing
    Set Match = Nothing
    Set reg = Nothing
    Set Rgn = Nothing
    Set cel = Nothing
    Str = Empty
    NewCommune = Empty
End Sub