Dans notre article Découvrez les fonctions REGEX dans Excel, nous avons vu comment REGEX.EXTRAIRE, REGEX.REMPLACER et REGEX.TEST permettent d’extraire, de nettoyer et de valider des données textuelles à l’aide d’un modèle.
Aujourd’hui, on pousse l’idée un peu plus loin : saviez-vous que ce même modèle REGEX peut désormais servir de critère de recherche directement dans RECHERCHEX et EQUIVX ?
Concrètement, plutôt que de chercher une valeur exacte ou d’utiliser un caractère générique (? ou *), vous pouvez demander à Excel de repérer la première donnée d’une liste qui respecte un modèle précis, puis d’aller récupérer l’information associée.
Dans cet article, on reprend les mêmes exemples que dans l’article précédent, mais cette fois dans l’autre sens : au lieu d’extraire une information d’une cellule, on s’en sert pour retrouver la bonne ligne.
Vous préférez la version vidéo ? La voici!
Pourquoi combiner REGEX avec RECHERCHEX et EQUIVX
Voici quelques situations où c’est particulièrement utile :
- Retrouver l’enregistrement dont le contenu respecte un format précis (un code produit, par exemple), sans connaître sa valeur exacte à l’avance
- Faire une recherche lorsque la même information est écrite de plusieurs façons différentes (Québec ou QC, par exemple)
- Repérer, dans une liste, la première (ou la seule) donnée qui respecte déjà — ou au contraire ne respecte pas — un modèle donné.
Le tout sans construire de colonne intermédiaire avec REGEX.TEST ou REGEX.EXTRAIRE : le modèle REGEX est fourni directement comme valeur cherchée.
La syntaxe : le mode_correspondance = 3
Les deux fonctions acceptent un modèle REGEX comme valeur_cherchée, à condition d’indiquer 3 dans le paramètre mode_correspondance :
=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_introuvable]; [mode_correspondance]; [mode_recherche])
=EQUIVX(valeur_cherchée; tableau_recherche; [mode_correspondance]; [mode_recherche])
La seule différence avec ce que vous connaissez déjà concernant REGEX : le modèle REGEX prend la place de la valeur_cherchée, et non un paramètre séparé comme dans REGEX.EXTRAIRE ou REGEX.TEST.
Regardons comment ceci se traduit en 3 exemples concrets.
Exemple 1 : Retrouver un enregistrement selon un format — les codes produits
Reprenons l’exemple des codes de produit de l’article précédent, où l’on validait avec REGEX.TEST qu’un code respecte le modèle « 2 lettres majuscules + 4 chiffres + 1 lettre majuscule ».
Ajoutons une colonne Description à cette liste :
Au lieu de valider chaque code, un par un, avec REGEX.TEST, on peut demander directement à Excel de retrouver la position — ou la description — du premier code qui respecte le modèle « 2 lettres majuscules + 4 chiffres + 1 lettre majuscule ».
Pour retourner la position on utilisera la formule suivante :
=EQUIVX(“[A-Z]{2}[0-9]{4}[A-Z]{1}“; B2:B5; 3)
Remarquez que le modèle : [A-Z]{2}[0-9]{4}[A-Z]{1}, se trouve dans le paramètre valeur_cherchée, et qu’il est indiqué 3 dans le paramètre mode_correspondance.
→ la formule retourne 1, la position de la valeur AA1234C, le premier code valide de la liste.
Pour retourner la description on utilisera la formule suivante :
=RECHERCHEX(“[A-Z]{2}[0-9]{4}[A-Z]{1}“; B2:B5; C2:C5;; 3)
Remarquez que le modèle : [A-Z]{2}[0-9]{4}[A-Z]{1}, se trouve dans la valeur_cherchée, et qu’il est indiqué 3 dans le paramètre mode_correspondance.
→ la formule retourne Chaise de bureau, la description associée à ce premier code valide.
Si vous souhaitez récupérer le dernier code valide plutôt que le premier. Il suffit d’ajouter -1 dans le paramètre mode_recherche pour parcourir la liste de la fin vers le début :
=EQUIVX(“[A-Z]{2}[0-9]{4}[A-Z]{1}”; B2:B5; 3; -1)
→ la formule retourne 4, la position de CD5321A.
Exemple 2 : Retrouver une adresse à partir d’un modèle de code postal
Reprenons maintenant la liste d’adresses de l’article précédent, saisies de façons inconsistantes, avec le code postal tantôt collé, tantôt séparé par un espace ou un tiret.
Supposons que vous cherchiez à retrouver l’adresse complète associée à un code postal qui commence par la lettre G (les codes postaux de l’Est du Québec, par exemple), sans connaître le reste du code postal. On reprend exactement le même modèle que celui construit avec REGEX.EXTRAIRE dans l’article précédent, en fixant la première lettre à G au lieu d’une lettre aléatoire entre a et z [A-Za-z] pour le 1er élément du 1er bloc.
=RECHERCHEX(“G[0-9][A-Za-z][ -]?[0-9][A-Za-z][0-9]“; B13:B21; B13:B21;; 3)
→ la formule retourne « 5678 Avenue des Érables, Québec, QC – Code postal : G1R-2K2 », la première adresse de la liste dont le code postal commence par G.
Et si vous voulez plutôt savoir à quelle ligne se trouve cette adresse, EQUIVX fait exactement la même recherche, mais retourne une position :
Exemple 3 : Repérer un format précis parmi des numéros de téléphone
Reprenons la liste de numéros de téléphone présentée dans l’article précédent. Comme vous pouvez le constater, les numéros ont été saisis selon quatre formats différents.
On peut se servir de cette méthode pour repérer lesquels respectent déjà — ou pas — un format donné. Par exemple, retrouver le premier numéro saisi avec des tirets :
=EQUIVX(“[0-9]{3}-[0-9]{3}-[0-9]{4}“; B2:B5; 3)
→ retourne 3, la position de 888-888-1235.
On peut aussi combiner plusieurs formats indésirables en insérant entre crochet et entres deux guillemets les caractères souhaités, qui indique une alternative entre deux modèles. Ici, on cherche un numéro qui contient soit une parenthèse, soit un tiret soit une barre oblique: “[\(-]”
=EQUIVX(“[\(-]“; B2:B5; 3)
→la formule retourne 2, la position de (888) 888 1235, la première entrée qui contient l’un ou l’autre de ces caractères.
Un truc à connaître : les ancres : ^ et $
Le comportement par défaut du mode_correspondance = 3 trouve une correspondance dès que le modèle apparaît quelque part dans le contenu de la cellule — pas nécessairement sur la totalité de ce celle-ci. C’est exactement ce qui permet à l’exemple des adresses de fonctionner : le code postal n’est qu’une partie de la cellule.
Vous pouvez ajouter des ancres ^ (début) et $ (fin) autour de votre modèle, pour exiger que toute la cellule corresponde au modèle, ou bien que la cellule débute par le modèle ou se termine par le modèle. Reprenons l’exemple des adresses :
Si l’on veut s’assurer que le dernier élément de l’adresse correspond au code postal, l’on peut ajouter un $ à la fin du modèle.
=RECHERCHEX(“G[0-9][A-Za-z][ -]?[0-9][A-Za-z][0-9] $“;B30:B38;B30:B38;;3)
Si l’on veut plutôt s’assurer que le modèle est au début du contenu, il faut alors ajouter un ^ au début du modèle.
=RECHERCHEX(“^G[0-9][A-Za-z][ -]?[0-9][A-Za-z][0-9]”;B30:B38;B30:B38;;3)
Et puis, pour indiquer que la cellule doit correspondre entièrement au modèle l’on ajoute un ^ au début et un $ à la fin.
=RECHERCHEX(“^G[0-9][A-Za-z][ -]?[0-9][A-Za-z][0-9] $“;B30:B38;B30:B38;;3)
Ce dernier exemple peut-être particulièrement utile lorsque vous voulez vérifier que la donnée cherchée correspond exactement, du début à la fin, au modèle — par exemple pour confirmer qu’une cellule ne contient rien d’autre qu’un numéro de téléphone à 10 chiffres.
→ dans notre cas, si l’on utilise les 2 derniers exemples, les formules retournent une erreur #N/A, puisqu’aucune cellule ne débute par le code postal et aucune ne contient uniquement un code postal : il y a toujours une rue et une ville avant.
Conclusion
Le mode_correspondance = 3 transforme RECHERCHEX et EQUIVX en véritables outils de recherche approximative et de validation de format, sans étape intermédiaire. Combiné avec ce que vous avez appris sur REGEX.EXTRAIRE, REGEX.REMPLACER et REGEX.TEST dans l’article précédent, vous avez maintenant de quoi extraire, nettoyer, valider et retrouver vos données textuelles, peu importe la façon dont elles ont été saisies.
N’hésitez pas à expérimenter avec vos propres modèles, et rappelez-vous : Copilot, Claude ou tout autre outil AI reste un excellent allié pour composer un modèle REGEX quand la syntaxe vous donne du fil à retordre.
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
Si vous manipulez régulièrement des données dans Excel, découvrez notre formation Excel – Traitement, manipulation et analyse de données. Vous y apprendrez une foule d’astuces et de techniques pour manipuler, transformer et analyser vos données plus efficacement.
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é.
