Les fonctions REGEX (Microsoft 365) vous permettent d’effectuer des recherches de texte en fonction d’un modèle ou, si vous préférez, d’une séquence spécifique.
Quelques exemples où les fonctions REGEX peuvent vous être utiles
- Extraire le code postal canadien à partir d’une adresse complète
- Remplacer une partie des caractères des numéros de cartes de crédit par des étoiles (***)
- Vérifier si l’identifiant d’un produit respecte le bon modèle (AA1234C).
- Nettoyer des données : espace en trop dans vos données texte
- Formater des données : enlever les parenthèses, tiret ou espace d’une liste de numéros de téléphone
Pour y arriver, il faudra en premier lieu construire le modèle que l’on recherche dans les données.
Voici quelques symboles à utiliser pour composer ce modèle :
- [0-9] : n’importe quel chiffre
- [A-Z] : caractère de A à Z, en majuscule
- [A-Za-z] : caractère de a à z, majuscule ou minuscule
- . : n’importe quel caractère (incluant les espaces)
- \w : n’importe quel caractère alphanumérique
- a* : l’astérisque indique qu’il peut y avoir une ou plusieurs occurences du caractère spécifié (ici un « a ») et il pourrait y en avoir aucun.
- a+ : le plus indique qu’il doit y avoir au moins une occurence du caractère spécifié (ici un « a »)
- ? : élément facultatif
- {} : Permet d’identifier le nombre d’occurrence [0-9] {3} = groupe de 3 chiffres
Il existe 3 fonctions REGEX :
REGEX.EXTRAIRE (REGEXEXTRACT): permet d’extraire des données à partir d’un modèle
REGEX.REMPLACER (REGEXREPLACE) : permet de remplacer des données à partir d’un modèle
REGEX.TEST (REGEXTEST ): Permet de vérifier si des données existent en fonction d’un modèle.
Voyons comment utiliser ces 3 fonctions REGEX à l’aide d’exemples
1. REGEX.EXTRAIRE
La fonction REGEX.EXTRAIRE contient 4 paramètres.
REGEX.EXTRAIRE(text; pattern; [return_mode]; [case_sensitivity])
Text : le texte à partir duquel l’on veut extraire une information
Pattern : le modèle que l’information doit respecter
Return_mode : la correspondance que nous souhaitons retourner
Case_sensitivity : Est-ce que l’élément doit respecter la casse?
Pour démontrer la fonction REGEX.EXTRAIRE et ses paramètres, je vais utiliser une liste d’adresses qui ont été saisies de diverses façons. Je voudrais être en mesure d’extraire le code postal canadien pour chacune des adresses.
La liste d’adresses
Remarquez les codes postaux (ils sont en gras). L’endroit où se trouvent les codes postaux n’est pas toujours le même. De plus, parfois, il y a un espace entre les deux blocs de 3 caractères et parfois il n’y en a pas. Il y a même un code postal avec un trait d’union.
Le code postal, dans l’exemple, possède un modèle bien précis, c’est-à-dire :
- 1er bloc composé de : 1 lettre + 1 chiffre + 1 lettre
- 2e bloc composé de : 1 espace, 1 tiret ou de ni l’un ni l’autre
- 3e bloc composé de : 1 chiffre + 1 lettre + 1 chiffre
Voici comment construire la fonction REGEX.EXTRAIRE pour extraire l’information
L’on reprend chacun des blocs énumérés ci-dessus et l’on indique les symboles à utiliser pour la fonction REGEX (voir les symboles en gras):
- 1 lettre [A-Za-z] + 1 chiffre [0-9] + 1 lettre [A-Za-z]
- 1 espace, 1 tiret ou ni l’un ni l’autre [ -]? (Remarquez l’espace et le tiret entre crochets et puis le ? qui signifie que ce bloc est facultatif)
- Troisième bloc constitué de 1 chiffre [0-9] + 1 lettre [A-Za-z] + 1 chiffre [0-9]
Si l’on assemble le tout dans la fonction REGEX.EXTRAIRE, en séparant chacun des blocs par des parenthèses, ça donne ceci :
=REGEX.EXTRAIRE(B13;"([A-Za-z][0-9][A-Za-z])([ -]?)([0-9][A-Za-z][0-9])")
J’obtiens ainsi tous les codes postaux de mon exemple.
Dans ma fonction REGEX.EXTRAIRE ci-dessus, j’ai omis les 2 derniers paramètres et ainsi utilisé les paramètres par défaut.
Mais voici plus de détails sur ces 2 paramètres :
Return_mode
3 options possibles :
0 : extraire la première correspondance
1 : extraire toutes les correspondances
2 : capturer les groupes du premier match
Return_mode =1
Return_mode =1, permettra d’extraire plusieurs fois le modèle spécifié s’il se répète plusieurs fois dans les données sources.
Dans l’adresse suivante, il y a deux codes postaux. En ajoutant un 1 dans le paramètre Return_mode, le résultat est une matrice de deux éléments qui apparaissent dans deux cellules distinctes.
Return_mode = 2
Le Return_mode 2 retourne également une matrice, mais, cette fois, la fonction retournera chacun des blocs du 1er code postal sur 3 différentes cellules.
Le premier code postal de l’adresse ci-dessous est G5L 0R0.
Le 1er groupe de ce code postal G5L se retrouve dans la cellule de la colonne C, l’espace entre les 2 groupes dans la cellule de la colonne D et 0R0 dans la cellule de la colonne E.
Case_sensitivity
2 options possibles :
0 : Les données doivent respecter la casse (Majuscule / Minuscule)
1 : Les données n’ont pas besoin de respecter la casse
Dans mon premier exemple, j’ai laissé la valeur par défaut (0), puisque j’ai identifié directement dans mon modèle que je voulais soit des majuscules ou des minuscules pour les lettres du code postal [A-Za-z].
Mais il aurait été possible de mettre la valeur à 1 pour la casse et ne pas spécifier les majuscules dans mon équation :
=REGEX.EXTRAIRE(B13;"([a-z][0-9][a-z])([ -]?)([0-9][a-z][0-9])";;1)
2. REGEX.REMPLACER
La fonction REGEX.REMPLACER contient 5 paramètres.
REGEX.REMPLACER(texte; motif; remplacement; [occurence]; [case_sensitivity])
Texte : le texte à partir duquel l’on veut extraire une information
Motif : le modèle que l’information doit respecter
Remplacement : le modèle avec lequel je désire remplacer le motif trouvé
Occurrence : Indique quelle occurrence du motif il faut remplacer. Par défaut, toutes les occurrences sont remplacées.
Case_Sensitivity : Est-ce que l’élément doit respecter la casse
Pour démontrer REGEX.REMPLACER, je vais utiliser une liste de numéros de téléphone qui ont été saisis de différentes façons. Je voudrais, dans un premier temps, retirer les tirets et parenthèses des numéros de téléphone.
Comme je veux retirer les tirets et parenthèses, je vais mettre dans le paramètre du motif : [-()].
Puis, dans le paramètre remplacement, j’inscris deux guillemets sans rien mettre entre les deux. Il remplace donc les éléments par rien.
Voici la fonction complétée :
=REGEX.REMPLACER(B35; "[-()]"; "")
Ainsi, j’obtiens la liste suivante :
Par la suite, je voudrais séparer les différents blocs du numéro de téléphone par un espace.
Pour ce faire, je vais réutiliser la fonction REGEX.REMPLACER de la façon suivante :
Dans le paramètre motif, j’identifie les 3 blocs du numéro de téléphone:
3 chiffres [0-9]{3} + 3 chiffres [0-9]{3} + 4 chiffres [0-9]{4}
Le nombre entre les accolades représente le nombre d’occurrences du caractère recherché.
Lorsqu’on les met bout à bout en entourant chacun des blocs par des parenthèses, ça nous donne ceci :
([0-9]{3})([0-9]{3})([0-9]{4})
Ensuite, dans le paramètre Remplacement, l’on distingue chacun des blocs identifiés dans le motif à l’aide d’un signe de dollar $. Comme nous avons 3 blocs, et que l’on veut séparer chacun d’eux avec un espace, l’on inscrit l’information comme suit : “$1 $2 $3”
=REGEX.REMPLACER(C35;"([0-9]{3})([0-9]{3})([0-9]{4})"; "$1 $2 $3")
Ici, j’ai omis les 2 derniers paramètres qui sont facultatifs. Nous avons déjà parlé du paramètre de Case_Sensitivity précédemment et il s’utilise de la même façon. Je ne l’expliquerai donc pas ici.
Occurrence
Pour ce qui est du paramètre occurrence, il permet d’identifier quelle occurrence du modèle (motif) l’on souhaite remplacer.
Prenons l’exemple d’un numéro de téléphone : 888 888 1235 où l’on souhaite insérer un tiret entre le 2e et le 3e blocs.
Dans le paramètre motif, l’on inscrit un espace entre crochets : [ ]
Dans le paramètre remplacement, l’on inscrit un tiret : “-“
Puis, dans le paramètre occurrence, l’on indique 2 pour que le tiret remplace le 2e espace du numéro de téléphone.
Comme ceci :
=REGEX.REMPLACER(B41; "[ ]"; "-";2)
3. REGEX.TEST
La fonction REGEX.TEST contient 3 paramètres.
REGEX.TEST(text; pattern; [case_sensitivity])
Text : le texte à partir duquel l’on veut vérifier si une information existe
Pattern : le modèle que l’information doit respecter
Case_Sensitivity : Est-ce que l’élément doit respecter la casse?
La fonction REGEX.TEST retourne un VRAI si le modèle (pattern) est trouvé. Dans le cas contraire, il retourne un FAUX.
Cette fonction peut être utile pour effectuer de la validation sur les données.
Prenons cette fois-ci un exemple de code de produit qui doit se composer de la façon suivante : 2 lettres majuscules + 4 chiffres + 1 lettre majuscule
Le modèle sera donc le suivant : [A-Z]{2}[0-9]{4}[A-Z]{1}
Je peux donc valider la liste de code en utilisant la fonction REGEX.TEST de la façon suivante:
=REGEX.TEST(B52;"[A-Z]{2}[0-9]{4}[A-Z]{1}")
Conclusion sur les fonctions REGEX
Les fonctions REGEX offrent une puissance et une flexibilité incroyables pour manipuler et valider des données textuelles.
Que ce soit pour extraire des informations spécifiques, remplacer des segments de texte ou vérifier la conformité des données à un modèle particulier, ces fonctions peuvent grandement simplifier votre travail.
N’hésitez pas à expérimenter avec différents modèles et à utiliser des outils comme Copilot pour vous aider à composer vos expressions. Avec un peu de pratique, vous découvrirez rapidement comment utiliser ces fonctions.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Bonjour
C’est disponible dans quelle version (en France) ?
Cdt
Pascal
Les fonctions REGEX sont disponibles dans la version Microsoft 365. Pour ma part, j’utilise plus précisément la version 2412.