Formule générique
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Sommaire
Pour fractionner du texte avec un délimiteur et transformer le résultat en tableau, vous pouvez utiliser la fonction FILTERXML avec l'aide des fonctions SUBSTITUTE et TRANSPOSE. Dans l'exemple illustré, la formule en D5 est:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Remarque: FILTERXML n'est pas disponible dans Excel sur Mac ou dans Excel Online.
Remarque: j'ai appris cette astuce de Bill Jelen dans une vidéo MrExcel.
Explication
Excel n'a pas de fonction dédiée à la division de texte en un tableau, similaire à la fonction d'explosion PHP ou à la méthode de fractionnement Python. Pour contourner ce problème, vous pouvez utiliser la fonction FILTERXML après avoir ajouté un balisage XML au texte.
Dans l'exemple illustré, nous avons plusieurs chaînes de texte séparées par des virgules comme celle-ci:
"Jim,Brown,33,Seattle,WA"
Le but est de diviser les informations en colonnes séparées en utilisant la virgule comme délimiteur.
La première tâche consiste à ajouter un balisage XML à ce texte, afin qu'il puisse être analysé en tant que XML avec la fonction FILTERXML. Nous allons arbitrairement faire de chaque champ du texte un élément, entouré d'un élément parent. Nous commençons par la fonction SUBSTITUTE ici:
SUBSTITUTE(B5,",","")
Le résultat de SUBSTITUTE est une chaîne de texte comme celle-ci:
"JimBrown33SeattleWA"
Pour garantir des balises XML bien formées et pour envelopper tous les éléments dans un élément parent, nous ajoutons et ajoutons d'autres balises XML comme ceci:
""&SUBSTITUTE(B5,",","")&""
Cela donne une chaîne de texte comme celle-ci (sauts de ligne ajoutés pour la lisibilité)
" Jim Brown 33 Seattle WA "
Ce texte est livré directement à la fonction FILTERXML en tant qu'argument xml, avec une expression Xpath de "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath est un langage d'analyse et "// y" sélectionne tous les éléments. Le résultat de FILTERXML est un tableau vertical comme celui-ci:
("Jim";"Brown";33;"Seattle";"WA")
Parce que nous voulons un tableau horizontal dans cette instance, nous enroulons la fonction TRANSPOSE autour de FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Le résultat est un tableau horizontal comme celui-ci:
("Jim","Brown",33,"Seattle","WA")
qui se répand dans la plage D5: H5 dans Excel 365.
