Mise à jour : Une nouvelle fonction d'Excel 365, la fonction FRACTIONNER.TEXTE permet de simplifier le travail de séparer un texte par formule.

• La méthode ci-dessous n'est à utiliser que si vous n'avez pas la fonction FRACTIONNER.TEXTE sur votre version d'Excel.

Plusieurs méthodes pour séparer un texte sur un délimiteur

Dans Excel, il existe plusieurs techniques pour séparer le contenu d'une cellule selon un délimiteur.

• Il y a la technique avec l'outil Convertir ou plus récemment Power Query qui propose des options supplémentaires très intéressantes.
• Mais grâce aux fonctions propagées d'Office 365 ou Excel Online, il est possible de séparer un texte sur un délimiteur avec une formule.

Nous allons partir de l'exemple de cette liste d'adresses pour illustrer la technique.

Utilisation de la fonction FILTRE.XML

La fonction FILTRE.XML sert à découper un fichier XML en respectant le XPath. Houlà ? C'est quoi ce charabia ? 😯

Le format XML est un format d'écriture, peu lisible pour nous les humains, mais très efficace pour une machine. En fait, on parle de fichiers structurés quand on parle de fichiers XML comme le montre l'image ci-dessous.

Chaque élément d'un fichier XML est caractérisé par une balise, comme <result>, <short_name>, ... La hiérarchie entre ces balises répond à une norme appelé XPath. Donc l'astuce, c'est de construire une chaîne de caractères qui va intégrer notre adresse comme dans une structure XML comme celle-ci.

<root>
 <node>35 Rue de la République</node>
 <node>21250 Seurre</node>
 <node>France</node>
</root>

Formule pour séparer un texte sur un délimiteur

Donc dans un premier temps, nous allons construire l'adresse sous la forme d'un fichier XML.

="<root><node>"&A2&"</node></root>"

Mais cette écriture n'est pas finie car le résultat est pour l'instant le suivant :

<root>
 <node>35 Rue de la République, 21250 Seurre, France</node>
</root>

Donc, nous allons remplacer le séparateur de notre texte, ici la virgule, par un niveau hiérarchique grâce à la fonction SUBSTITUE

="<root><node>"&SUBSTITUE(A2;",";"</node><node>")&"</node></root>"

Et toute cette écriture est à englober dans la formule FILTRE.XML avec comme XPath la balise @//node.

=FILTRE.XML("<root><node>"&SUBSTITUE(A2;",";"</node><node>")&"</node></root>";"@//node")

Changer l'orientation

Seulement, juste comme cela, la formule n'est pas pratique car le résultat est renvoyé verticalement. Pour changer l'orientation, il suffit d'écrire cette formule dans la fonction TRANSPOSE.

=TRANSPOSE(FILTRE.XML("<root><node>"&SUBSTITUE(A2;",";"</node><node>")&"</node></root>";"@//node"))

Fonction personnalisée avec LAMBDA

Comme la formule précédente ne peut s'écrire qu'avec Excel 365, dans cette version vous avez également la fonction LAMBDA. La fonction LAMBDA vous permet d'écrire vos propres fonctions personnalisées. Dans ce cas de figure, nous pouvons créer la fonction SEPARER.TEXTE.

Et avec comme formule :

=LAMBDA(monTexte;Separateur;TRANSPOSE(FILTRE.XML(""&SUBSTITUE(monTexte;Separateur;"
")&"";"//node")))

Articles liés

• La fonction FRACTIONNER.TEXTE dans Excel
• TEXTE.AVANT et TEXTE.APRES
• Extraire les mots à droite d’un délimiteur
• Séparer un texte avec alignement à droite
• Séparer un texte sur la virgule dans Excel
• Vous trouverez des informations complémentaires sur les fonctions SUBSTITUE, TRANSPOSE et FILTRE.XML sur le site de Microsoft.