Extraire du texte avec Excel : Le guide complet des fonctions SUBSTRING

Comprendre les fonctions SUBSTRING dans Excel

Le terme "SUBSTRING" n'est pas une fonction Excel à proprement parler. Il s'agit d'un terme générique désignant les fonctions permettant d'extraire une partie d'une chaîne de texte. Excel propose plusieurs fonctions pour réaliser cette opération, chacune ayant ses spécificités. Les plus courantes sont GAUCHE, DROITE et STXT. Comprendre leur fonctionnement est essentiel pour manipuler efficacement le texte dans vos feuilles de calcul.

Les principales fonctions SUBSTRING d'Excel

• GAUCHE(texte, nombre_caractères): Cette fonction extrait un nombre spécifié de caractères à partir du début (gauche) d'une chaîne de texte.
• DROITE(texte, nombre_caractères): Cette fonction extrait un nombre spécifié de caractères à partir de la fin (droite) d'une chaîne de texte.
• STXT(texte, position_départ, nombre_caractères): Cette fonction extrait un nombre spécifié de caractères à partir d'une position de départ spécifiée dans une chaîne de texte. C'est la fonction la plus polyvalente des trois.

Pourquoi utiliser les fonctions SUBSTRING ?

Les fonctions SUBSTRING sont utiles dans de nombreux cas de figure:

• Nettoyage de données: Extraire des informations pertinentes à partir de données brutes ou mal formatées.
• Analyse de données: Segmenter des données textuelles pour faciliter l'analyse et la recherche.
• Automatisation: Créer des formules qui extraient automatiquement des informations à partir de données variables.
• Création de rapports: Mettre en forme des données textuelles pour une présentation claire et concise.

La fonction GAUCHE : Extraire le début d'une chaîne

La fonction GAUCHE est la plus simple des fonctions SUBSTRING. Elle prend deux arguments:

• texte: La chaîne de texte à partir de laquelle extraire les caractères.
• nombre_caractères: Le nombre de caractères à extraire à partir du début de la chaîne.

Syntaxe de la fonction GAUCHE

La syntaxe de la fonction GAUCHE est la suivante:

=GAUCHE(texte, nombre_caractères)

Exemple d'utilisation de la fonction GAUCHE

Supposons que vous ayez une liste de codes produits dans la colonne A, et que vous souhaitiez extraire les trois premiers caractères de chaque code pour identifier la catégorie du produit. Vous pouvez utiliser la formule suivante dans la colonne B:

=GAUCHE(A1, 3)

Cette formule extraira les trois premiers caractères du code produit situé dans la cellule A1 et les affichera dans la cellule B1. Par exemple, si A1 contient "ABC123", B1 affichera "ABC".

Astuces pour utiliser la fonction GAUCHE

• Si nombre_caractères est omis, Excel extrait un seul caractère.
• Si nombre_caractères est supérieur à la longueur de la chaîne de texte, Excel renvoie la chaîne de texte entière.
• La fonction GAUCHE est sensible à la casse.

La fonction DROITE : Extraire la fin d'une chaîne

La fonction DROITE est similaire à la fonction GAUCHE, mais elle extrait les caractères à partir de la fin (droite) de la chaîne de texte. Elle prend également deux arguments:

• texte: La chaîne de texte à partir de laquelle extraire les caractères.
• nombre_caractères: Le nombre de caractères à extraire à partir de la fin de la chaîne.

Syntaxe de la fonction DROITE

La syntaxe de la fonction DROITE est la suivante:

=DROITE(texte, nombre_caractères)

Exemple d'utilisation de la fonction DROITE

Supposons que vous ayez une liste de numéros de téléphone dans la colonne A, et que vous souhaitiez extraire les quatre derniers chiffres de chaque numéro pour identifier l'extension interne. Vous pouvez utiliser la formule suivante dans la colonne B:

=DROITE(A1, 4)

Cette formule extraira les quatre derniers chiffres du numéro de téléphone situé dans la cellule A1 et les affichera dans la cellule B1. Par exemple, si A1 contient "0123456789", B1 affichera "6789".

Astuces pour utiliser la fonction DROITE

• Si nombre_caractères est omis, Excel extrait un seul caractère.
• Si nombre_caractères est supérieur à la longueur de la chaîne de texte, Excel renvoie la chaîne de texte entière.
• La fonction DROITE est sensible à la casse.

La fonction STXT : Extraire une portion spécifique d'une chaîne

La fonction STXT (pour "Sous-Texte") est la plus polyvalente des fonctions SUBSTRING. Elle permet d'extraire une portion spécifique d'une chaîne de texte en spécifiant la position de départ et le nombre de caractères à extraire. Elle prend trois arguments:

• texte: La chaîne de texte à partir de laquelle extraire les caractères.
• position_départ: La position du premier caractère à extraire (le premier caractère est en position 1).
• nombre_caractères: Le nombre de caractères à extraire.

Syntaxe de la fonction STXT

La syntaxe de la fonction STXT est la suivante:

=STXT(texte, position_départ, nombre_caractères)

Exemple d'utilisation de la fonction STXT

Supposons que vous ayez une liste de références de produits dans la colonne A, et que vous souhaitiez extraire le code de couleur (trois caractères) situé à partir du 5ème caractère de chaque référence. Vous pouvez utiliser la formule suivante dans la colonne B:

=STXT(A1, 5, 3)

Cette formule extraira trois caractères à partir de la position 5 de la référence située dans la cellule A1 et les affichera dans la cellule B1. Par exemple, si A1 contient "REF-BLU-123", B1 affichera "BLU".

Astuces pour utiliser la fonction STXT

• Si position_départ est supérieure à la longueur de la chaîne de texte, Excel renvoie une chaîne vide (" ").
• Si nombre_caractères est supérieur à la longueur de la chaîne de texte à partir de position_départ, Excel extrait tous les caractères restants.
• La fonction STXT est sensible à la casse.

Combiner les fonctions SUBSTRING avec d'autres fonctions Excel

La puissance des fonctions SUBSTRING est décuplée lorsqu'elles sont combinées avec d'autres fonctions Excel. Voici quelques exemples:

Utiliser CHERCHE ou TROUVE pour déterminer la position de départ

Les fonctions CHERCHE et TROUVE permettent de localiser la position d'un caractère ou d'une chaîne de caractères dans une autre chaîne de caractères. Vous pouvez utiliser ces fonctions pour déterminer dynamiquement la position de départ dans la fonction STXT.

Par exemple, supposons que vous ayez une liste d'adresses e-mail dans la colonne A, et que vous souhaitiez extraire le nom d'utilisateur (avant le signe @). Vous pouvez utiliser la formule suivante:

=GAUCHE(A1,CHERCHE("@",A1)-1)

Cette formule utilise la fonction CHERCHE pour trouver la position du signe @ dans l'adresse e-mail, puis soustrait 1 pour obtenir la position du dernier caractère du nom d'utilisateur. La fonction GAUCHE extrait ensuite le nom d'utilisateur à partir du début de l'adresse e-mail.

Utiliser NBCAR pour déterminer le nombre de caractères à extraire

La fonction NBCAR renvoie le nombre de caractères d'une chaîne de texte. Vous pouvez utiliser cette fonction pour déterminer dynamiquement le nombre de caractères à extraire dans les fonctions GAUCHE, DROITE ou STXT.

Par exemple, supposons que vous ayez une liste de noms complets (Prénom Nom) dans la colonne A, et que vous souhaitiez extraire le nom de famille. Vous pouvez utiliser la formule suivante:

=DROITE(A1,NBCAR(A1)-CHERCHE(" ",A1))

Cette formule utilise la fonction CHERCHE pour trouver la position de l'espace entre le prénom et le nom, puis soustrait cette position du nombre total de caractères (obtenu avec NBCAR) pour obtenir le nombre de caractères du nom de famille. La fonction DROITE extrait ensuite le nom de famille à partir de la fin du nom complet.

Utiliser SUBSTITUE pour nettoyer les données avant l'extraction

La fonction SUBSTITUE permet de remplacer une chaîne de caractères par une autre dans une chaîne de texte. Vous pouvez utiliser cette fonction pour nettoyer les données avant d'utiliser les fonctions SUBSTRING, par exemple pour supprimer les espaces inutiles ou les caractères spéciaux.

Erreurs courantes et comment les éviter

Voici quelques erreurs courantes lors de l'utilisation des fonctions SUBSTRING, et comment les éviter:

• Erreur #VALEUR!: Cette erreur se produit généralement lorsque l'un des arguments des fonctions SUBSTRING n'est pas valide. Vérifiez que les arguments texte, position_départ et nombre_caractères sont des valeurs valides.
• Erreur #NOM?: Cette erreur se produit lorsque le nom de la fonction est mal orthographié. Vérifiez que vous avez correctement orthographié les fonctions GAUCHE, DROITE et STXT.
• Résultats inattendus: Si vous obtenez des résultats inattendus, vérifiez que les arguments position_départ et nombre_caractères sont corrects. N'oubliez pas que la position du premier caractère est 1, et non 0.
• Sensibilité à la casse: Les fonctions SUBSTRING sont sensibles à la casse. Si vous devez extraire du texte sans tenir compte de la casse, vous pouvez utiliser les fonctions MAJUSCULE ou MINUSCULE pour convertir le texte en majuscules ou en minuscules avant d'utiliser les fonctions SUBSTRING.

Bonnes pratiques pour utiliser les fonctions SUBSTRING

Voici quelques bonnes pratiques pour utiliser les fonctions SUBSTRING efficacement:

• Utilisez des noms de cellules explicites: Au lieu d'utiliser des références de cellules directes (par exemple, A1), utilisez des noms de cellules explicites (par exemple, CodeProduit). Cela rendra vos formules plus faciles à comprendre et à maintenir.
• Divisez les formules complexes en étapes plus simples: Si vous avez une formule complexe qui utilise plusieurs fonctions SUBSTRING, divisez-la en étapes plus simples en utilisant des colonnes intermédiaires. Cela rendra la formule plus facile à déboguer et à comprendre.
• Utilisez des commentaires pour expliquer vos formules: Ajoutez des commentaires à vos formules pour expliquer ce qu'elles font. Cela aidera les autres utilisateurs (et vous-même dans le futur) à comprendre vos formules.
• Testez vos formules avec des données variées: Assurez-vous de tester vos formules avec des données variées pour vous assurer qu'elles fonctionnent correctement dans tous les cas de figure.

Conclusion

Les fonctions SUBSTRING d'Excel sont des outils puissants pour manipuler le texte. En comprenant leur fonctionnement et en les combinant avec d'autres fonctions Excel, vous pouvez extraire, couper et transformer votre texte en un clin d'œil. Que vous ayez besoin de nettoyer des données, d'analyser des informations ou d'automatiser des tâches, les fonctions GAUCHE, DROITE et STXT vous seront d'une aide précieuse. N'hésitez pas à expérimenter avec ces fonctions et à les adapter à vos besoins spécifiques. Avec un peu de pratique, vous deviendrez un expert de la manipulation de texte dans Excel !