Expert Matricielles

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

Temps de lecture 2 minutes

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 matricielles dynamiques 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'adresse pour illustrer la technique.

Adresses a separer sur le delimiteur virgule 1

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 charabiât ? 😕🤔🤨

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.

Exemple fichier XML

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 écrire 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 écrire est à englober dans la formule FILTRE.XML avec comme XPath la balise @//node.

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

Formule pour separer un texte sur un delimiteur

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"))

Ecrire une fonction matricielle dynamique horizontalement

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 fonction personnalisée. 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")))

Fonction LAMBDA pour separer un texte en colonne

Le résultat est le suivant

Fonction personnalisee pour separer du texte

Related posts

Extraire un échantillon avec Excel

Frédéric LE GUEN

Extraire sans doublon avec formule

Frédéric LE GUEN

Prévision des valeurs futures

Frédéric LE GUEN

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.