Le Contexte
Cette article complète une précédente publication Comment vérifier et contrôler la saisie d’une référence produit – partie 1. Je vous conseille d’aller lire cette fiche avant de travailler sur cette seconde partie.
Rappel des règles établies dans le précédent article
Dans un tableau, des employés doivent saisir des références produits. Ces références doivent respecter la règle de construction suivante :
- elles doivent être de 7 caractères de long,
- le premier caractère doit être une lettre de A à Z (minuscule ou majuscule),
- et les 6 caractères suivants doivent être des chiffres.
Exemple : A123456.
Les Questions
Comment éviter les erreurs lors de la saisie de ces références ? Comment interdire une saisie qui ne respecte pas ces règles ?
On monte en puissance …
Nous avons vu que notre précédente validation de données n’est pas 100% efficace. En effet, le premier caractère ne peut pas être un chiffre mais on a pu constater qu’il peut être remplacé par un symbole (@, #, !, etc..) et tromper ainsi notre validation.
Nous devons donc trouver un autre moyen pour contrôler la deuxième condition.
Comment corriger ce problème ?
La Réponse
Nous allons donc faire de nouveau appel à la validation des données en introduisant une nouvelle fonction : la fonction CODE().
Cette fonction permet de vérifier le numéro UNICODE d’un caractère.
Dans cet article, nous allons voir comment mettre en place une validation de données complexe et nous utiliserons pour cela les fonctions suivantes :
| Excel | Google Sheets |
| NBCAR | LEN |
| UNICODE | CODE |
| ESTNUM | ISNUMBER |
| ET | AND |
| OU | OR |
| GAUCHE | LEFT |
| DROITE | RIGHT |
Je donne ici les correspondances Excel / Google Sheets mais dans la fiche j’utiliserai les formules et fonctions de Google.
Si vous ne savez pas ce qu’est une validation de données sous Excel ou Google Sheets, je vous invite à regarder ma précédente vidéo sur le sujet dans laquelle j’aborde les fondamentaux de cet outil.
C’est quoi UNICODE ?
À chaque caractère que vous saisissez au clavier correspond un numéro UNICODE.
Par exemple, le code UNICODE pour la lettre E est 69.
Les emojis tant appréciés possèdent également leur numéro UNICODE.
🦊 Lui, par exemple, son numéro c’est le 129418.
C’est grâce à ces codes que nous allons pouvoir vérifier que le premier caractère de notre référence est une lettre majuscule ou minuscule de a à z.
La première chose à faire est donc de connaître les numéros UNICODE pour les 26 lettres de l’alphabet, en majuscule et en minuscule.
Pour ça on utilise la fonction CODE().
Dans l’exemple ci-dessous, la lettre A est saisie dans la cellule C2.
Dans la cellule C3, la formule est :
=code(C2)
Le résultat indique 65. 65 est le numéro UNICODE de la lettre A majuscule.
Si on effectue la même manipulation avec un a minuscule, on obtient le numéro 97.
Grâce à la fonction CODE(), il est donc facile d’obtenir tous les numéros UNICODE de nos 26 lettres de l’alphabet, en minuscules et en majuscules.
Liens utiles : pour avoir la liste de tous les codes UNICODE, vous pouvez aller voir par ici ou par là si vous êtes amateur d’emojis.
Identifier une lettre Majuscule
Grâce au tableau ci-dessus, nous savons que les numéros UNICODE des lettres majuscules de A à Z vont de 65 à 90.
Ainsi le code UNICODE d’une majuscule doit être plus grand ou égal à 65 et plus petit ou égal à 90. Pour être certains d’avoir à faire à une majuscule, nous devons répondre par la positive (TRUE / VRAI) à ces deux conditions.
Cette formule vérifie que le code qui correspond à la lettre a est supérieur ou égal à 65. C’est le cas, la formule renvoie donc VRAI (TRUE).
Cette seconde formule vérifie si le code qui correspond au caractère est inférieur ou égal à 90. Ce n’est pas le cas et la formule renvoie donc FAUX (FALSE).
Combinons ces deux vérifications avec l’opérateur AND (ET).
En clair :
Est-ce que le caractère possède un code plus grand ou égal à 65 ET plus petit ou égal à 90.
Ici, nous ne répondons pas aux deux critères, ce n’est donc pas une Majuscule.
Identifier une lettre Minuscule
Le tableau indique que les numéros UNICODE des lettres minuscules de a à z vont de 97 à 122.
Ainsi le code UNICODE d’une minuscule doit être plus grand ou égal à 97 et plus petit ou égal à 122. Pour être certains d’avoir à faire à une minuscule, nous devons répondre par la positive (TRUE/VRAI) à ces deux conditions.
Cette première formule vérifie que le code qui correspond à la lettre a est supérieur ou égal à 97. C’est le cas, la formule renvoie donc VRAI (TRUE).
Cette seconde formule vérifie si le code qui correspond au caractère est inférieur ou égal à 122. C’est le cas également et la formule renvoie donc VRAI (TRUE).
Combinons ces deux vérifications avec l’opérateur AND (ET).
En clair :
Est-ce que le caractère possède un code plus grand ou égal à 97 ET plus petit ou égal à 122.
Ici, nous répondons bien aux deux critères, c’est donc une Minuscule.
Est-ce que le caractère est bien une lettre de A à Z ?
Dans notre référence, nous acceptons la saisie des lettres minuscules et des lettres majuscules. On doit donc envisager les deux possibilités. Nous allons combiner ces deux formules avec l’opérateur OR (OU).
Ainsi, la condition sera vraie si le code du caractère contrôlé est :
- plus grand ou égal à 65 ET plus petit ou égal à 90
- OU
- plus grand ou égal à 97 ET plus petit ou égal à 122
Nous avons notre formule ! Cette fois-ci, c’est sûr, les utilisateurs ne pourront pas saisir autre chose qu’une lettre comme premier caractère de notre référence.
Dans le précédent exemple, c’est donc la seconde condition que nous allons remplacer par cette nouvelle formule.
Testons notre méthode avec la référence suivante en B3 :
Vérification de la première condition
La référence doit faire 7 caractères de longueur
Pour vérifier que notre référence fait bien 7 caractères de longueur, nous allons utiliser la fonction LEN(). Saisissons la formule suivante :
=LEN(B3) En clair :
Donne-moi la longueur de ce qui se trouve en B3.
Le tableur indique le résultat trouvé : 7
Nous avons ici récupéré la longueur de la référence mais nous ne pouvons pas en faire grand-chose. Ce dont nous avons besoin en réalité c’est que le tableur nous dise si la longueur est égale à 7 (VRAI) ou si elle n’est pas égale à 7 (FAUX).
Donc, posons la question au tableur :
Est-ce que la référence fait 7 caractères de long ?
=LEN(B3)=7En clair :
Dis moi si le contenu de B3 est = à 7.
Cette fois-ci la réponse du tableur est : TRUE.
Notre première condition est remplie !
Vous pouvez modifier la référence en B3 pour voir comment va réagir votre formule. Si vous ajoutez ou supprimez un ou plusieurs chiffres, le résultat passe à FALSE (FAUX).
Vérification de la deuxième condition
La référence doit commencer par une lettre
— étape 1 – à Bâbord toutes !
Tout d’abord, nous allons devoir extraire le premier caractère de notre référence afin de pouvoir l’analyser. Pour ça on va utiliser la fonction LEFT (GAUCHE).
Cette fonction permet d’extraire le nombre de caractère désiré dans une chaîne à partir de la gauche. Ici nous voulons un seul caractère, le premier.
=LEFT(B3;1)En clair :
Donne moi le premier caractère à gauche de la cellule B3.
Le tableur indique le résultat trouvé : A
— étape 2 – est-ce que tu es une lettre ?
Maintenant que nous savons isoler le premier caractère de notre référence (=LEFT(B3;1)), on doit savoir si c’est bien une lettre.
Nous allons maintenant utiliser notre super formule qui utilise CODE(), AND et OR pour vérifier ça ! Au lieu d’indiquer la cellule qui contient la référence, nous allons indiquer le premier caractère de cette cellule avec LEFT().
=OR(AND(CODE(LEFT(B3;1))>=65;CODE(LEFT(B3;1))<=90);AND(CODE(LEFT(B3;1))>=97;CODE(LEFT(B3;1))<=122))En clair, dis moi si :
le premier caractère est plus grand ou égal à 65 ET plus petit ou égal à 90
OU
le premier caractère est plus grand ou égal à 97 ET plus petit ou égal à 122.
La formule avec des couleurs :
La réponse du TABLEUR est TRUE (VRAI). Nous avons bien une lettre comme premier caractère de la référence.
Notre deuxième condition est remplie !
On récapitule, nous savons vérifier que la référence fait bien 7 caractères, et nous savons vérifier que le premier caractère est une lettre de A à Z, majuscule ou minuscule, à l’exclusion de tout autre caractère.
Il nous reste à vérifier que les 6 caractères qui suivent sont des chiffres.
Vérification de la troisième condition
— étape 1 – à Tribord toutes !
Pour pouvoir travailler sur les 6 derniers caractères de notre référence, il va nous falloir les extraire d’abord. Pour ça on va utiliser la fonction RIGHT (DROITE).
Cette fonction permet d’extraire le nombre de caractères désiré dans une chaîne à partir de la droite. Ici nous voulons les 6 premiers caractères en partant de la droite.
=RIGHT(B3;6)En clair :
Donne moi les 6 premiers caractères en partant de la droite de la cellule B3.
Le tableur indique le résultat trouvé : 123456
— étape 2 – qu’est-ce que tu es (bis repetita) ?
Nous voulons des chiffres et uniquement des chiffres. Donc c’est le moment où jamais d’utiliser la fonction ISNUMBER !
Demandons au tableur si cette série de caractères est bien un nombre :
=ISNUMBER(right(B3;6))En clair :
Dis moi si les 6 premiers caractères en partant de la droite de la cellule B3 constituent un nombre.
Le tableur indique le résultat trouvé : FALSE
Pourquoi ? 123456, c’est bien 6 chiffres ? Pourquoi est-ce que j’obtiens FALSE ?
Nous venons de re-tomber dans le piège ! Comme nous l’avons vu dans le précédent article, le contenu de cette cellule est considéré comme du texte car il y a une lettre à l’intérieur.
Pour corriger ça, il suffit de multiplier nos chiffres par 1, et le tableur comprend que vous voulez les utiliser comme des nombres et pas comme du texte.
Ajoutons *1 à notre fonction RIGHT.
=RIGHT(B3;6)*1En clair :
Prends les 6 premiers caractères en partant de la droite de la cellule B3 et multiplie-les par 1.
Puis on demande à nouveau si c’est un nombre :
=ISNUMBER(right(B3;6)*1)En clair :
Dis moi si les 6 premiers caractères multipliés par 1 en partant de la droite de la cellule B3 constituent un nombre.
Le tableur indique le résultat trouvé : TRUE
Notre troisième condition est remplie !
On met la validation en place
Bien ! Nous avons nos trois TRUE !
=LEN(B3)=7On vérifie que la référence fait 7 caractères.
=OR(AND(CODE(LEFT(B3;1))>=65;CODE(LEFT(B3;1))<=90);AND(CODE(LEFT(B3;1))>=97;CODE(LEFT(B3;1))<=122))On vérifie que le premier caractère est bien une lettre de A à Z, majuscule ou minuscule.
=ISNUMBER(right(B3;6)*1)On vérifie que les 6 caractères qui suivent sont bien des nombres.
Maintenant on assemble tout ça.
Pour que la saisie soit acceptée, il faut que les 3 conditions soient vérifiées ; si une seule condition n’est pas validée, alors la saisie sera refusée.
Pour assembler toutes nos vérifications en une seule, on va utiliser la fonction AND (ET).
On schématise d’abord :
=AND(a;b;c)à la place de chacune des lettres, on va insérer nos formules de calcul :
=AND(LEN(B3)=7;OR(AND(CODE(LEFT(B3;1))>=65;CODE(LEFT(B3;1))<=90);AND(CODE(LEFT(B3;1))>=97;CODE(LEFT(B3;1))<=122));ISNUMBER(right(B3;6)*1))Pour mettre en place notre validation sur une ou plusieurs cellules de notre feuille de calcul, il ne nous reste plus qu’à copier cette formule et la coller dans le champ La formule personnalisée est :”
Et voilà ! La saisie d’une référence qui ne respecte pas les conditions est désormais impossible !
Allez, ça y est ! Vous pouvez reprendre votre souffle.
Si vous êtes arrivé jusqu’ici et que tout fonctionne, je vous félicite. Si vous rencontrez une erreur, reprenez vos formules, vérifiez votre syntaxe (un petit point virgule qui manque suffit à tout faire capoter).
Vous pouvez retrouver le fichier Google Sheets avec toutes les formules en cliquant sur cette image :
À bientôt pour un prochain partage !
Tous les articles par catégorie
Envie de plus de lecture ? Tous les articles sont listés sur cette page par catégorie.
