Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : extraire une adresse e-mail d’un texte avec les expressions régulières

Google Sheets : extraire une adresse e-mail d’un texte avec les expressions régulières

Vous devez vérifier si les valeurs d’une colonne de votre tableau sont des adresses e-mails correctement construites, ou bien extraire le code postal d’une adresse complète, ou bien remplacer dans des URLs de fichiers du […]

personnes ont consulté cet article

3 minutes

Rédigé par Antoine MARTIN - il y a 2 ans

Ce que vous allez découvrir

  • Quelles sont les manipulations de chaînes de caractères possibles avec Google Sheets ?
  • Comment rédiger une expression régulière dans une fonction REGEX de Google Sheets ? Cas d'usage : extraire une adresse e-mail d'un bloc de texte ?

Google Sheets : extraire une adresse e-mail d’un texte avec les expressions régulières

Vous devez vérifier si les valeurs d’une colonne de votre tableau sont des adresses e-mails correctement construites, ou bien extraire le code postal d’une adresse complète, ou bien remplacer dans des URLs de fichiers du Drive tous les « /edit et tous les caractères qui suivent » par un « /preview » pour communique une URL de prévisualisation et non d’édition ? (voir cet article pour en savoir plus sur les URL des fichier Google).

Ces cas d’usage mettent en avant le besoin de manipuler des textes, appelés également « chaînes de caractère ».

Tous les langages informatiques sont capables de manipuler les textes avec les fameuses « expressions régulières » ou « RegEx ».

Sticker Sticker bulle BD injures | Bulle bd, Tutoriel bande dessiné,  Stickers muraux

Cet article a pour objectif de montrer comment utiliser ces expressions dans une feuille de calcul Google Sheets, grâce aux fonctions REGEXMATCH(), REGEXEXTRACT() et REGEXREPLACE(). Nous verrons à travers un exemple concret comment construire ces expressions régulières, qui sont très puissantes et peuvent atteindre des formulations très complexes, composées de caractères bizarres et qui ressemblent parfois à des injures de BD !

Quelles sont les manipulations de chaînes de caractères possibles avec Google Sheets ?

Google Sheets permet d’agir de trois façon sur du texte :

  • Vérifier avec REGEXMATCH(« texte » ; « expression régulière ») : la fonction renvoie une valeur booléenne (VRAI ou FAUX) si le texte contient une chaîne de caractères correspondant à l’expression régulière.
  • Extraire avec REGEXEXTRACT(« texte » ; « expression régulière ») : la fonction recherche et extrait la première partie de la chaîne du texte qui correspond à l’expression régulière.
  • Remplacer avec REGEXREPLACE(« texte » ; « expression régulière » ; « texte à mettre à la place ») : la fonction recherche et remplace la première partie de la chaîne du texte qui correspond à l’expression régulière par le texte à remplacer.

Comment rédiger une expression régulière dans une fonction REGEX de Google Sheets ? Cas d’usage : extraire une adresse e-mail d’un bloc de texte ?

J’ai dû intervenir pour un client sur un fichier qui contenait des extraits de messages avec des adresses e-mail.

Vous devez extraire l’adresse mail de chaque message !

Dans un premier temps il faut bien identifier la syntaxe d’une adresse e-mail :

nom-utilisateur_123.456@domaine.ext

Une adresse e-mail est composée de caractères alphanumériques, sans accents, avec éventuellement un ou plusieurs « . », ou « – » ou « _ », suivis d’un « @ » puis de deux chaines sans caractères spéciaux, séparées par un seul « . » .

À présent, recherchons les caractères en construisant pas à pas l’expression régulière correspondante.

Est-ce qu’il y a au moins un « @ » ?

Pour rechercher la présence d’au moins un caractère, il suffit de l’écrire entre les «  » :

=REGEXEXTRACT(A3;"@")

Sheets affiche « @ » dès qu’il rencontre un « @ » dans le texte.

C’est un début, mais loin d’être suffisant pour trouver une adresse e-mail complète.

Est-ce qu’il y a du texte devant le « @ » ?

Le « @ » doit être précédé de chiffres ou lettres sans accents. L’expression régulière « \w » permet de rechercher n’importe quelle lettre de l’alphabet (minuscule et majuscule) et n’importe quel chiffre. Pour que la recherche valide le fait qu’il en faut au moins un, il faut ajouter « + » . Cela donne donc « \w+ » à placer devant le « @ » .

=REGEXEXTRACT(A3;"\w+@")

La recherche s’affine, Sheets extrait une partie du nom de l’utilisateur, tant qu’il n’y a pas de caractère spécial.

Est-ce qu’il y a du texte alphanumérique, ou « – » ou « . » avant le « @ » ?

Pour rechercher plusieurs éléments de façon optionnelle, il suffit de les placer dans des []. Ainsi [ab] recherchera si « a » ou « b » est présent.

Donc ici nous devons chercher si :

  • « \w » est présent au moins une fois ;
  • « – » est peut-être présent une ou plusieurs fois ;
  • « . » est peut-être présent une ou plusieurs fois.

Je crée donc un groupe entouré de crochets, contenant « \w+« , «  » et attention, le « . » doit être précédé d’un antislash car il s’agit d’un caractère utilisé pour désigner n’importe quel caractère ! Donc « \. » .

Tout cumulé cela donne : [\w+-\.]

=REGEXEXTRACT(A3;"^[\w-\.]+@")

Est-ce qu’après le « @ » il y a des lettres pour le domaine, séparées par un « . » puis encore un groupe de cinq lettres maximum pour l’extension ?

Le nom de domaine est composé de caractères alphanumériques et éventuellement de « – » : donc [\w-]+

Ensuite il faut un et un seul « . » avec l’antislash devant toujours car nous cherchons le caractère « . » : \.

Enfin, l’extension est composée d’un groupe de 1 à 5 caractères. Utilisons encore une fois \w auquel on ajoute un « quantifieur » entre {}.

=REGEXEXTRACT(A3;"^[\w-\.]+@[\w-]+\.\w{0,5}$")

Nous avons donc pu extraire une adresse e-mail valide présente dans un texte complexe. En espérant que cet article vous a permis de construire pas à pas une expression régulière qui réponde à votre besoin ! La maîtrise de cet outil demande beaucoup de tests, et une capacité à lire une suite de caractères qui ont des significations différentes selon le contexte d’utilisation, pas toujours simple à déchiffrer. Je vous conseille d’utiliser https://regex101.com/ qui permet de tester et visualiser en direct la validité de votre expression régulière.

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour
guest
6 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
THIERRY VANOFFE

Il est trop fort cet Antoine !!! Bravo et merci

Cet avis vous a été utile ?

FABRE
FABRE

Bonjour, comment fais t’on pour récupérer l’email d’une case où il y a la fiche d’un contact (fiche contact : dans la case on tape @+nom et on clique sur le contact présent dans l’application CONTACT) ?

Cet avis vous a été utile ?

S DER
S DER

Bonjour
J’ai fait un copié collé de la formule mais elle ne marchait (pour moi)
=REGEXEXTRACT(A3; »^[\w-\.]+[\w-]+\.\w{0,5}$ »)
En tâtonnant, je suis arrivé à cette version (mais sans tout le TRAVAIL en AMONT je n’aurais pas pu le faire)
=REGEXEXTRACT(A3; »[\w-\.]+[\w-]+[\w]\.[\w-]+[\w] »)
Encore Merci à Antoine MARTIN

Cet avis vous a été utile ?

Ozr
Ozr
Répondre à  S DER
9 mois il y a

Merci cette formule m’a énormément aidé !
Au début j’avais mis celle là =REGEXEXTRACT(A3; »^[\w-\.]+[\w-]+\.\w{0,5}$ »)
Mais il y’avais de nombreuses adresses mails qui ne ressortais pas. Puis grâce à t’as formule j’ai pu récupérer toutes les adresses mails.

Cet avis vous a été utile ?