Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Admin Workspace 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube Sheets : comment récupérer les noms de domaine d’une liste d’e-mails ?
Vous vous êtes déjà sûrement demandé comment extraire le nom de domaine d’une colonne contenant des mails. En effet, je me suis posé la question cette semaine, suite à l’organisation d’un webinaire sur notre plateforme […]
Ce que vous allez découvrir
- Une colonne A dans Sheets contenant juste des mails
- La formule magique pour garder uniquement le nom de domaine d'une liste de mails
- Regex dans Sheets : une autre méthode pour parvenir à nos fins ?
- Un tableau croisé dynamique pour trier ensuite les noms de domaine
Sheets : comment récupérer les noms de domaine d’une liste d’e-mails ?
Vous vous êtes déjà sûrement demandé comment extraire le nom de domaine d’une colonne contenant des mails. En effet, je me suis posé la question cette semaine, suite à l’organisation d’un webinaire sur notre plateforme d’adoption aux outils Google Workspace : GSkills.
Une colonne A dans Sheets contenant juste des mails
Pour les besoins de cet article, j’ai demandé à l’Intelligence Artificielle Générative de me reconstituer une table de données avec des mails factifs avec une centaine d’entrées (en effet les données sont confidentielles). L’idée de l’exercice proposé dans ce tutoriel est de récupérer uniquement les noms de domaine voire le nom de la société sans l’extension .com, .org, etc… Les puristes diront que le nom de société n’est pas toujours compris dans le nom de domaine, certes !
La formule magique pour garder uniquement le nom de domaine d’une liste de mails
Il y a plusieurs possibilités pour s’en sortir, je vous propose ici de commencer par un combo avec droite, nbcar et trouve. Ces combinaisons de fonctions simples sont idéales pour débuter, mais la formule peut vite devenir longue et les erreurs aussi.
La formule à injecter en A2 serait alors :
=DROITE(A2;NBCAR(A2)-TROUVE("@";A2))Explication de la formule imbriquée :
TROUVE("@";A2):- Cette partie trouve la position du caractère « @ » dans l’email, dans la cellule A2 contenant le premier mail de la liste.
- Par exemple, si l’email est « john.doe@example.com« , cela renverra la position de « @ » dans l’email, disons la 9e position.
NBCAR(A2):- Mais où est donc nbcar ? 🤣 Cette fonction retourne le nombre total de caractères dans l’email. Par exemple, « john.doe@example.com » a 19 caractères.
- Mais où est donc nbcar ? 🤣 Cette fonction retourne le nombre total de caractères dans l’email. Par exemple, « john.doe@example.com » a 19 caractères.
NBCAR(A2)-TROUVE("@";A2):- Cela calcule combien de caractères se trouvent après le symbole « @ », en soustrayant la position de « @ » de la longueur totale de l’email.
- Dans l’exemple « john.doe@example.com« , cela donnerait 10 (19 – 9 = 10).
- =DROITE(A2;NBCAR(A2)-TROUVE(« @ »;A2))
- Cette partie extrait les caractères à droite du symbole « @ », jusqu’à la fin de l’email.
- Pour « john.doe@example.com« , cela renverra « example.com ».
Comment utiliser la formule d’extraction des noms de domaine ?
- Si tes emails sont dans la colonne A (par exemple, A2, A3, etc.), tu peux copier cette formule dans la cellule B2 (ou une autre cellule de la colonne B).
- Fais glisser la cellule vers le bas, si tu ne maîtrises pas encore ArrayFormula, pour appliquer la formule à toute ta colonne B.
Cela permettra d’extraire automatiquement le nom de domaine de chaque adresse email.
Maintenant si je souhaite retirer l’extension dans cet exemple .IO pour ne garder que potentiellement le nom de la société ?
Et c’est parti pour rajouter la surcouche qui va bien ! La formule pour obtenir le résultat escompté sera donc :
=GAUCHE(DROITE(A2;NBCAR(A2)-TROUVE(« @ »;A2));TROUVE(« . »;DROITE(A2;NBCAR(A2)-TROUVE(« @ »;A2)))-1)
Plaçons sur le billard cette formule et décomposons la ensemble ! Même pas mal !
Explication détaillée :
DROITE(A2;NBCAR(A2)-TROUVE("@";A2))- Comme dans la formule précédente, cette partie extrait tout ce qui se trouve à droite du « @ » dans l’adresse email. Par exemple, pour « john.doe@example.com« , cela donne « example.com ».
- Comme dans la formule précédente, cette partie extrait tout ce qui se trouve à droite du « @ » dans l’adresse email. Par exemple, pour « john.doe@example.com« , cela donne « example.com ».
TROUVE(".";DROITE(A2;NBCAR(A2)-TROUVE("@";A2)))- Cette fonction trouve la position du premier point (« . ») dans la partie extraite précédemment. Pour « example.com », cela renverra la position du point, qui est ici la 8e position.
- Cette fonction trouve la position du premier point (« . ») dans la partie extraite précédemment. Pour « example.com », cela renverra la position du point, qui est ici la 8e position.
GAUCHE(DROITE(A2;NBCAR(A2)-TROUVE("@";A2));TROUVE(".";DROITE(A2;NBCAR(A2)-TROUVE("@";A2)))-1)- Cette partie extrait les caractères avant le premier point (« . ») dans la chaîne. Donc pour « example.com », cela extrait « example ».
Colle cette formule dans la cellule B2 (ou une autre cellule) puis applique la formule à toute ta colonne B.
- Cette partie extrait les caractères avant le premier point (« . ») dans la chaîne. Donc pour « example.com », cela extrait « example ».
Cette formule extrait uniquement le nom de domaine, sans l’extension, pour chaque email dans la colonne A.
Ce qui donne :
Regex dans Sheets : une autre méthode pour parvenir à nos fins ?
Oui, comme indiqué en introduction et comme souvent dans Sheets, il y a plusieurs moyens d’arriver à ses fins avec les formules, ce qui fait toute la magie du tableur. Voyons voir du côté des expressions régulières de Sheets, les fameuses Regex ! Au passage, Quentin, développeur chez Numericoach a écrit un guide pratique composé d’exercices pour monter en puissance sur l’une des fonctions les plus avancées du tableur de Google. Je vous invite vraiment à le télécharger, vous gagnerez un temps de dingue dans votre quotidien !
La formule dans notre cas d’usage à utiliser est :
=REGEXEXTRACT(A1;"@([^.]+)")
Comme vous le voyez et comme souvent avec les REGEX, la formule est courte et chirurgicale, pas besoin d’imbriquer des tonnes de formules, ce qui facilite et limite les erreurs !
Décortication de cette expression régulière :
REGEXEXTRACT(A1;"@([^.]+)"):REGEXEXTRACTest une fonction qui extrait un texte d’une cellule en fonction d’un modèle de recherche (appelé expression régulière, ou regex).- Ici, A1 représente la cellule qui contient l’adresse email, comme par exemple
john.doe@example.com
- « @([^.]+) » :
- Cette partie est l’expression régulière qui définit ce que nous voulons extraire.
@: Le symbole@dans la regex indique où commence la recherche, c’est-à-dire juste après l’arobase dans l’adresse email.([^.]+): Ce groupe est ce qui va être extrait :[^.]: Signifie « tout caractère sauf un point » (.).+: Indique que nous voulons un ou plusieurs caractères avant le point.
- Cela va donc capturer tout ce qui est entre le symbole
@et le premier point suivant dans l’adresse email.
Si la cellule A1 contient l’email « john.doe@example.com« , la formule va :
- Trouver le symbole « @ ».
- Extraire tous les caractères après l’arobase et avant le premier point, c’est-à-dire « example ».
Si tes emails sont dans la colonne A (par exemple, en A1), mets cette formule dans B1. Tu peux ensuite faire glisser la formule vers le bas pour l’appliquer à toutes les lignes.
Un tableau croisé dynamique pour trier ensuite les noms de domaine
Une fois la formule tirée avec une fonction matricielle ArrayFormula, vous allez pouvoir faire un TCD de la colonne (ici D) pou calculer le nombre de domaines ou entreprises différentes
Voici le Tableau Croisé Dynamique (TCD pour les intimes) pertinent qui permet à partir d’une liste de mails de savoir le nombre de personnes d’un nom de domaine ou d’une société :
Cet article et donc ces formules m’ont permis de savoir rapidement que sur les 124 personnes inscrites à notre webinaire de ce mardi 3 septembre 2024, il y avait 85 entreprises différentes intéressées par notre plateforme d’adoption sur Google Workspace. La puissance de la feuille de calcul et de ses TCD m’ont permis ensuite d’analyser les données.
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
- Articles connexes
- Plus de l'auteur
Thierry VANOFFE, consultant, formateur, coach Google Workspace CEO de Numericoach, leader de la formation Google Workspace en France. Passionné par Google, ce blog me permet de partager cette passion et distiller tutos, trucs, astuces, guides sur les outils Google. N'hésitez pas à me solliciter pour vos projets de formation.
