Blog

Bonjour !

Je vous propose dans ce long article de jouer avec quelques fonctions pour réaliser une validation de données complexe.

Compter le nombre de caractères dans une cellule, extraire du texte à partir de la gauche ou de la droite et décomposer le contenu de la cellule afin de vérifier qu’elle contient à la fois du texte et des chiffres, en respectant des conditions très précises.

Vous allez apprendre à utiliser ces fonctions de façon indépendante, puis nous les imbriquerons pour obtenir une formule unique pour répondre à notre objectif.

De l’action, du suspense et même quelques pièges qu’il vous faudra déjouer. Vous êtes prêt ?

Le Contexte

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, 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 ?

La Réponse 

Nous allons faire appel à la validation des données.

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
NON	NOT
ESTNUM	ISNUMBER
ET	AND
GAUCHE	LEFT
DROITE	RIGHT

Je donne ici les correspondances Excel / Google Sheets mais dans l’article 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.

Rappel sur la validation des données

Pour faire simple, dans le tableur, la validation des données est un outil qui “regarde” si une condition est remplie. Si la condition est remplie, la validation des données indique VRAI, et autorise la saisie, si la condition n’est pas remplie, alors elle indique FAUX et interdit la saisie.

Exemple, si je tape dans une cellule la formule suivante :

=1=1 

Le tableur indique VRAI. C’est exact car 1 est bien égal à 1.

Si te tape la formule suivante :

=1=2 

Le tableur indique FAUX. En effet, 1 n’est pas égal à 2.

Analyse du problème

Pour que la saisie de l’utilisateur puisse être acceptée, il faut répondre à 3 conditions :

• Les références saisies doivent donc faire 7 caractères de long ;
• Le premier caractère doit être une lettre ;
• les 6 caractères qui suivent doivent être des chiffres.

Derrière ces trois conditions qui paraissent simples, se cache une validation relativement complexe qui va faire appel à plusieurs fonctions.

Mettre en place une validation complexe

La validation de données “simple” qui utilise les critères standard fournis par le tableur n’est pas compliquée à mettre en place. Mais lorsque la validation des données fait appel à de multiples conditions, comme c’est le cas ici, aucune des options proposées par le tableur ne répond à notre besoin. Il va falloir utiliser une formule personnalisée. Mais la construction de la formule correcte peut s’avérer difficile à mettre en œuvre. 

Pour que nôtre validation de données fonctionne, il faut que nous testions les 3 conditions et que nous parvenions à un résultat VRAI à chaque fois.

Cette fiche montre comment procéder, pas à pas, pour réunir tous les éléments nécessaires à la mise en place de cette validation de données complexe. Retrouvez le fichier exemple ici.

La référence que nous voulons tester se trouve dans la cellule B10 du fichier exemple.

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(B10) 

En clair :

Donne moi la longueur de ce qui se trouve en B10. 

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(B10)=7

En clair :

Dis moi si le contenu de B10 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 B10 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

Pour vérifier ça, il va nous falloir utiliser pas moins de 3 fonctions ! Pas de panique, voyons tout ça en détails.

— é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(B10;1)

En clair :

Donne moi le premier caractère à gauche de la cellule B10. 

Le tableur indique le résultat trouvé : A

— étape 2 – qu’est-ce que tu es ?

Maintenant que nous savons isoler le premier caractère de notre référence (=LEFT(B10;1)), on doit savoir si c’est bien une lettre.

Pour ça on va utiliser un moyen qui peut paraître un peu contre-intuitif. On ne va pas demander au tableur si ce caractère est une lettre. On va demander au tableur si ce premier caractère est un nombre.

Pour savoir si le contenu d’une cellule est un nombre, on va utiliser la fonction ISNUMBER. 

Demandons au tableur si le premier caractère est un nombre :

=ISNUMBER(left(B10;1))

En clair :

Dis moi si le premier caractère de B10 est un nombre. 

La réponse du tableur est : FALSE.

C’est exact, A n’est pas un nombre.

Mais, il nous faut un TRUE pour que notre validation de données fonctionne.

Bien, inversons alors notre question.

— étape 3 – qu’est-ce que tu n’es pas  ?

Pour inverser un résultat (Obtenir un TRUE si le résultat est FALSE), on va utiliser la Fonction NOT.

On pose alors notre question de cette façon :

=NOT(ISNUMBER(left(B10;1)))

En clair :

Dis moi si le premier caractère de B10 n’est pas un nombre. 

La réponse du tableur est : TRUE.

Parfait, c’est ce que nous voulions. Mais attention ici, il y a un piège ! Vous l’avez trouvé ? Non ? On va y revenir plus tard.

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 n’est pas un nombre. 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(B10;6)

En clair :

Donne moi les 6 premiers caractères en partant de la droite de la cellule B10. 

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(B10;6))

En clair :

Dis moi si les 6 premiers caractères en partant de la droite de la cellule B10 constituent un nombre. 

Le tableur indique le résultat trouvé : FALSE

Hein ? Comment ? C’est quoi l’arnaque ? 123456, c’est bien 6 chiffres, et ces six chiffres, ça forme bien un nombre ! Il est fou le tableur ou quoi ?

Pas du tout ! Nous venons de tomber dans le piège dont je parlais un peu plus tôt ! Je vous explique :

Lorsque vous saisissez quelque chose dans une cellule de votre tableur, le type de données est automatiquement attribué (Nombre, Date, heure, booléens, etc.).

Si vous saisissez le chiffre 123, le tableur traite cette cellule comme un nombre.

Si vous saisissez abc, le tableur comprend que vous utilisez du texte.

Quand vous utilisez un mélange des deux, A123456, et bien c’est du texte. Donc tout ce qui est dans la cellule B10 est considéré comme du texte.

Et quand on extrait ces 6 chiffres, ce sont des chiffres au format texte, et pas des chiffres au format nombre. 

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(B10;6)*1

En clair :

Prends les 6 premiers caractères en partant de la droite de la cellule B10 et multiplie-les par 1. 

Puis on demande à nouveau si c’est un nombre :

=ISNUMBER(right(B10;6)*1)

En clair :

Dis moi si les 6 premiers caractères multipliés par 1 en partant de la droite de la cellule B10 constituent un nombre. 

Le tableur indique le résultat trouvé : TRUE

Notre troisième condition est remplie !

Petit Correctif

Avant de mettre en place notre validation de données, il va nous falloir corriger une erreur que nous avons laissée derrière nous.

Et oui, vous vous souvenez quand nous avons vérifié que le A n’était pas nombre. Essayez de remplacer le A par le chiffre 1 par exemple. AÎe .. Le tableur nous dit que 1 n’est pas un chiffre … 

… pour la même raison que tout à l’heure quand nous avons vérifié que les 6 caractères de droite étaient ou non des chiffres.

Ici aussi, il va falloir multiplier par 1 le résultat de notre fonction LEFT car sans celà, un chiffre restera du texte. Avec le A, l’erreur était invisible, mais avec un chiffre, elle vous saute soudainement au visage !

Modifiez la formule en ajoutant *1

=NOT(ISNUMBER(left(B10;1)*1))

On met la validation en place

Bien ! Nous avons nos trois TRUE ! 

=LEN(B10)=7

On vérifie que la référence fait 7 caractères.

=NOT(ISNUMBER(left(B10;1)*1))

On vérifie que le premier caractère n’est pas un nombre.

=ISNUMBER(right(B10;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(B10)=7;NOT(ISNUMBER(left(B10;1)*1));ISNUMBER(right(B10;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à !

Toutes les cellules concernées par cette validation ne pourront désormais pas recevoir une saisie qui ne correspond pas à votre modèle : une lettre – 6 chiffres.

La fin ? pas tout à fait…

Essayez de saisir un caractère comme # ou @ dans la référence à la place du premier caractère et vous verrez que le tableur acceptera sans broncher ce caractère. Ce n’est pas un chiffre, certes, mais pas vraiment une lettre.  

Pour aller plus loin, je vous prépare un nouvel article qui viendra compléter celui-ci et nous verrons cette fois comment forcer l’usage des lettres de l’alphabet uniquement pour le premier caractère de notre référence !

A bientôt pour un prochain partage !

Photo d’illustration de l’article : PxHere

---

Tous les articles par catégorie

Envie de plus de lecture ? Tous les articles sont listés sur cette page par catégorie.