Comment utiliser les fonctions REGEX dans Excel

Les expressions régulières (ou REGEX) sont des modèles de recherche qui peuvent être utilisés pour vérifier si une chaîne de texte est conforme à un modèle donné et extraire ou remplacer des chaînes de texte qui correspondent à un modèle donné. Compte tenu de leur complexité, cet article propose des résumés simplifiés et des exemples de leur utilisation dans Excel.

Comment utiliser REGEXTEST

Cette fonction teste si une chaîne de texte correspond à un modèle donné, renvoyant VRAI ou FAUX en fonction de ce test. C'est un excellent moyen de tester que vos données suivent un certain modèle.

Syntaxe

REGEXTEST(a,b,c)

où

• un (obligatoire) est le texte, la valeur ou la référence de cellule contenant le texte que vous souhaitez tester,
• b (obligatoire) est le modèle utilisé pour effectuer le test, et
• c (facultatif) vaut soit 0 si vous souhaitez que le test soit sensible à la casse, soit 1 sinon.

Exemple d'utilisation de REGEXTEST

Cette feuille de calcul contient une liste de codes produits qui doivent suivre une structure stricte.

Un code valide contient :

• Une représentation en minuscules de la taille du produit (« xs » pour extra-small, « s » pour small, « m » pour medium, etc.),
• Un numéro à un ou deux chiffres représentant la matière du produit,
• Trois lettres majuscules qui représentent l'endroit où le produit est fabriqué, et
• Un tiret entre chacune des trois parties décrites ci-dessus.

Je veux tester que tous les codes produits correspondent à cette structure.

Donc, dans la cellule B2, je vais taper :

=REGEXTEST((@Code),"(xs|s|m|l|xl)-(0-9){1,2}-(A-Z){3}",0)

où

• (@Code) est une référence structurée à la colonne où se trouvent les codes que je souhaite tester,
• (xs|s|m|l|xl) est la première partie du code produit que je souhaite tester, avec les lignes verticales signifiant « ou »,
• (0-9){1,2} est la deuxième partie du code produit que je souhaite tester, (0-9) étant n'importe quel chiffre et {1,2} signifiant qu'il peut y avoir un ou deux chiffres,
• (AZ){3} est la troisième partie du code produit que je souhaite tester, (AZ) étant n'importe quelle lettre majuscule et {3} signifiant qu'il doit y en avoir exactement trois,
• Les trois parties du code que je souhaite tester sont séparées par un tiret, et
• 0 est le dernier argument de la formule qui indique à Excel que le test est sensible à la casse.

Lorsque j'appuie sur Entrée pour appliquer cette formule à toutes les lignes de la colonne B, le résultat révèle que seuls deux des codes sont valides (VRAI).

• m-2-ROYAUME-UNI n'est pas valide (indiqué par le résultat FALSE) car le code pays ne contient que deux lettres majuscules,
• xl-714-AUS n'est pas valide car le code matériau contient trois chiffres, et
• S-5-USA n'est pas valide car le code de taille est en majuscule.

Cette fonction renvoie des parties de texte dans une cellule selon un modèle spécifié. Par exemple, vous souhaiterez peut-être séparer les chiffres et le texte.

Syntaxe

REGEXEXTRACT(d,e,f,g)

où

• d (obligatoire) est le texte, la valeur ou la référence de cellule contenant le texte que vous souhaitez extraire,
• e (obligatoire) est le motif que vous souhaitez extraire,
• f (facultatif) vaut 0 si vous souhaitez extraire uniquement la première correspondance, 1 pour extraire toutes les correspondances applicables sous forme de tableau et 2 pour extraire les groupes de la première correspondance, et
• g (facultatif) vaut soit 0 si vous souhaitez que l'extraction soit sensible à la casse, soit 1 sinon.

Dans cet exemple, je souhaite extraire les prénoms, noms et numéros de téléphone des clients dans trois colonnes distinctes.

Concentrons-nous d'abord sur les noms. Dans la cellule B2, je tape :

=REGEXEXTRACT(A2,"(A-Z)(a-z)+",1)

où

• A2 est la cellule contenant les données que je souhaite extraire,
• (AZ)(az)+ indique à Excel que je souhaite extraire tous les mots commençant par une lettre majuscule suivie de lettres minuscules, le « + » indiquant que je souhaite renvoyer une ou plusieurs lettres minuscules dans chaque motif, et
• 1 indique que je souhaite que chaque exemple du modèle ci-dessus soit séparé en cellules individuelles sous forme de tableau (en d'autres termes, le premier nom dans la cellule B2 et le deuxième nom dans la cellule C2). Si j'omis cet argument, Excel renverrait la première correspondance (le prénom) uniquement dans la cellule B2.

Lorsque j'appuie sur Entrée, Excel effectue l'extraction avec succès et ajoute une légère ligne bleue autour de la cellule C2 pour me rappeler qu'il s'agit d'un tableau renversé.

Avec la cellule B2 sélectionnée, je peux maintenant utiliser la poignée de recopie dans le coin inférieur droit de la cellule pour dupliquer cette formule relative dans les lignes de détails restantes.

Maintenant, je dois utiliser une formule REGEXTRACT similaire pour extraire les numéros de téléphone des clients. Dans la cellule D2, je tape :

=REGEXEXTRACT(A2,"(0-9())+ (0-9-)+")

où

• A2 est la cellule contenant les données que je souhaite extraire,
• (0-9())+ extrait les chiffres de zéro à neuf qui se trouvent entre parenthèses arrondies, le « + » extrayant un ou plusieurs chiffres dans ce modèle, et
• (0-9-)+ extrait les chiffres restants de la chaîne, le deuxième « – » représentant le tiret qui sépare les deux parties du numéro de téléphone, et le « + » indiquant à Excel que je souhaite extraire un ou plusieurs chiffres si la chaîne les contient.

Puisqu'il n'y a qu'une seule instance de ce modèle dans chaque cellule de la colonne A, je n'ai pas besoin d'ajouter d'autres arguments. Encore une fois, une fois que j'ai vérifié que cette formule produit le résultat attendu, je peux utiliser la poignée de remplissage pour la dupliquer dans les cellules restantes de la colonne D.

Manipuler des données avec REGEXREPLACE

Cette fonction prend le texte dans une cellule et crée une nouvelle version de ces données dans une autre cellule. Même si la fonction s'appelle REGEXREPLACE, elle ne remplace pas réellement le texte original à son emplacement d'origine.

Syntaxe

REGEXREPLACE(h,i,j,k,l)

où

• h (obligatoire) est le texte, la valeur ou la référence de cellule contenant le texte que vous souhaitez remplacer,
• je (obligatoire) est le motif que vous souhaitez remplacer,
• j (obligatoire) est le remplacement que vous souhaitez créer,
• k (facultatif) est l'occurrence du modèle que vous souhaitez remplacer, et
• je (facultatif) vaut soit 0 si vous souhaitez que le remplacement soit sensible à la casse, soit 1 sinon.

Exemple d'utilisation de REGEXREPLACE

Ci-dessous, vous pouvez voir une liste de noms dans la colonne A. Mon objectif est de recréer ces noms dans la colonne B, mais en utilisant le format « Nom, Prénom », incluant la virgule séparant les noms.

Dans la cellule B2, je tape :

=REGEXREPLACE((@Client name),"((A-Z)(a-z)+) ((A-Z)(a-z)+)","$2, $1")

où

• (@Nom du client) fait référence à la colonne contenant les données que je souhaite affecter,
• (AZ)(az)+ être inclus deux fois dans la formule (et séparé par un espace) indique à Excel que je souhaite prendre les deux chaînes de texte contenant une lettre majuscule suivie d'une ou plusieurs lettres minuscules, et
• 2 $, 1 $ indique à Excel que je souhaite inverser l'ordre de ces deux chaînes de texte, séparées par une virgule et un espace. Si je n'incluais pas les symboles du dollar, Excel renverrait simplement « 2, 1 » comme résultat dans chaque cellule.

Je n'ai pas abordé les arguments k et je dans la formule ci-dessus car je souhaite qu'Excel remplace toutes les occurrences (la valeur par défaut pour l'argument k), et je souhaite que le remplacement soit sensible à la casse (valeur par défaut pour l'argument je).

Étant donné que j'utilise un tableau formaté, lorsque j'appuie sur Entrée, la formule s'appliquera aux cellules restantes de la colonne B.

Les expressions régulières ne sont pas uniquement destinées à être utilisées dans Excel. En fait, vous pouvez utiliser REGEX pour automatiser d'autres tâches sur votre ordinateur, comme corriger le texte PDF copié, renommer en masse les fichiers téléchargés, formater la devise, supprimer les balises HTML, etc.