Techniques de sous-chaîne dans Excel : extraire et formater du texte

Extraire du texte par position avec les fonctions de sous-chaîne d’Excel

Il est fréquent de vouloir extraire des parties spécifiques d’une chaîne, comme un mot ou une séquence de caractères, en fonction de leur position. Excel propose plusieurs fonctions intégrées pour vous aider à le faire. Les plus courantes sont LEFT(), RIGHT(), MID(), TEXTBEFORE() et TEXTAFTER(). Voyons-les une par une. 

Utiliser la fonction Excel LEFT()

La fonction LEFT() extrait des caractères depuis le début d’une chaîne. Sa syntaxe est :

=LEFT(text, [num_chars])

Où :

• text désigne la cellule contenant la chaîne d’origine.

• num_chars indique le nombre de caractères à extraire. Sa valeur par défaut est 1.

Voyons un exemple. Ici, j’ai des codes produits et je souhaite extraire les trois premiers caractères.

=LEFT(A2,3)

Récupérer les trois premiers caractères avec la fonction LEFT(). Image par l’auteur.

Utiliser la fonction Excel RIGHT()

La fonction RIGHT() extrait des caractères depuis la fin d’une chaîne. Sa syntaxe est :

=Right(text, [num_chars])

Où :

• text désigne la cellule contenant la chaîne d’origine.

• num_chars indique le nombre de caractères à extraire. Sa valeur par défaut est 1.

Exemple : j’utilise RIGHT() pour extraire les trois derniers caractères.

=RIGHT(A2, 3)

Récupérer les trois derniers caractères avec la fonction RIGHT(). Image par l’auteur.

Utiliser la fonction Excel MID()

La fonction MID() extrait des caractères au milieu d’une chaîne. Sa syntaxe est :

=MID(text, start_num, num_chars)

Où :

• text désigne la cellule contenant la chaîne d’origine.

• num_chars indique le nombre de caractères à extraire. Sa valeur par défaut est 1.

• start_num indique la position de départ dans la chaîne.

Dans cet exemple, j’utilise la formule MID() pour extraire une valeur au milieu de la chaîne à partir du quatrième caractère.

=MID(A2,4,3)

Récupérer trois caractères au milieu avec MID(). Image par l’auteur. 

Et voilà. =MID(A2,4,3) extrait trois caractères du texte en A2, en commençant au 4e caractère.

Utiliser la fonction Excel FIND()

La fonction FIND() repère une sous-chaîne dans un texte et renvoie la position du premier caractère de cette sous-chaîne. Sa syntaxe est :

FIND(find_text, within_text, [start_num]) 

Où :

• find_text est le texte à rechercher.

• within_text est le texte dans lequel effectuer la recherche.

• start_num précise la position de début de recherche, par défaut 1.

Prenons un petit jeu de données pour illustrer FIND(). Dans cet exemple, j’ai le nom DataCamp dans la colonne Text et je veux localiser la sous-chaîne Camp. Le résultat est 5 car Camp commence au cinquième caractère.

=FIND("Camp", A2)

Utiliser FIND() pour extraire la position du texte. Image par l’auteur.

Utiliser la fonction Excel SUBSTITUTE()

La fonction SUBSTITUTE() remplace une sous-chaîne spécifique par une autre. Sa syntaxe est :

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Où :

• text contient le texte à modifier.

• old_text est le texte à remplacer.

• new_text est le texte de remplacement.

• instance_num précise l’occurrence à modifier. Si omis, toutes les occurrences sont remplacées. 

Pour comprendre, prenons un jeu de données simple contenant des chaînes avec des délimiteurs. La colonne Text liste des noms et âges séparés par des points-virgules. Pour nettoyer, je saisis la formule suivante :

=SUBSTITUTE(A2, ";" , ",")

Ici :

• A2 désigne la cellule contenant le texte d’origine.

• ";" spécifie l’ancien texte à remplacer.

• "," est le nouveau texte.

Utiliser SUBSTITUTE() pour remplacer l’ancien texte. Image par l’auteur.

Et voilà. En appliquant la formule en B2, Excel remplace le point-virgule par une virgule.

Extraire du texte par délimiteur avec les fonctions de sous-chaîne d’Excel

Les délimiteurs sont des caractères qui séparent deux chaînes, par exemple un point-virgule ou une virgule. Dans Excel, vous pouvez utiliser les fonctions TEXTBEFORE() et TEXTAFTER() pour extraire du texte lorsqu’un délimiteur est présent.  

Utiliser la fonction Excel TEXTBEFORE() 

Comme son nom l’indique, TEXTBEFORE() extrait le texte situé avant un caractère ou une sous-chaîne donnée. Sa syntaxe est la suivante :

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Où :

• text renvoie au texte d’origine.

• delimiter est généralement une virgule ou un point-virgule.

• instance_num indique l’occurrence souhaitée du délimiteur. Par défaut 1.

• match_mode précise si la recherche du délimiteur est sensible à la casse (TRUE, par défaut) ou non (FALSE).

• match_end détermine si la fin du texte doit être traitée comme un délimiteur. Si TRUE, la fonction renvoie le texte d’origine si le délimiteur est introuvable.

• if_not_found indique une valeur personnalisée à renvoyer si le délimiteur est absent.

Voyons cela en pratique. J’ai une colonne Text contenant des informations sur des collaborateurs — nom, service et agence. Je souhaite extraire uniquement les noms et services.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Utiliser TEXTBEFORE() pour extraire des données. Image par l’auteur 

Voici le fonctionnement :

• A2 contient le texte source. 

• "," est le délimiteur qui sépare les éléments. 

• 2 indique la deuxième occurrence du délimiteur.

• 1 ignore la sensibilité à la casse, le cas échéant.

• 1 (à la fin) renvoie le texte d’origine si le délimiteur est absent.

Utiliser la fonction Excel TEXTAFTER()

La fonction TEXTAFTER() est similaire à TEXTBEFORE() — la différence réside dans le résultat. TEXTAFTER() extrait le texte qui suit un délimiteur. Sa syntaxe est la suivante :

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Où :

• text renvoie au texte d’origine.

• delimiter est une virgule ou un point-virgule. 

• instance_num indique l’occurrence souhaitée du délimiteur. Par défaut 1.

• match_mode précise si la recherche est sensible à la casse (TRUE, par défaut) ou non (FALSE).

• match_end détermine si la fin du texte doit être traitée comme un délimiteur. Si TRUE, la fonction renvoie le texte d’origine si le délimiteur est introuvable.

• if_not_found indique une valeur personnalisée à renvoyer si le délimiteur est absent.

Ici, j’ai une colonne Text avec les détails des collaborateurs — nom, service, et agence. Je souhaite extraire la localisation et l’identifiant.

=TEXTAFTER(A2,",",2,1,1)

Utiliser TEXTAFTER() pour extraire des données. Image par l’auteur 

Voici le fonctionnement :

• A2 contient le texte source. 

• "," est le délimiteur qui sépare les éléments. 

• 2 indique la deuxième occurrence du délimiteur. 

• 1 ignore la sensibilité à la casse, le cas échéant.

• 1 (à la fin) renvoie le texte d’origine si le délimiteur est absent.

Cas d’usage spécifiques des sous-chaînes dans Excel

Maintenant que vous maîtrisez les bases, passons à des méthodes avancées. Elles permettent des manipulations de texte plus complexes et rendent vos traitements encore plus efficaces. 

Gérer des longueurs variables de prénoms

J’ai un jeu de données contenant le nom complet, mais je veux extraire uniquement le prénom.

Un tableau contenant des noms complets. Image par l’auteur.

On pourrait croire que c’est simple — utiliser LEFT() pour récupérer le prénom. J’ai donc essayé la formule :

=LEFT(A2,4)

Utiliser LEFT() pour récupérer le prénom. Image par l’auteur.

Mais au lieu d’obtenir le prénom, j’ai obtenu les quatre premiers caractères, car j’ai fixé num_char à 4, en me basant sur « Jane ». En recopiant la formule, le résultat n’est pas correct pour les autres prénoms.

Il me faut une solution plus souple. Pour extraire le prénom quelle que soit sa longueur, combinez LEFT() avec FIND(). FIND() repère la position du premier espace qui sépare prénom et nom. Ensuite, LEFT() extrait les caractères jusqu’à cette position. 

=LEFT(A2,FIND(" ",A2)-1)

Combiner FIND() et LEFT() pour extraire le prénom. Image par l’auteur.

Décomposons la formule :

• La cellule A2 contient le nom complet.

• FIND(" ", A2, 1) trouve la position du premier espace dans A2 à partir du premier caractère.

• -1 permet d’exclure l’espace.

• LEFT(A2, ...) extrait le nombre de caractères indiqué depuis la gauche.

Extraire le domaine d’une adresse e-mail

Prenons un autre exemple pour voir si  LEFT() et FIND() conviennent à un autre cas. J’ai un jeu de d’adresses e-mail dont je dois extraire le nom de domaine.

Données contenant des adresses e-mail. Image par l’auteur.

Pour cela, je crée une colonne Email Domain, et je saisis la formule suivante dans une cellule, puis je la recopie vers les trois autres :

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Extraire le domaine avec RIGHT(), LEN() et FIND(). Image par l’auteur.

Et voilà — j’ai tous les domaines. Voici comment Excel obtient le résultat :

• FIND("@", A2) localise la position du symbole @ dans l’adresse.

• LEN(A2) calcule la longueur totale de l’adresse.

• LEN(A2) - FIND("@", A2) calcule le nombre de caractères après le @.

• Enfin, RIGHT(A2, LEN(A2) - FIND("@", A2)) extrait ce nombre de caractères depuis la droite de la chaîne.

Extraire des codes produits en combinant LEFT(), MID() et RIGHT()

Jusqu’ici, j’ai expliqué LEFT(), MID() et RIGHT() séparément. Mais en les combinant, vous pouvez faire bien plus. Elles permettent d’extraire différentes parties d’un texte selon des motifs précis.

Ici, j’ai une liste de codes produits et je veux en extraire une section spécifique. 

• De la première section, je veux un caractère.
• De la deuxième, je veux les quatre caractères.
• De la dernière, je veux les trois derniers caractères.

Une liste de codes produits. Image par l’auteur.

Pour y parvenir, je combine LEFT(), RIGHT() et MID() et saisis la formule suivante :  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Combiner MID(), LEFT() et RIGHT() pour extraire des données. Image par l’auteur.

Comme vous le voyez, la formule combinée a extrait les caractères souhaités dans chaque section des codes. Détails :

• LEFT(A2, 1) extrait le premier caractère.

• MID(A2, FIND("-", A2) + 1, 4) trouve le premier tiret, se décale d’un caractère à droite, puis extrait quatre caractères.

• "-" ajoute un tiret.

• RIGHT(A2, 3) extrait les trois derniers caractères.

• & concatène les éléments. 

Combiner SUBSTITUTE() avec MID() pour une extraction dynamique 

Voyons maintenant comment combiner MID() avec SUBSTITUTE() pour extraire des portions spécifiques d’une chaîne puis remplacer des caractères ou sous-chaînes dans la partie extraite.

Par exemple, j’ai des Order Details clients et je veux remplacer l’ID de commande par le texte Order confirmed.

Un tableau contenant des détails de commande. Image par l’auteur.

J’utilise la formule suivante :

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

SUBSTITUTE() et MID() pour remplacer du texte. Image par l’auteur.

C’est tout ! La formule extrait et remplace exactement ce que je voulais.

• MID(A2, 1, 19) extrait les 19 premiers caractères de la cellule A2.

• SUBSTITUTE(text, old_text, new_text) remplace chaque old_text par new_text.

Autre exemple : j’ai une colonne Product Detail et je veux extraire la taille du produit entre crochets.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Extraire la taille du produit entre crochets. Image par l’auteur.

Et c’est fait — tout a été extrait correctement.

• FIND("[", A2) trouve la position du crochet ouvrant.

• FIND("]", A2) trouve la position du crochet fermant.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) extrait le texte entre les deux crochets.

• SUBSTITUTE(...,"[", "") supprime le crochet ouvrant du texte extrait.

Nettoyer des saisies de données

Parfois, nos données sont désordonnées, mais ce n’est pas pour autant qu’il faut les utiliser en l’état. Ici, j’ai une liste de numéros de téléphone dans plusieurs formats, que je veux standardiser en nettoyant les incohérences.

Numéros non formatés. Image par l’auteur.

Pour standardiser, je crée une colonne appelée Clean data. Je saisis la formule suivante dans la deuxième cellule puis je la recopie là où je veux voir le résultat :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Ici :

• SUBSTITUTE(A2, "-", "") supprime les tirets.

• SUBSTITUTE(..., "(", "") supprime les parenthèses ouvrantes.

• SUBSTITUTE(..., ")", "") supprime les parenthèses fermantes.

• SUBSTITUTE(..., ".", "") supprime les points.

Cela transforme les multiples formats en une suite continue de chiffres, comme ci-dessous. Les données sont propres, bien formatées et prêtes à l’emploi. Vous pouvez les laisser ainsi si vous le souhaitez.

Nettoyer les données avec SUBSTITUTE(). Image par l’auteur.

Mettre en forme le texte pour des rapports

Si vous souhaitez aller plus loin après le nettoyage et appliquer un format adapté, vous pouvez aussi utiliser différentes fonctions de sous-chaîne.

Reprenons l’exemple précédent des numéros de téléphone nettoyés. Je veux maintenant modifier le format pour qu’ils ressemblent à de vrais numéros et non à une simple suite de chiffres. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Format standardisé des numéros à l’aide de plusieurs sous-chaînes. Image par l’auteur.

Et c’est fait. J’ai maintenant tous les numéros au format voulu. Voici le fonctionnement :

• LEFT(B2, 3) extrait les trois premiers chiffres.

• MID(B2, 4, 3) extrait les trois chiffres suivants à partir de la quatrième position.

• RIGHT(B2, 4) extrait les quatre derniers chiffres.

• TEXT(..., "000") et TEXT(..., "0000") mettent en forme chaque section pour garantir le bon nombre de chiffres, avec des zéros non significatifs si besoin.

• & "-" & assemble les sections mises en forme avec des tirets.

En conclusion

Vous savez désormais utiliser les fonctions de sous-chaîne d’Excel comme LEFT(), RIGHT(), MID(), TEXTBEFORE() et TEXTAFTER() pour manipuler des données textuelles. Qu’il s’agisse d’extraire des segments précis ou de nettoyer des données brouillonnes, ces fonctions facilitent et accélèrent votre travail.

Mais il y a toujours plus à apprendre avec Excel. Le cours Introduction to Excel est une excellente prochaine étape si vous débutez. Pour aller plus loin en analyse de données, essayez le cours Data Analysis in Excel, qui complète parfaitement notre Data Manipulation in Excel Cheat Sheet. De quoi bâtir des fondamentaux solides et faire passer vos compétences au niveau supérieur.