Comment vérifier qu’une adresse mail est valide sur Excel ?

Dans ce tutoriel, nous allons mettre en place une formule Excel qui va nous permettre de contrôler qu’une adresse mail saisie dans une cellule Excel est bien valide. Il s’agit de la partie d’une série de trois tutoriels dans lesquels nous aurons l’occasion de revenir sur la gestion des adresses mails dans Excel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Partie 1 : Contrôler qu'une adresse mail est valide

Partie 2 : Contrôler qu'une adresse mail est valide en temps réel (à la saisie)

Partie 3 : Créer facilement une fonction personnalisée pour vérifier qu'une adresse mail est valide

1. Introduction

Comme nous venons de le voir dans l’introduction de ce tutoriel, nous allons maintenant découvrir comment faire en sorte de pouvoir tester si une adresse mail saisie dans une cellule Excel est effectivement valide.

Pour cela nous allons tester cinq paramètres qui vont répondre aux règles suivantes :

• 1 : Tout d’abord nous allons évidemment vouloir regarder s’il existe une arobase dans le texte saisi dans la cellule,
• 2 : Ensuite nous allons regarder s’il y a un point après cette arobase,
• 3 et 4 : Puis nous contrôlerons le nombre de caractères : il doit y en avoir au moins un avant l’arobase, et au moins quatre après celle-ci,
• 5 : Et enfin nous contrôlerons l’absence d’espace dans l’adresse mail.

2. Vérifier la présence de l’arobase

Le premier élément que nous allons vouloir contrôler est la présence d’une (et une seule) arobase dans l’adresse mail.

Pour cela nous allons utiliser une méthode que nous avions déjà vue dans un tutoriel précédent, lequel nous permettait de compter le nombre de mots contenus dans une cellule à partir du nombre d’espace.

Pour cela nous allons utiliser deux fonctions :

• Tout d’abord la fonction NBCAR() qui permet de dénombrer le nombre de caractères contenues dans une chaîne,
• Et ensuite la fonction SUBSTITUE() qui permet quant à elle de modifier un ou plusieurs caractères contenus dans une chaîne de caractère par un autre ou plusieurs autres caractères

Cette formule nous permettra donc de vérifier que la différence entre le nombre de caractères total et le nombre de caractères contenus dans la chaîne modifiée pour faire disparaître les arobases est bien égale à 1.

Cette formule est donc la suivante :

=NBCAR(B8)-NBCAR(SUBSTITUE(B8;"@";"")) 

Ensuite nous allons vérifier que cette formule nous retourne bien une valeur égale à « 1 » qui correspondrait donc à une seule arobase dans la cellule :

=(NBCAR(B8)-NBCAR(SUBSTITUE(B8;"@";"")))=1 

Ici cette formule va nous permet de récupérer une valeur Booléenne laquelle sera égale a VRAI lorsque effectivement il y a une seule arobase de saisie et elle sera égal à FAUX dans tous les cas contraire c’est-à-dire que :

• Soit il n’y a aucune arobase,
• Soit qu’il y en a au moins deux

Ici nous pourrions penser qu’il suffirait d’utiliser la fonction TROUVE() afin de détecter s’il y a effectivement une arobase dans la chaîne de caractères.

Mais cette méthode ne permettrait que de savoir s’il y en a au moins une.

La fonction TROUVE() nous renverrait en effet VRAI même s’il y en avait plusieurs, ce qui n’est évidemment pas permis dans une adresse mail.

3. Vérifier la présence d’un point dans le nom de domaine

Dans une adresse mail, l’arobase permet de séparer les deux parties qui la composent :

• La première partie (qui se place avant l’arobase) correspond au nom de l’utilisateur,
• La deuxième partie (qui se situe donc après l’arobase) correspond au nom de domaine

Et c’est sur cette deuxième partie que nous allons maintenant nous intéresser, en sachant qu’un nom de domaine doit forcément contenir au moins un point (les plus célèbres chez nous étant les noms de domaine en .com, .fr, .be ou encore .eu).

Mais il faut savoir qu’il existe également des noms de domaine contenant plusieurs points comme @impos.gouv.fr ou @amazon.co.jp.

Pour savoir s’il y a effectivement au moins un point dans le nom de domaine nous allons tout d’abord devoir extraire la partie située sur la droite de l’arobase.

Pour cela nous allons bien entendu utiliser la fonction DROITE() qui permet d’extraire la partie située sur la droite d’une chaîne de caractères, c’est-à-dire la partie située à la fin.

Pour déterminer le nombre de caractères que nous allons vouloir extraire, nous allons utiliser la fonction TROUVE() que nous avions abordé rapidement un peu plus tôt, et qui permet de retrouver la position d’un caractère donné au sein d’une chaîne.

Il ne restera ensuite plus qu’à soustraire ce résultat au nombre de caractères contenu dans toutes la chaîne, en utilisant une fois de plus la fonction NBCAR() :

=DROITE(B8;NBCAR(B8)-TROUVE("@";B8))

Maintenant, pour savoir si un point est bien saisi à l’intérieur de cette chaîne, il ne reste plus qu’à l’encapsuler dans la fonction TROUVE() :

=TROUVE(".";DROITE(B8;NBCAR(B8)-TROUVE("@";B8))) 

Il faut savoir que la fonction TROUVE() va effectivement nous retourner la position à laquelle se trouve le caractère recherché dans la base de données, lorsque celui-ci s’y trouve effectivement.

Dans le cas contraire, elle retournera une erreur.

Ici, notre objectif est d’obtenir une valeur booléenne, qui sera égale à VRAI lorsqu’un point sera identifié, et FAUX dans le cas contraire.

Il ne reste donc plus qu’à vérifier que le résultat est bien une valeur numérique, peu importe sa valeur.

Pour cela, nous pouvons utiliser la fonction ESTNUM() :

 =ESTNUM(TROUVE(".";DROITE(B8;NBCAR(B8)-TROUVE("@";B8))))

4. Vérifier le nombre de caractères avant et après l’arobase

Maintenant, et comme nous l’avons vu tout à l’heure, nous allons vouloir regarder le nombre de caractères situés avant l’arobase.

La première chose à savoir, c’est que l’arobase ne peut pas être le premier caractère de l’adresse mail, car comme nous l’avons vu tout à l’heure la partie située sur la gauche correspond au nom de l’utilisateur ou à n’importe quelle autre chaîne de caractère mais qui doit obligatoirement posséder au moins un caractère.

Il sera donc très simple de vérifier ce paramètre, il suffira en effet d’extraire ce caractère avec la fonction GAUCHE(), puis de regarder si celui-ci est différent de l’arobase :

=GAUCHE(B8)<>"@"

Ici, nous n’avons même pas besoin de spécifier le nombre de caractères à extraire dans la fonction GAUCHE(), cet argument ayant pour valeur par défaut un seul caractère.

Une fois encore cette fonction nous renverra une valeur booléenne qui sera égale à VRAI lorsqu’effectivement il y aura au moins un caractère situé entre le début de l’adresse mail et l’arobase et sera égale à FAUX dans le cas contraire.

5. Vérifier le nombre de caractères situés après l’arobase.

Maintenant nous allons nous intéresser au nom de domaine, c’est-à-dire à la partie située après l’arobase.

Ce nom de domaine devant contenir au moins quatre caractères qui correspondent :

• Au nom de domaine en lui-même, lequel est composé d’un ou plusieurs caractères,
• Du point de séparation avec l’extension (un caractère)
• Puis de l’extension en elle-même, qui contient au moins deux caractères (par exemple .fr ou .eu)

Pour obtenir ce nom de domaine, nous allons tout simplement en capsuler ce dernier, que nous avions déjà isolé un peu plus tôt en tant que paramètres de la fonction NBCAR() :

=NBCAR(DROITE(B8;NBCAR(B8)-TROUVE("@";B8))) 

Attention, dans le cas où l’adresse mail serait exempt d’arobase, la fonction TROUVE() renverrait une erreur comme nous l’avons vu un peu plus tôt.

Pour éviter que cette erreur ne remonte, nous allons encapsuler ce résultat dans une fonction SIERREUR() qui permet de renvoyer une valeur alternative lorsqu’un résultat est justement une erreur.

Ici, nous allons renvoyer la valeur zéro :

 =SIERREUR(NBCAR(DROITE(B8;NBCAR(B8)-TROUVE("@";B8)));0)

>

Pour finir, nous allons regarder si la valeur renvoyée, est strictement supérieur à trois :

 =SIERREUR(NBCAR(DROITE(B8;NBCAR(B8)-TROUVE("@";B8)));0)>3

6. Vérifier l’absence d’espace

Pour finir le dernier test que nous allons effectuer ici va consister à regarder si aucune espace n’a été saisie dans l’adresse mail.

Pour cela nous allons encore une fois utiliser la fonction TROUVE() afin de déterminer la position de l’espace au sein de la chaîne de caractères :

=TROUVE(" ";B8)

S’il n’y a aucune espace, alors cette fonction nous enverra une erreur et nous pourrons intercepter cette erreur grâce à la fonction ESTERREUR() qui nous retournera VRAI lorsqu’effectivement la fonction TROUVE() génère une erreur (c’est-à-dire qu’il n’y a aucune espace dans l’adresse mail) et nous renverra FAUX en présence d’une espace :

=ESTERREUR(TROUVE(" ";B8))

7. Créer une fonction unique

Maintenant que nous avons un tableau qui nous permet de valider les cinq critères de détermination d’une adresse mail, nous allons pouvoir en capsuler toutes ces formules dans une seule et même cellule en utilisant la fonction logique ET().

Celle-ci permet d’effectuer plusieurs tests afin de renvoyer une valeur booléenne égale à VRAI lorsque tous ces tests sont VRAI.

=ET((NBCAR(B8)-NBCAR(SUBSTITUE(B8;"@";"")))=1;ESTNUM(TROUVE(".";DROITE(B8;NBCAR(B8)-TROUVE("@";B8))));GAUCHE(B8)<>"@";SIERREUR(NBCAR(DROITE(B8;NBCAR(B8)-TROUVE("@";B8)));0)>3;ESTERREUR(TROUVE("  ";B8)))

De cette manière il suffira qu’un seul test échoue et donc renvoie FAUX pour que cette fonction renvoie FAUX également.

8. Comment vérifier une adresse mail, directement lors de sa saisie dans une cellule Excel ? (sans VBA)

Maintenant, nous allons pouvoir exploiter la formule que nous avions mise en place dans la partie précédente pour nous assurer que l’utilisateur saisisse une adresse mail valide, en fonction des règles que nous venons d’édicter.

Pour rappel, ces règles sont au nombre de cinq :

• 1 : Il y a une et une seule arobase dans l’adresse,
• 2 : Il y a au moins un point APRÈS celle-ci,
• 3 : Il y a au moins un caractère avant l’arobase,
• 4 : Il y a au moins quatre caractères après l’arobase,
• 5 : Il n’y a pas d’espace dans toute l’adresse mail.

La première chose à faire pour contrôler que l’utilisateur saisisse une adresse mail répondant à ces critères est de cliquer sur la cellule dans laquelle nous se trouve la formule complète, afin de la récupérer en la sélectionnant puis en la copiant en appuyant sur les touches [Ctrl]+[C] :

Ensuite, nous sélectionnons toutes les cellules dans lesquelles l’utilisateur sera amené à venir saisir une adresse mail à contrôler, et nous allons y insérer une règle de validation des données.

Il s’agit d’une fonctionnalité intégrée dans Excel qui permet d’analyser les données saisies par un utilisateur afin de s’assurer que celles-ci correspondent effectivement avec des règles définies par la personne qui a créé le classeur Excel, ce qui évite un grand nombre d’erreurs et de plantage de formules.

Nous pouvons être amené à utiliser les règles de validation des données dans de nombreux cas :

• Contrôler qu’un utilisateur a bien saisi une donnée numérique, une date, un nombre entier, …),
• Nous avons également vu dans des tutoriels précédents que nous pouvons détourner la validation des données pour afficher un menu déroulant dans une cellule pour sélectionner une donnée parmi un ensemble de données pré-enregistrées, ce qui empêche le risque d’erreur de saisie.
• …

Lorsque les règles de la validation des données proposées par Excel ne sont pas suffisantes, il est possible d’aller beaucoup plus loin en utilisant une formule pour la validation des données, et c’est justement ce qui va nous intéresser ici.

Cette formule qui va nous permettre de valider une saisie doit évidemment renvoyer un résultat booléen, c’est-à-dire VRAI ou FAUX, et c’est ce résultat qui permettra à Excel d’autoriser ou non la saisie

Pour cela, une fois les cellules concernées ont bien été sélectionnées, nous allons nous rendre dans le menu Données afin de cliquer sur validation de données :

Nous choisissons de mettre en place une règle de validation Personnalisée et à ce moment et Excel nous propose un champ de saisir dans lequel nous allons pouvoir entrer une formule :

Nous y collons la formule que nous avions précédemment copiée en appuyant sur les touches [Ctrl]+[V] :

À ce moment-là, il est important que la cellule appelée dans cette formule soit la formule active (pour laquelle nous retrouvons les coordonnées dans la zone des noms située en haut à gauche) :

Normalement, si vous avez bien suivi les étapes de ce tutoriel, cela devrait être le cas, mais dans le cas contraire, il faudra bien modifier les références utilisées dans la formule pour que celles-ci correspondent à la cellule active.

Cela étant fait, nous pouvons valider la mise en place de la validation des données en appuyant sur le bouton [OK].

De cette manière si nous saisissons à la suite une adresse mail correcte, Excel va effectuer le test que nous venons de mettre en place et va autoriser la saisie de celle-ci :

En revanche, si nous saisissons une adresse erronée (par exemple en saisissant deux arobases), la validation des données va la rejeter, et Excel va nous inviter à modifier notre saisie :

Si le message affiché par Excel n’est pas suffisant explicite, il est possible de le personnaliser.

Pour cela revenons dans la fenêtre de validation des données, puis dans l’onglet Alerte d’erreur. Nous pouvons saisir le texte « Veuillez saisir une adresse mail valide » :

Si nous le souhaitons, il est également possible d’afficher un message lorsque l’utilisateur sélectionne la cellule pour lui expliquer ce l’on attend de lui avant qu’il entre sa saisie (onglet Message de saisie) « Adresse mail de l’utilisateur » :

Pour finir sur la notion de validation de données, sachez qu’il est possible de permettre de valider des données, même lorsque celles-ci ne sont pas conformes aux règles définies.

Pour cela, revenons sur la fenêtre de validation des données afin de décocher l’option « Quand des données non valides sont tapées » :

À ce moment-là, la saisie sera acceptée, quel que soit le résultat du test, la validation ne servant alors qu’à afficher l’infobulle pour préciser ce qu’il est attendu.

9. La fonction VERIFMAIL() pour vérifier qu’une adresse mail est valide sur Excel

Maintenant notre formule fonctionne correctement et nous pouvons l’utiliser.

En revanche, comme vous pouvez le constater celle-ci est assez complexe à mettre en place, et surtout relativement longue à rédiger.

Le problème sera alors que si nous devons effectuer des tests similaires dans un autre classeur, il sera nécessaire de la rédiger à nouveau.

Pour la simplifier l’utilisation de ces tests, nous pouvons créer une fonction personnalisée qui sera enregistré directement dans le classeur, ou encore mieux, dans le classeur des macro personnelles, ce qui permettra de la réutiliser simplement dans tous les classeurs.

Ici, pour gagner du temps, nous allons laisser à Excel le soin de créer les bases de la fonction personnalisée pour nous.

En effet, pour créer une fonction personnalisée de manière automatique nous pouvons utiliser l’enregistreur de macro de VBA.

Il s’agit d’un outil très puissant qui va enregistrer toutes les actions que nous allons effectuer au sein d’un classeur Excel afin de les enregistrer et de les traduire dans le langage VBA.

Nous nous rendons tout en bas d’écran et nous cliquons sur le bouton « Enregistrer une macro » :

Excel nous demande alors le nom que nous voulons donner à la macro, nous pouvons par exemple l’appeler « VERIFMAIL » :

Ensuite, nous pouvons choisir si nous souhaitons enregistrer la macro dans le classeur actif, ou dans le classeur des macros personnelles.

Il faut alors savoir que :

• Si nous enregistrons la macro dans le classeur actif, celle-ci ne sera disponible que dans celui-ci, mais nous l’exploiter pleinement sur un autre poste (tant que celle-ci n’est appelée que dans le classeur actif)
• En revanche, si nous l’enregistrons dans le classeur des macros personnelles, alors celle-ci sera utilisables sur tous les fichiers Excel ouverts sur le poste, mais nous ne pourrons pas l’exploiter si nous ouvrons le classeur sur un autre poste

Lorsque nous validons l’enregistrement en appuyant sur le bouton [OK], Excel va enregistrer toutes nos actions pour en faire une macro VBA.

Cela va nous permettre de contourner facilement le principal problème auquel nous pourrions être confronté ici, à savoir que les formules Excel et les fonctions VBA ne parle pas la même langue.

En effet, pour rendre beaucoup plus accessible l’utilisation des formules, Excel traduit celles-ci dans la plupart des langues.

La plupart d’entre nous aurons donc une version d’Excel avec des fonctions en français.

D’un autre côté, VBA étant un langage de programmation pur, il n’est pas concevable de le traduire lui aussi, il reste donc utilisable uniquement dans sa langue d’origine : l’anglais.

Cela concerne bien entendu les noms de fonctions, mais également :

• Le caractère de séparation des décimales : pour un Excel installé en français, ce sera la virgule alors que pour VBA ce sera le point
• La virgule quant à elle sert de séparateur des éléments d’une fonction en VBA (là où Excel utilise le point-virgule)

L’astuce que nous allons utiliser ici va donc nous permettre de ne pas avoir à effectuer toutes ces conversions à la main.

Pour cela, nous allons tout simplement avoir à rentrer dans une formule en cliquant sur la première cellule, puis en appuyant sur la touche [F2], et enfin en validant en appuyant sur la touche [Entrée] :

Nous allons ensuite répéter cette opération pour toutes les formules de test.

Cela étant fait, nous pouvons arrêter l’enregistreur de macro en appuyant sur le petit carré qui se trouve en bas à gauche, et nous allons maintenant pouvoir modifier la fonction :

Pour cela nous allons lancer VBE qui est l’outil de développement des macros commandes VBA, en appuyant sur les touches [Alt]+[F11] :

Nous pourrons alors retrouver notre macro VERIFMAIL à l’intérieur du Module1 :

Pour commencer, nous allons convertir cette macro en fonction personnalisée, en remplaçant le mot-clé Sub par Function :

Function VERIFMAIL()

Ce qui va nous intéresser ici, c’est les portions de code que nous retrouvons entre les guillemets, qui comme vous le constater correspondent à nos fonctions, traduites dans le langage de VBA.

Pour réaliser ces calculs directement dans VBA, nous allons pouvoir les utiliser en tant qu’argument de la fonction VBA Evaluate.

Celle-ci va en effet calculer le résultat de la formule, puis nous le renvoyer en sachant que la formule que nous avons mis en place est une formule booléenne qui nous enverra donc la valeur VRAI ou FAUX.

Nous allons effectuer tous ces tests successivement afin de vérifier qu’ils sont tous égaux à VRAI.

Pour démultiplier les tests dans un If… Then, nous pouvons utiliser l’instruction logique AND :

    If Evaluate("=(LEN(""" &  email & """)-LEN(SUBSTITUTE(""" & email  &  """,""@"","""")))=1")  And _
         Evaluate("=ISNUMBER(FIND(""."",RIGHT("""  & email & """,LEN(""" & email &  """)-FIND(""@"",""" &  email & """))))") And _
        Evaluate("=LEFT(""" & email  & """)<>""@""") And _
        Evaluate("=IFERROR(LEN(RIGHT("""  & email & """,LEN(""" & email &  """)-FIND(""@"",""" &  email & """))),0)>3") And _
        Evaluate("=ISERROR(FIND(""  "",""" & email & """))")  Then
            VERIFMAIL = True
    End If

Pour finir, nous pourrons affecter la valeur VRAI à la fonction si TOUS les tests retournent VRAI.

Au contraire, nous renverrons FAUX dans le cas contraire (il suffit qu’un seul test soit FAUX pour retourner FAUX), avec une instruction Else :

    If Evaluate("=(LEN(""" &  email & """)-LEN(SUBSTITUTE(""" & email  &  """,""@"","""")))=1")  And _
         Evaluate("=ISNUMBER(FIND(""."",RIGHT("""  & email & """,LEN(""" & email &  """)-FIND(""@"",""" &  email & """))))") And _
        Evaluate("=LEFT(""" & email  & """)<>""@""") And _
        Evaluate("=IFERROR(LEN(RIGHT("""  & email & """,LEN(""" & email &  """)-FIND(""@"",""" &  email & """))),0)>3") And _
        Evaluate("=ISERROR(FIND(""  "",""" & email & """))")  Then
            VERIFMAIL = True
    Else
            VERIFMAIL = False
    End If

Pour finaliser notre fonction, il ne va plus rester qu’à insérer un argument pour celle-ci en le saisissant entre les parenthèses de la déclaration.

Nous l’appellerons email et nous le typons en tant que String, soit une chaîne de caractères :

Function VERIFMAIL(email As String)

Ensuite, nous allons modifier les appels à la cellule B8 par le nom de cet argument.

Pour cela, nous pouvons les modifier manuellement, en sachant que ceux-ci doivent être exclus de la chaine de caractère, nous devrons donc utiliser l’esperluette pour concaténer les chaînes entre elles.

Il est également nécessaire d’insérer des guillemets qui seront repris dans la chaîne, pour cela, nous devrons les doubler, comme nous l’avons déjà vu dans un tutoriel précédent, ce qui fera trois guillemets consécutifs.

Soit, nous pouvons automatiser le remplacement en appuyant sur les touches [Ctrl]+[H] du clavier.

Dans ce cas, nous demandons à VBE de remplacer les termes « RC[-1] » par « """ & email & """ » (en faisant bien attention d’insérer trois guillemets à la suite au début et à la fin).

Nous cliquons ensuite sur le bouton « Remplacer tout » pour effectuer tous les remplacements d’un seul coup.

Ensuite, nous répétons cette opération pour toutes les occurrences (de RC[-2] jusqu’à RC[-5]).

Nous pouvons maintenant revenir sur la feuille de calcul et plutôt que d’utiliser la formule à rallonge mise en place au début de ce tutoriel, il nous suffira d’utiliser la fonction VERIFMAIL que nous venons de créer en l’appelant comme n’importe quelle autre fonction d’Excel et en passant la valeur de la cellule B8 comme argument :

Le résultat retourné par Excel est strictement identique, mais la fonction pourra être appelée très rapidement !