Tester la validité d'un numéro de téléphone avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Validité d'un numéro de téléphone

Avec cette nouvelle astuce Excel, nous allons apprendre à tester la validité de la saisie d'un numéro de téléphone, mais pas seulement. En effet, en dérivant la technique que nous allons découvrir, nous serons en mesure de tester les inscriptions consistant en des séquences remarquables, comme c'est le cas pour les dates ou pour les numéros de sécurité sociale par exemple.

Classeur source
Pour simplifier l'étude, nous proposons de travailler à partir d'un classeur présentant quelques séquences remarquables à tester.

• Télécharger le classeur validite-telephone.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons une rangée de numéros de téléphone en colonne C. Mais en y regardant de plus près, ils ne sont pas tous conformes. A première vue, ce n'était pas évident. Et cette remarque est d'autant plus vraie pour des bases de données de centaines de lignes et de dizaines de colonnes.

La colonne E est vide quant à elle. Elle est intitulée Validité. C'est elle qui doit livrer la sentence quand à la bonne conformité des numéros inscrits en regard.

Contrôler les séquences saisies
La forme remarquable d'un numéro de téléphone est relativement simple à contrôler. Il est nécessairement codé sur dix caractères par groupes de deux chiffres, délimités les uns des autres par un espace. A l'instar de l'astuce précédente pour détecter les lignes répondant à un critère parmi plusieurs, ce sont les WildCards ou caractères génériques qui apportent la solution. Le symbole du point d'interrogation (?) représente n'importe quel caractère. En réalisant un assemblage reproduisant la séquence d'un numéro de téléphone, nous pouvons l'utiliser en critère de la fonction de dénombrement Nb.Si. Si elle répond par une valeur non nulle, nous saurons que le numéro est correctement formé.

Remarque importante : La colonne C des numéros est formatée en texte pour conserver l'affichage des zéros en préfixe. Elle aurait pu être formatée directement avec le type numéro de téléphone. Mais dans ce cas, le zéro en préfixe aurait été visible seulement en apparence, c'est-à-dire à l'affichage, donc non considéré dans la séquence.

• Sélectionner la cellule du premier test à livrer en cliquant sur la case E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,
• Désigner le premier numéro à tester en cliquant sur sa cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Nb.Si,

C'est ici que nous devons tester l'enchaînement des caractères par groupes de deux grâce au caractère générique du point d'interrogation.

• Entre guillemets, inscrire la séquence suivante : "?? ?? ?? ?? ??",

Il s'agit bien de cinq paires de caractères, chacune espacée d'une autre par un espace.

• Fermer la parenthèse de la fonction Nb.Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,

Le premier résultat tombe. Avec le chiffre 1, il confirme que la première séquence est bien un numéro de téléphone valide.

• Cliquer et glisser la poignée du résultat jusqu'en cellule E12,

Les verdicts semblent corrects mais en apparence seulement. Le deuxième et le troisième numéros sont sanctionnés par le chiffre 0. Ils ne sont effectivement pas conformes. Il manque un espace entre les deux dernières paires de chiffres dans le deuxième et les tirets remplacent l'espace dans le troisième.

Par contre, les cinquième et septième séquences sont considérées comme correctes. Or, il n'en est rien. La forme remarquable est certes respectée. Il s'agit bien d'un enchaînement de cinq paires de caractères, toutes séparées d'une autre par un espace. Mais des lettres de l'alphabet y sont glissées.

Tester si la séquence est numérique
Ce premier test, bien que satisfaisant pour analyser la forme de la chaîne, n'est pas suffisant. Nous devons lui recouper une seconde condition. Elle doit consister à vérifier que tous les caractères inscrits sont bien des chiffres. Pour cela, l'astuce consiste à tester si la séquence purgée de ses espaces est bien numérique grâce à la fonction logique EstNum.

• Sélectionner de nouveau la cellule du premier résultat, soit E4,
• Dans sa barre de formule, cliquer juste après le symbole égal pour y placer le point d'insertion,
• Inscrire la fonction de recoupement des critères, suivie d'une parenthèse, soit : Et(,

Ainsi, nous accueillons d'ores et déjà la première condition de la formule précédente consistant à vérifier la forme de la chaîne du potentiel numéro de téléphone.

• Cliquer maintenant à la toute fin de la syntaxe, soit après la parenthèse fermante,
• Taper un point-virgule (;) pour poursuivre l'énumération des critères de la fonction Et,
• Inscrire la fonction de test numérique suivie d'une parenthèse, soit : EstNum(,

Nous l'avons dit, nous devons tester la chaîne purgée de ses espaces. Pour cela, nous devons exploiter la fonction de remplacement Substitue pour échanger chaque espace avec une chaîne vide, ce qui aura pour effet de les supprimer tous.

• Inscrire la fonction de remplacement suivie d'une parenthèse, soit : Substitue(,
• Désigner le premier numéro de téléphone en cliquant sur sa cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Inscrire un espace entre guillemets, soit : " ",
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Inscrire deux guillemets, soit : "", pour former une chaîne vide,
• Fermer la parenthèse de la fonction Substitue,
• Multiplier ce résultat par un, soit : *1, pour tenter de forcer la conversion en valeur numérique,
• Fermer la parenthèse de la fonction EstNum,
• Puis, fermer la parenthèse de la fonction Et,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Cette fois et fort logiquement, c'est un booléen qui sanctionne le numéro testé. Avec la valeur Vrai, il indique sans équivoque que la première séquence correspond bien à un numéro de téléphone correctement formé.

• Double cliquer sur la poignée de la cellule du résultat pour propager la logique,

Comme vous pouvez le voir, les faux numéros de téléphone commentés précédemment ont été exclus de l'identification, en raison de la présence de caractères non conformes. La syntaxe complète de la formule que nous avons construite est la suivante :

=ET(NB.SI(C4;"?? ?? ?? ?? ??"); ESTNUM(SUBSTITUE(C4; " "; "")*1))



Dans le même temps vous l'avez sans doute constaté, une règle de mise en forme conditionnelle prédéfinie fait surgir en couleur tous les numéros non valides, afin de les identifier plus facilement. Pour la consulter, vous devez tout d'abord cliquer sur l'un des numéros de téléphone. Ensuite, dans la section Styles du ruban Accueil, vous devez cliquer sur le bouton Mise en forme conditionnelle. Dans la liste des propositions du menu, vous devez choisir l'option Gérer les règles. Dans la boîte de dialogue qui suit, vous devez cliquer sur le bouton Modifier la règle.



Elle se contente simplement de vérifier si le résultat livré par notre calcul (E4) retourne la valeur Faux pour appliquer une couleur Orange aux numéros de téléphone sur lesquels elle est construite.