Sommaire
Sommaire
Pendant longtemps, extraire un numéro, nettoyer un code ou vérifier un format d’e-mail dans Excel revenait à empiler les fonctions de texte classiques (GAUCHE, STXT, TROUVE, SUBSTITUE…), voire à écrire une macro VBA. Trois fonctions basées sur les expressions régulières (regex) changent la donne. REGEXEXTRACT, REGEXREPLACE et REGEXTEST sont désormais disponibles dans Excel pour Microsoft 365.
Envie de voir les fonctions REGEX en action ? Suivez la masterclass Solpedinn en vidéo, avec des exercices pas à pas.
Qu’est-ce que c’est qu’une expression régulière ?
REGEX, c’est le diminutif de “regular expression”.
Quand on n’en a jamais entendu parler, ça peut faire un peu peur, mais tâchons d’expliquer cela de la manière la plus simple possible.
Une expression régulière, c’est une suite logique de caractères (un motif récurrent) que nous pouvons utiliser pour faire des recherches.
Par exemple, on sait qu’un adresse mail est toujours construire de la même manière : un premier mot, un arobase, un nom de domaine et un indicateur (par exemple : dupontetdupond@tintin.fr).
- On pourrait donc faire de ce motif une expression régulière qui nous permettrait de retrouver automatiquement tout ce qui ressemble à une adresse mail dans un texte plus long.
- De la même façon, on pourrait trouver des motifs réguliers pour les numéros de téléphone (10 chiffres démarrant par un “0”) pour les codes postaux (5 chiffres) ou encore des motifs plus complexes (tous les caractères spéciaux, des termes entre guillemets…).
La syntaxe des REGEX
Là où ça se complique et où les gens ont tendance à abandonner, c’est que l’informatique utilise une syntaxe assez particulière pour traduire les expressions régulières.
Voilà quelques exemples de syntaxe pour des expressions régulières fréquentes :
- Pour récupérer une adresse mail : [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}
- Pour récupérer un numéro de téléphone de 10 chiffres : (\+33|0)[1-9]([ .-]?\d{2}){4}
- Pour récupérer du texte entre parenthèses : \(([^)]+)\)
Et oui, on comprend mieux pourquoi les abandons sont nombreux à ce stade !
Vous pouvez aussi utiliser l’IA directement dans vos fichiers Excel pour générer le motif adapté à votre besoin.
Contentez-vous de décrire avec le plus de précision possible le motif récurrent à retrouver et vous devriez récupérer assez rapidement la syntaxe dont vous avez besoin.
REGEXEXTRACT : extraire l’information d’un texte
Elle permet d’aller extraire, dans une chaîne de caractères, tous les éléments qui correspondent au motif que vous avez identifié.
Voici la syntaxe de la fonction
=REGEXEXTRACT(text, pattern, [return mode], [case sensitivity])
Les deux premiers arguments sont obligatoires et les deux derniers sont facultatifs :
- text = le texte ou la cellule dans laquelle vous souhaitez rechercher et extraire l’expression régulière
- pattern = l’expression régulière dans sa syntaxe (récupérée via l’IA) et mise entre guillemets
- [return mode] = permet d’indiquer si vous voulez récupérer un seul motif ou toutes les correspondances
- [case sensitivity] = permet d’indiquer si la casse importe (majuscule ou minuscule)
Par exemple, si nous souhaitons extraire une adresse mail d’une chaîne de texte, voici la formule que nous pourrions utiliser.
Et nous obtiendrions alors le résultat ci-dessous.
Pour bien comprendre la logique, nous avons demandé à Excel d’extraire dans la cellule B7, le motif qui correspond à notre expression régulière (le format d’une adresse mail). En réponse, Excel nous renvoie l’adresse mail trouvée (ou une erreur si aucun motif ne correspond).
REGEXREPLACE : nettoyer, masquer et réorganiser vos textes
A l’inverse de REGEXEXTRACT qui permet simplement d’extraire le motif recherché d’un texte, REGEXREPLACE permet de le remplacer par une alternative de son choix dans le texte d’origine.
Par exemple, si vous voulez anonymiser un numéro de sécurité sociale en remplaçant les 8 derniers chiffres par des étoiles (*** *** **), vous pouvez désormais le faire grâce à la fonction REGEXREPLACE.
En la paramétrant correctement, elle identifiera le motif et le remplacera directement dans le texte d’origine.
Son fonctionnement est presque similaire à celui de REGEXEXTRACT, à l’exception d’un argument supplémentaire, celui qui nous permet d’indiquer le texte de remplacement souhaité.
La syntaxe de REGEXREPLACE est donc la suivante :
=REGEXREPLACE(text, pattern, replacement, [return mode], [case sensitivity])
Par exemple, si je souhaite supprimer les caractères spéciaux dans un texte, je peux utiliser la fonction suivante.
J’obtiens alors le résultat ci-dessous.
Pour assembler ou réordonner des textes sans regex, voyez aussi comment fusionner le texte de plusieurs cellules.
REGEXTEST : vérifier qu’un texte respecte le bon format
Dernière fonction de notre liste, la fonction REGEXTEST.
Elle permet simplement de tester si le motif recherché se trouve ou non dans une chaîne de caractères.
Par exemple, je peux vouloir vérifier une liste d’adresse mail pour savoir rapidement si j’ai des adresses mal formatées et les supprimer.
La fonction REGEXTEST me permet de les faire rapidement et me renvoie une réponse booléenne, VRAI ou FAUX.
Sa syntaxe est la suivante :
REGEXTEST(text, pattern, [case_sensitivity])
Elle pourrait facilement être contournée par l’utilisation de REGEXEXTRACT et de SIERREUR, donc je n’y consacre volontairement pas un paragraphe très important.
Un cas concret : contrôler le format des numéros reçus lors de la récupération des SIREN/SIRET avec Excel et une API.
RECHERCHEX et EQUIVX : la recherche par motif, la fonction regex que personne n’utilise
Excel ne propose pas que trois fonctions regex.
RECHERCHEX s’écrit =RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche]) et son cinquième argument accepte la valeur 3, qui active le mode regex.
Microsoft donne l’exemple d’une donnée désordonnée où les États-Unis sont écrits « USA » ou « United States » : on cherche « USA|United States » avec match_mode = 3.
=RECHERCHEX("USA|États-Unis";A2:A100;B2:B100;"Non trouvé";3)
EQUIVX est le nom français de XMATCH, et elle fonctionne de la même façon pour renvoyer une position.
