Séparer un texte dans Excel

Dans Excel, la technique la plus simple pour découper rapidement un texte sur un délimiteur c'est d'utiliser l'outil Convertir.

Seulement, avec cette technique, le résultat se fait nécessairement depuis la gauche. Et dans le cas de figure présent, le résultat est peu exploitable car ville ou pays ne sont pas dans les mêmes colonnes. Ceci est dû au fait que certaines adresses on plus de détails que d'autres. Donc, comme le nombre de virgules n'est pas le même, ceci engendre ce décalage. Donc dans un tel cas de figure, il faut ordonner le fractionnement depuis la droite.

Pour simplifier le travail de manipulation des données, Power Query est l'outil idéal. Power Query est un outil remarquable et extrêmement puissant pour toutes les opérations de manipulation sur les fichiers. Si une transformation de fichiers est complexe avec les fonctions standard d'Excel, pensez que Power Query pourra vous simplifier le travail. 😉

Etape 1 : Importer vos données dans Power Query

Télécharger le fichier suivant pour suivre les explications.

1. Sélectionnez tout votre document à transformer.
2. Cliquez sur le menu Données > A partir d'un tableau ou d'une plage.

Instantanément, Power Query s'ouvre avec vos données prêtes à être modifiées.

Etape 2 : Ajouter une colonne personnalisée

La manipulation que nous voulons faire, n'existe pas telle quelle dans les options proposées par Power Query dans le ruban ou par clic-droit. Donc nous allons devoir écrire nous-mêmes notre formule depuis le menu Ajouter une colonne > Colonne personnalisée.

Etape 3 : Créer une List

Une list dans Power Query correspond à la création d'un tableau en mémoire. Rien de compliqué ici, c'est comme si le résultat était stocké dans une colonne temporaire.

Nous allons commencer par découper le contenu de la colonne Adresse sur le séparateur "virgule" grâce à l'instruction Text.Split.

=Text.Split([Adresse], ",")

Le résultat est visible en cliquant sur l'une des cellules contenant le mot List. MAIS ne cliquez pas sur ce mot, juste sélectionnez la cellule.

Etape 4 : Inverser le résultat

Toute l'astuce va consister à inverser le résultat des listes pour faire apparaître le pays en première position.

=List.Reverse(Text.Split([Adresse], ","))

Etape 5 : Transformer en Table

Dans Power Query, pour retourner un résultat exploitable, il faut l'intégrer dans une table (c'est le terme). Donc nous allons rajouter à l'écriture précédente une instruction pour faire cette transformation.

=Table.FromRows({List.Reverse(Text.Split([Adresse], ","))})

L'ajout des accolades { et } autour de l'écriture précédente est obligatoire pour que l'instruction Table.FromRows puisse être comprise.

Voici l'écriture de la fonction dans l'étape de la fonction personnalisée.

Etape 6 : Déployer la table

Ici c'est très simple, il faut juste déployer la colonne contenant le résultat en cliquant sur l'icône de droite. Pour être sûr de n'oublier aucune colonne, il faut cliquer sur l'option Charger Plus.

Etape 7 : Réordonner les colonnes

Dernière astuce, pour présenter le résultat dans l'ordre attendu, il suffit de ré-écrire l'ordre dans lequel vous voulez voir le résultat. 😉

Charger dans Excel

Changez le nom des colonnes pour rendre vos données plus exploitables et chargez-les dans Excel.

Le résultat est le suivant (fichier) :

Vous trouverez des informations complémentaires sur séparer un texte avec Power Query sur le site de Microsoft.