Kim a une colonne de données utilisée pour contenir un code de localisation. Ce code se compose d’une seule lettre suivie de deux chiffres, tels que A03 ou B12.

Kim aimerait mettre en forme conditionnellement la colonne afin que tout ce qui est entré dans la colonne qui n’utilise pas ce modèle soit mis en évidence d’une manière ou d’une autre.

Il existe de nombreuses manières d’aborder ce problème. Chaque approche dépend du développement d’une formule qui peut être utilisée dans une règle de mise en forme conditionnelle pour renvoyer True ou False et déclencher le format conditionnel. (Cette astuce ne vous expliquera pas comment créer une règle de mise en forme conditionnelle, mais se concentrera plutôt sur les différentes formules qui peuvent être utilisées dans la règle. La façon dont vous créez des règles de mise en forme conditionnelle est traitée dans d’autres ExcelTips.)

Quelle que soit la formule mise en place, il faut tester trois choses:

  • Il y a exactement trois caractères dans la chaîne.

  • Le premier caractère est une lettre.

  • Les deuxième et troisième caractères sont des chiffres.

Savoir si le texte d’une cellule ne comporte que trois caractères est assez simple; vous pouvez utiliser la fonction LEN pour le faire:

=LEN(A1)=3

Savoir si le premier caractère est une lettre est également assez facile. En fait, il y a plusieurs façons d’y parvenir. L’un des éléments suivants renverra True si le premier caractère est une lettre:

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

Celles-ci vérifient que seule une lettre majuscule est en première position. Si vous souhaitez également accepter les lettres minuscules, vous pouvez utiliser une variante du deuxième test:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

Si les lettres majuscules et minuscules sont acceptables (avec pratiquement n’importe quel autre symbole), vous pouvez envisager d’utiliser le test suivant:

=NOT(ISNUMBER(LEFT(A1,1)+0))

Voici quelques façons d’appliquer le troisième test – si les deuxième et troisième caractères sont des chiffres:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

Notez que ces approches traitent les deux derniers caractères ensemble. Cela signifie que « 1 », « 11 » et « 111 » réussiraient tous le test – ils ont réussi à vérifier sous forme de nombres. Si votre formule ne vérifiait que les deux derniers chiffres, cela pourrait être un problème, mais le fait que vous incluiez également la première vérification (pour la longueur totale de la chaîne dans la cellule et qu’elle doit être 3), alors cela ne ne présente pas du tout de problème.

L’astuce, maintenant, est de combiner votre approche de choix pour chacun des trois tests en une seule formule. Cela peut être fait en utilisant la fonction ET. Je vais juste choisir le plus court de chacun des tests et les combiner de cette manière:

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

Telle qu’elle est écrite, cette formule renvoie True si tous les tests sont réussis, ce qui signifie que la cellule contient un code d’emplacement avec un modèle valide. Cela fonctionnerait très bien comme format conditionnel si Kim formait la colonne en tant que couleur (par exemple, vert), puis utilisait le format conditionnel pour supprimer la couleur verte. Cela peut sembler arriéré et vous ne voudrez peut-être appliquer un format que si le modèle n’est pas respecté. Si tel est le cas, encaissez simplement la formule dans une fonction NOT pour inverser le Vrai / Faux qui est retourné:

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

Comme vous pouvez le constater, l’utilisation d’une formule comme celle-ci peut être un peu délicate. Vous pouvez, si vous préférez, créer une UDF (fonction définie par l’utilisateur) qui raccourcirait un peu la règle de mise en forme conditionnelle. La macro suivante est une bonne solution:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

Pour utiliser l’UDF dans votre règle de mise en forme conditionnelle, il vous suffit d’utiliser la formule suivante:

=IsBadPattern(A1)

Le résultat de l’UDF sera TRUE si la chaîne de la cellule référencée ne correspond pas au modèle souhaité. Tel qu’il est écrit, il ne permettra pas l’utilisation de lettres minuscules dans la première position de caractère. Si vous devez autoriser les lettres minuscules, vous n’avez pas besoin de modifier l’UDF. Au lieu de cela, modifiez la formule comme suit:

=IsBadPattern(UPPER(A1))

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (9976) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.