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

[Usine à gaz]

Re-Bonjour à toutes et à tous,

Me voilà devant un nouveau souci

Pour mon fichier communes de France, environ 40000 communes, j'ai besoin de 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.
Pour le début de chaine, pas de souci.

Mais voilà avec ma formule actuelle en colonne "E" du fichier test joint, tous les st ou ste, où qu'ils soient positionnés dans la chaine sont remplacés par "saint" ou "sainte".
Evidemment, cela me change les noms des communes et ça ne fait pas mon affaire lol ou plutôt pas lol du tout

A l'instant, malgré mes recherches et tentatives, je n'ai pas trouvé la solution.
Auriez-vous la bonne formule ?
Fichier test joint,

Je continue mes recherches.
A grand merci par avance,
Amicalement,
lionel,

 

[Chris401]

Bonjour usine à gaz

Une façon :

=SI(GAUCHE(A6;3)="st ";SUBSTITUE(A6;"st ";"saint ";1);SI(GAUCHE(A6;4)="ste ";SUBSTITUE(A6;"ste ";"sainte ";1);A6))

Edit : Oups ! je n'avais pas vu la seconde partie

ou dans la chaine si séparés par 1 espace avant et 1 espace après.

Je continue la recherche

 

[Chris401]

Autre formule (à tester)

=SI(ESTNUM(TROUVE("st ";A6;1));SUBSTITUE(A6;"st ";"saint ";1);SI(ESTNUM(TROUVE("ste ";A6;1));SUBSTITUE(A6;"ste ";"sainte ";1);A6))

 

[Usine à gaz]

Bonjour Chris,

Merci ça fonctionne super bien
J'essaie de baser ta formule sur la colonne C (pas sur la colonne A).
Mais je n'y arrive pas.
Bonne fin de dimanche,
lionel

 

[Chris401]

Tu remplaces tous les A6 de la formule par Cx (x étant la ligne concernés)

 

[Usine à gaz]

lol : pas réussi.
Je vais voir autrement,
Merci à toi

 

[Chris401]

Dis-moi quelle est la 1ère cellule concernée ?

 

[Usine à gaz]

c'est c6

 

[Chris401]

=SI(ESTNUM(TROUVE("st ";C6;1));SUBSTITUE(C6;"st ";"saint ";1);SI(ESTNUM(TROUVE("ste ";C6;1));SUBSTITUE(C6;"ste ";"sainte ";1);C6))

 

[Usine à gaz]

Merci à toi :
=SI(ESTNUM(TROUVE("st ";C6;1));SUBSTITUE(C6;"st ";"saint ";1);SI(ESTNUM(TROUVE("ste ";C6;1));SUBSTITUE(C6;"ste ";"sainte ";1);C6))
c'est bien ce que j'avais fait mais là, ça ne fonctionne plus lol

 

[Chris401]

Je t'ai envoyé un message

 

[laurent950]

Bonsoir,

Pourquoi ne pas passer par une macro VBA

 

[Usine à gaz]

Bonjour Laurent,

Merci d'être là toi aussi,

Moi, je veux bien lol
lionel,

 

[Usine à gaz]

Voici le fonctionnement du fichier :
Colonne A : j'ai les noms des communes,
Colonne B : j'ai les codes postaux des communes,
Colonne C : formule de regroupement et '()' =A6&" ("&B6&")",

Il faudrait que la formule soit faite sur cellules de base colonne "C".

Colonne E : tentative 1 "=SUBSTITUE(C6;"ste ";"sainte ";1)"

Colonne F : tentative 2 "=SI(ESTNUM(TROUVE("st ";A6;1));SUBSTITUE(A6;"st ";"saint ";1);SI(ESTNUM(TROUVE("ste ";A6;1));SUBSTITUE(A6;"ste ";"sainte ";1);A6))

Colonne G : tentative 3 "=SI(ESTNUM(TROUVE("st ";C6;1));SUBSTITUE(C6;"st ";"saint ";1);SI(ESTNUM(TROUVE("ste ";C6;1));SUBSTITUE(C6;"ste ";"sainte ";1);C6))

Les cellules surlignées en rouges sont erronées car noms des communes modifiées et faux,

Les "st" et "ste" peuvent être n'importe où dans la chaine, c'est pourquoi, il faudrait :
Remplacer par saint ou sainte si st/ste en début de chaine et/ou Remplacer par saint ou sainte si st/ste ailleurs également dans la chaine si précédé d'1 espace et si espace après.

LOL, ça à l'air coton l'affaire
lionel

 

[laurent950]

Bonsoir @Usine à gaz

Cela pourrais être bon
ici c'est la plage a traiter (Colonne C) Pour Set Rgn = [C6:C36] a adapter
est le resultat
cel.Offset(, 9).Value = Trim(NewCommune)
Régler le résultat a obtenir dans la bonne cellule
ici le texte qui a était remplacer pour "st" ou "ste" pour " Sainte "
NewCommune = reg.Replace(cel.Value, " Sainte ")

Option Explicit
Sub test()
Dim Rgn As Range
    Set Rgn = [C6:C36]
        'Rgn.Offset(, 1).Clear
Dim cel As Range
Dim Tcommune() As String
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)
        ReDim Tcommune(Matches.Count)
            For Each Match In Matches
                'Debug.Print "source >>", Match.Value
                NewCommune = reg.Replace(cel.Value, " Sainte ")
                cel.Offset(, 9).Value = Trim(NewCommune)
            Next Match
        'Tcommune = Split(NewCommune, " ")
        'Rgn = "'" & Join(Tcommune, "/")
        'cel.Offset(, 9).Value = Tcommune(1)
    Next cel
 
' libération d'objets
    Set Matches = Nothing
    Set Match = Nothing
    Set reg = Nothing
    Erase Tcommune
    Set Rgn = Nothing
    Set cel = Nothing
    NewCommune = Empty
End Sub