Comment faire un "Excel Split String" facilement ? Méthodes et Astuces

Excel Split String : Diviser une chaîne de caractères, mode d'emploi

L'opération "Excel split string", qui consiste à diviser une chaîne de caractères en plusieurs parties, est une tâche courante lorsqu'on travaille avec des données importées ou mal structurées dans Excel. Heureusement, Excel propose plusieurs outils et fonctions pour réaliser cette opération efficacement. Nous allons explorer les méthodes les plus courantes, en commençant par la plus simple : l'outil "Convertir".

1. Utiliser l'outil "Convertir" (Données > Convertir)

L'outil "Convertir" est la méthode la plus simple et la plus rapide pour diviser une chaîne de caractères basée sur un délimiteur commun (par exemple, une virgule, un point-virgule, un espace, etc.).

Étapes :

1. Sélectionnez la colonne contenant les chaînes de caractères à diviser.
2. Allez dans l'onglet "Données" du ruban Excel.
3. Cliquez sur le bouton "Convertir". L'Assistant Conversion s'ouvre.
4. Dans la première étape, choisissez "Délimité" et cliquez sur "Suivant".
5. Dans la deuxième étape, sélectionnez le délimiteur utilisé dans votre chaîne de caractères (par exemple, "Virgule", "Espace", "Point-virgule", ou "Autre" si le délimiteur n'est pas dans la liste). Vous pouvez voir un aperçu de la division dans la partie inférieure de la fenêtre.
6. Dans la troisième étape, vous pouvez choisir le format de données pour chaque colonne résultante (par exemple, "Texte", "Date", "Standard"). Vous pouvez également choisir de ne pas importer certaines colonnes en sélectionnant "Ne pas importer cette colonne (ignorer)".
7. Cliquez sur "Terminer". Les données sont divisées en plusieurs colonnes.

Exemple :

Supposons que vous ayez une colonne contenant des noms et prénoms séparés par une virgule (par exemple, "Dupont, Jean"). En utilisant l'outil "Convertir" avec la virgule comme délimiteur, vous obtiendrez deux colonnes distinctes : une pour le nom ("Dupont") et une pour le prénom ("Jean").

Capture d'écran (description textuelle) :

• Une capture d'écran montrant une feuille Excel avec une colonne contenant des noms et prénoms séparés par une virgule. La colonne est sélectionnée. L'onglet "Données" est mis en évidence, avec le bouton "Convertir" entouré d'un rectangle rouge.
• Une capture d'écran montrant l'Assistant Conversion, étape 2, avec l'option "Virgule" sélectionnée comme délimiteur et un aperçu de la division du nom et prénom en deux colonnes.

Avantages :

• Simple et rapide à utiliser.
• Idéal pour les chaînes de caractères avec un délimiteur commun.

Inconvénients :

• Ne fonctionne que pour un seul délimiteur à la fois.
• Moins flexible que les formules Excel.

2. Utiliser les fonctions Excel : GAUCHE, DROITE, STXT, CHERCHE, et LONGUEUR

Pour des divisions plus complexes ou lorsque vous avez besoin de plus de contrôle, vous pouvez utiliser les fonctions Excel suivantes :

• GAUCHE(texte, nombre_de_caractères) : Extrait un certain nombre de caractères à partir du début d'une chaîne de texte.
• DROITE(texte, nombre_de_caractères) : Extrait un certain nombre de caractères à partir de la fin d'une chaîne de texte.
• STXT(texte, position_départ, nombre_de_caractères) : Extrait un certain nombre de caractères à partir d'une chaîne de texte, en commençant à une position spécifiée.
• CHERCHE(texte_cherché, texte_dans_lequel_chercher, [position_départ]) : Renvoie la position de départ d'une chaîne de texte à l'intérieur d'une autre chaîne de texte.
• LONGUEUR(texte) : Renvoie le nombre de caractères dans une chaîne de texte.

Exemple :

Supposons que vous ayez une colonne contenant des adresses e-mail (par exemple, "jean.dupont@example.com") et que vous souhaitiez extraire le nom d'utilisateur (avant le "@"). Vous pouvez utiliser les formules suivantes :

• Pour extraire le nom d'utilisateur : =GAUCHE(A1;CHERCHE("@";A1)-1)

  • A1 représente la cellule contenant l'adresse e-mail.
  • CHERCHE("@";A1) recherche la position du caractère "@" dans l'adresse e-mail.
  • -1 soustrait 1 à la position du "@" pour exclure le caractère "@" lui-même.
  • GAUCHE extrait le nombre de caractères spécifié (la position du "@" moins 1) à partir du début de l'adresse e-mail.

Autre exemple : Extraire le nom de domaine (après le "@")

• =DROITE(A1;LONGUEUR(A1)-CHERCHE("@";A1))

Explication :

• LONGUEUR(A1) calcule la longueur totale de l'adresse e-mail.
• CHERCHE("@";A1) recherche la position du caractère "@" dans l'adresse e-mail.
• LONGUEUR(A1)-CHERCHE("@";A1) calcule le nombre de caractères après le "@".
• DROITE extrait le nombre de caractères calculé à partir de la fin de l'adresse e-mail.

Capture d'écran (description textuelle) :

• Une capture d'écran montrant une feuille Excel avec une colonne contenant des adresses e-mail. Une colonne adjacente contient la formule =GAUCHE(A1;CHERCHE("@";A1)-1) et affiche le nom d'utilisateur extrait de l'adresse e-mail. Une autre colonne contient la formule =DROITE(A1;LONGUEUR(A1)-CHERCHE("@";A1)) et affiche le nom de domaine.

Avantages :

• Très flexible et puissant.
• Permet de gérer des divisions complexes.

Inconvénients :

• Peut être plus complexe à comprendre et à utiliser pour les débutants.
• Nécessite une bonne compréhension des fonctions Excel.

3. Utiliser la fonction TEXTE.AVANT et TEXTE.APRES (Excel 365 et versions ultérieures)

Excel 365 et les versions ultérieures introduisent deux nouvelles fonctions très pratiques pour la division de chaînes de caractères : TEXTE.AVANT et TEXTE.APRES.

• TEXTE.AVANT(texte, délimiteur, [instance_num], [correspondance_mode], [fin_si_vide]) : Extrait le texte qui précède un délimiteur spécifié.
• TEXTE.APRES(texte, délimiteur, [instance_num], [correspondance_mode], [fin_si_vide]) : Extrait le texte qui suit un délimiteur spécifié.

Exemple :

Reprenons l'exemple des adresses e-mail (jean.dupont@example.com). Pour extraire le nom d'utilisateur et le nom de domaine, vous pouvez utiliser les formules suivantes :

• Pour extraire le nom d'utilisateur : =TEXTE.AVANT(A1;"@")
• Pour extraire le nom de domaine : =TEXTE.APRES(A1;"@")

Explication :

• A1 représente la cellule contenant l'adresse e-mail.
• "@" est le délimiteur.
• Les arguments optionnels (instance_num, correspondance_mode, fin_si_vide) ne sont pas utilisés dans cet exemple simple.

Capture d'écran (description textuelle) :

• Une capture d'écran montrant une feuille Excel avec une colonne contenant des adresses e-mail. Une colonne adjacente contient la formule =TEXTE.AVANT(A1;"@") et affiche le nom d'utilisateur extrait de l'adresse e-mail. Une autre colonne contient la formule =TEXTE.APRES(A1;"@") et affiche le nom de domaine.

Avantages :

• Très simple et facile à utiliser.
• Plus intuitif que les combinaisons de fonctions GAUCHE, DROITE, STXT, CHERCHE, et LONGUEUR.

Inconvénients :

• Disponible uniquement dans Excel 365 et les versions ultérieures.

4. Utiliser Power Query (Obtenir et Transformer des données)

Power Query, disponible dans les versions récentes d'Excel (sous l'onglet "Données" > "Obtenir et Transformer des données"), est un outil puissant pour l'importation, la transformation et le nettoyage des données. Il offre également des fonctionnalités avancées pour la division de chaînes de caractères.

Étapes :

1. Sélectionnez la colonne contenant les chaînes de caractères à diviser.
2. Allez dans l'onglet "Données" et cliquez sur "À partir d'un tableau/plage". Cela ouvre l'éditeur Power Query.
3. Dans l'éditeur Power Query, sélectionnez la colonne que vous souhaitez diviser.
4. Allez dans l'onglet "Accueil" et cliquez sur "Diviser la colonne". Vous pouvez choisir de diviser par délimiteur, par nombre de caractères, par position, etc.
5. Configurez les options de division (délimiteur, nombre d'occurrences, etc.).
6. Cliquez sur "OK". La colonne est divisée en plusieurs colonnes.
7. Cliquez sur "Fermer et charger" ou "Fermer et charger dans..." pour charger les données transformées dans une feuille Excel.

Avantages :

• Très puissant et flexible.
• Permet de réaliser des transformations complexes.
• Idéal pour l'automatisation des tâches de nettoyage de données.

Inconvénients :

• Peut être plus complexe à apprendre que les autres méthodes.
• Nécessite une certaine familiarité avec l'interface Power Query.

Conseils et astuces pour un Excel Split String réussi

• Nettoyez vos données avant de diviser : Supprimez les espaces inutiles, les caractères spéciaux ou les erreurs de frappe qui pourraient affecter la division.
• Choisissez la méthode la plus appropriée : L'outil "Convertir" est idéal pour les divisions simples, tandis que les fonctions Excel et Power Query sont plus adaptées aux divisions complexes.
• Testez vos formules : Vérifiez que vos formules fonctionnent correctement sur un échantillon de données avant de les appliquer à l'ensemble de la colonne.
• Utilisez la fonction SIERREUR : Pour gérer les erreurs potentielles (par exemple, si une chaîne de caractères ne contient pas le délimiteur spécifié), utilisez la fonction SIERREUR pour renvoyer une valeur par défaut ou un message d'erreur personnalisé.
• Documentez vos formules : Ajoutez des commentaires à vos formules pour expliquer leur fonctionnement et faciliter leur compréhension ultérieure.

Erreurs courantes à éviter

• Oublier de spécifier le délimiteur correct : Assurez-vous d'utiliser le bon délimiteur lors de la division des chaînes de caractères.
• Ne pas gérer les cas où le délimiteur est absent : Si certaines chaînes de caractères ne contiennent pas le délimiteur spécifié, vos formules peuvent renvoyer des erreurs. Utilisez la fonction SIERREUR pour gérer ces cas.
• Ne pas tenir compte des espaces inutiles : Les espaces inutiles peuvent affecter la division des chaînes de caractères. Utilisez la fonction SUPPRESPACE pour les supprimer.

Conclusion

Maîtriser l'opération "Excel split string" est essentiel pour travailler efficacement avec des données dans Excel. Que vous utilisiez l'outil "Convertir", les fonctions Excel, TEXTE.AVANT et TEXTE.APRES ou Power Query, vous disposez désormais des outils nécessaires pour diviser vos chaînes de caractères et organiser vos données de manière optimale. N'hésitez pas à expérimenter avec les différentes méthodes et à adapter les formules à vos besoins spécifiques. Avec un peu de pratique, vous deviendrez un expert de la division de chaînes sur Excel !