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,

 

[laurent950]

C'est fait mais le code ne traite que les lignes 6-7-11-12-13 et 25

Le code devrais traiter qu'elle autres lignes ?

 

[Usine à gaz]

ça à l'ai super bien mais comment modifier pour le code traite toutes les lignes jusqu'à la dernière ligne NON VIDE ?
Actuellement de la ligne 6 à la ligne 25 mais ça ira au moins jusqu'à 40000 lignes

 

[laurent950]

ça à l'ai super bien mais comment modifier pour le code traite toutes les lignes jusqu'à la dernière ligne NON VIDE ?
Actuellement de la ligne 6 à la ligne 25 mais ça ira au moins jusqu'à 40000 lignes

Set Rgn = Range(Cells(6, 3), Cells(Cells(65536, 3).End(xlUp).Row, 3))
Range
Cells(6, 3) C'est la cellule Ligne 6 Colonne 3 soit C6
Cells(65536, 3).End(xlUp).Row = C'est la derniere cellule non vide de la colonne 3 (Le Numéro de la ligne)
C'est la colonne 3 soit la colonne C
Et donc
Cells(Le numéro de la derniere ligne non vide de la colonne 3, C'est la colonne 3))
et donc
Range(la plage de la cellule C6 à la dernière cellule non vide de la colonne C)

40000 lignes je vais jusqu'à 65536

 

[Usine à gaz]

Ben OUI, j'ai bien compris mais ça ne traite pas lol
Je joins le fichier, tu verras certainement si j'ai fait une bêtise.

 

[job75]

Si tu veux appliquer la formule sur les lignes vides également utilise la fonction REPT, en C6 :

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

 

[laurent950]

Je joins le fichier, tu verras certainement si j'ai fait une bêtise.

Re @Usine à gaz
je ne vois aucune erreur !
donnez moi une ligne qui aurait du être traité sur votre fichier joins et que la macro VBA n'a pas traité ?

 

[Usine à gaz]

Merci Gérard
Parfait comme toujours

 

[Usine à gaz]

Re @Usine à gaz
je ne vois aucune erreur !
donnez moi une ligne qui aurait du être traité sur votre fichier joins et que la macro VBA n'a pas traité ?

lol mais j'ai joint le fichier
lignes à traiter de la 6 à la 25

 

[laurent950]

lol mais j'ai joint le fichier
lignes à traiter de la 6 à la 25

Re @Usine à gaz

Replace avec Mutli Critères "st" ou "ste" a l'aide d'une Regex.

J'ai modifier pour la prise en compte des compléments des lignes qui ne correspondent pas a "st" et "ste" pour les recopier dans la colonne C

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

 

[Usine à gaz]

C'est bon, il traite toutes les lignes
Mais il me remplace tout par sainte et pas les "st" par saint.
pardon pour le travail que je te donne
lionel,

 

[laurent950]

Mais il me remplace tout par sainte et pas les "st" par saint.

J'ai modifier le code en Poste #39
NewCommune = reg.Replace(cel.Value, " sainte ")
Par
NewCommune = reg.Replace(cel.Value, " saint ")

 

[Usine à gaz]

lol maintenant Mais il me remplace tout par saint et pas les "ste" par sainte.

 

[laurent950]

lol maintenant Mais il me remplace tout par saint et pas les "ste" par sainte.

@Usine à gaz

J'ai amélioré : "st" = "saint" et pour "ste" = "sainte"

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

 

[Usine à gaz]

Cette fois-ci il prend bien les sainte et saint.
Mais voilà le souci qui apparait :
st aubin ste vaast (62140)
donne en résultat : sainte aubin sainte vaast (62140)
alors qu'il faudrait : saint aubin sainte vaast (62140)

Je suis vraiment désolé du temps que tu passes à mon besoin

 

[Usine à gaz]

Re-Bonsoir,

Je dois me lever très tôt demain matin pour préparer le boulot de la journée.
Je vais me coucher car debout depuis 5h ce matin, je ne tiens plus.
Bonne nuit à toutes et à tous,
lionel Zzzzzzzzzzzzzzzz .....