Découvrez les fonctions REGEX dans Excel

Publié le 20 janvier 2025
par Kim Leblanc BAA
Découvrez les fonctions REGEX dans Excel

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
Je vous conseille d’utiliser l’intelligence artificielle pour composer votre modèle, par exemple avec Copilot, en lui donnant des exemples des données que vous avez et ce que vous désirez modifier ou extraire.

 

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

Découvrez les fonctions REGEX dans Excel

 

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.

Découvrez les fonctions REGEX dans Excel

 

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

Découvrez les fonctions REGEX dans Excel

 

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.

Découvrez les fonctions REGEX dans Excel

 

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.

Découvrez les fonctions REGEX dans Excel

 

Case_sensitivity

Découvrez les fonctions REGEX dans Excel

 

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.

Découvrez les fonctions REGEX dans Excel

 

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 :

Découvrez les fonctions REGEX dans Excel

 

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")

Découvrez les fonctions REGEX dans Excel

 

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 :

Découvrez les fonctions REGEX dans Excel

 

=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}")

Découvrez les fonctions REGEX dans Excel

 

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 :

Commentaires d'apprenants - Excel – Trucs de pro

CFO-Masque_Formations-en-ligne_FBLa 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é.

2 réflexions sur “Découvrez les fonctions REGEX dans Excel”

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut