Suppression des chaînes correspondantes par plusieurs sous-chaînes à l'aide d'une formule Excel

Question

Comment supprimer ou remplacer des mots familiers dans une chaîne de caractères par une liste de variantes familières de ces mots ?

La liste contient ~40 stubs (sous-chaînes). Avec tout cela, je m'attends à des centaines de substituts de mots correspondants.

Je suis intéressé par la solution de la formule car je sais déjà comment faire, en codant VBA.

---

J'ai joué autour de TEXTJOIN avec FILTERXML, mais il n'est pas possible de l'utiliser pour une grande liste de stub :

Formule 1. FILTERXML (talon saisi un par un)

=IFERROR(TEXTJOIN(" ";;FILTERXML("<A><B>"&SUBSTITUTE(A2;" ";"</B><B>")&"</B></A>";"//B[not(contains(., 'auti') or contains(., 'AuTi') or contains(., 'tion') or contains(., 'mpl') or contains(., 'Mpl')or contains(., 'etc'))]"));"")

Le résultat est bon, mais malheureusement il est sensible à la casse, donc les substrats entrés un par un pour toutes les versions de capitalisation (Propercase, UPERCASE, lowercase, MixEdCase) génèrent une très grande formule impossible à utiliser à cause de la limitation de longueur (255 caractères).

Formule 2. FILTERXML (avec traduction de cas)

=IFERROR(TEXTJOIN(" ";;FILTERXML("<T><S>"&SUBSTITUTE(A2;" ";"</S><S>")&"</S></T>";"//S[not(contains(translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz'),'auti')or contains(., 'tion')or contains(., 'mpl')or contains(., 'etc'))]"));"")

Le résultat est apparemment bon, mais il y a d'étranges correspondances de capitalisation manquées (vérifiez la cellule D14 dans l'image jointe). Pour éviter cela, je peux utiliser une traduction pour chaque stub translate(., 'AUTI', 'auti'),'auti') mais la limitation de la longueur ne me permettra pas d'utiliser toute la liste.

Formule 3. FILTERXML (avec LOWER-ing toutes les chaînes)

=IFERROR(TEXTJOIN(" ";;FILTERXML("<A><B>"&SUBSTITUTE(LOWER(A2);" ";"</B><B>")&"</B></A>";"//B[not(contains(., 'auti')or contains(., 'tion') or contains(., 'mpl') or contains(., 'etc'))]"));"")

Le résultat est bon, mais la chaîne de sortie est totalement inférieure. Et la limitation de longueur est toujours présente.

Je sais que FILTERXML est un bon moyen pour cette tâche, mais je ne sais pas comment travailler avec. Malheureusement Xpath 1.0 ne permet pas des choses comme matches()

Answer 1

Très bonne question, mais vous étendez les capacités d'une formule créée par Excel-2019 jusqu'à ses limites. Voici ce que j'ai trouvé, en tenant compte des limites de xpath 1.0 (pas d'utilisation de matches() ni la gestion des listes et des tableaux :

Formule en B1 :

=TEXTJOIN(" ",,IF(ROW(A$1:INDEX(A:A,LEN(A2)))=TRANSPOSE(FILTERXML("<x><t>"&TEXTJOIN("</s></t><t>",,IFERROR(ROW(A$1:INDEX(A:A,COUNTA(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"))))&"<s>"&FILTERXML("<t><s>"&SUBSTITUTE(LOWER(A2)," ","</s><s>")&"</s></t>","//s"),""))&"</s></t></x>","//t[.//*[not(contains(., '"&TEXTJOIN("') or contains (., '",,{"auti","tion","mpl","etc"})&"'))]]")),FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"),""))

Je l'ai testé avec un tableau de 40 éléments de 3 à 4 caractères de long et cela a fonctionné. no dépasser la limite des deux TEXTJOIN() ni la limite de caractères qu'il peut gérer.

Il s'agit d'une formule à entrée par tableau (CSE).

Pour résumer ce que cela fait :

• Divisez votre entrée sur l'espace (gardez les spécifications de la casse intactes) ;
• Créez une nouvelle chaîne d'entrée en utilisant TEXTJOIN() d'utiliser un autre niveau d'enfants xpath pour donner à chaque mot un index ;
• Avec FILTERXML() nous retournons chaque index qui ne contient aucun des éléments du tableau que vous avez spécifié ;
• Utilisez ces nombres pour récupérer tous les noeuds de la première étape et les concaténer ensemble ;
• Notez que cela aura un impact sur la ponctuation.

---

Notez que cela deviendra nettement plus facile avec la fonction d'aide lambda de ms365 REDUCE() :

=REDUCE(A1,{"auti","tion","mpl","etc"},LAMBDA(a,b,TEXTJOIN(" ",,FILTERXML("<t><s>"&SUBSTITUTE(a," ","</s><s>")&"</s></t>","//s[not(contains(translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz'), '"&b&"'))]"))))