Formule Excel : Vérifier si une cellule contient certains textes mais pas d’autres

Pour vérifier si une cellule contient certains textes de la plage A tout en n’en contenant aucun de la plage B, utilisez une formule matricielle combinant les fonctions NB, TROUVE et ET dans Excel. Ce tutoriel vous explique pas à pas comment mettre en œuvre cette formule.

Formule générique :

Arguments

Valeur de retour:

Cette formule renvoie une valeur logique : VRAI si la cellule contient au moins l’un des éléments de l’argument Inclure et aucun des éléments de l’argument Exclure ; FAUX dans tous les autres cas.

Fonctionnement de cette formule

Supposons que vous souhaitiez vérifier si la cellule B3 contient l’une des valeurs de la plage E3:E5, tout en n’en contenant aucune de la plage F3:F4. Utilisez alors la formule suivante :

Appuyez sur Maj + Ctrl + Entrée pour valider la cellule.

Explication

Partie 1 : ( COUNT(SEARCH($E$3:$E$5,B3))>0) vérifie si la cellule B3 contient l’une des valeurs de la plage E3:E5

Fonction TROUVE : la fonction TROUVE renvoie la position du premier caractère d’une chaîne de texte à l’intérieur d’une autre. Si le texte recherché est trouvé, elle renvoie sa position relative ; sinon, elle génère une erreur #VALEUR!. Ici, la formule SEARCH($E$3:$E$5,B3) recherche chaque valeur de la plage E3:E5 dans la cellule B3 et renvoie la position de chaque chaîne trouvée dans cette cellule, produisant un résultat sous forme de tableau tel que : {1;7;#VALUE!}.

Fonction NB : la fonction NB dénombre les éléments d’une plage ou d’un tableau. COUNT(SEARCH($E$3:$E$5,B3)) renvoie 2, car le tableau {1;7;#VALUE!} contient deux éléments.

COUNT(SEARCH($E$3:$E$5,B3))>0Comparez le résultat de la fonction NB à zéro. Dès qu’il est supérieur à 0, la formule renvoie VRAI ; sinon, elle renvoie FAUX. Ici, le résultat est VRAI.

Partie 2 : COUNT(SEARCH($F$3:$F$4,B3))=0 vérifie si la cellule B3 ne contient aucune des valeurs de la plage F3:F4

Fonction TROUVE : la fonction TROUVE renvoie la position du premier caractère d’une chaîne de texte à l’intérieur d’une autre. Si le texte recherché est trouvé, elle renvoie sa position relative ; sinon, elle génère une erreur #VALEUR!. Ici, la formule SEARCH($F$3:$F$4,B3) recherche chaque valeur de la plage F3:F4 dans la cellule B3 et renvoie la position de chaque chaîne trouvée dans cette cellule, produisant un résultat sous forme de tableau tel que : {#VALUE!; #VALUE!; #VALUE!}.

Fonction NB : la fonction NB dénombre les éléments d’une plage ou d’un tableau. COUNT(SEARCH($F$3:$F$4,B3)) renvoie 0, car aucun élément du tableau {#VALUE!; #VALUE!; #VALUE!} n’est valide.

COUNT(SEARCH($F$3:$F$4,B3))>0Comparez le résultat de la fonction NB à zéro. Dès que ce résultat est égal à 0, la formule renvoie VRAI ; sinon, elle renvoie FAUX. Ici, le résultat est VRAI.

Partie 3 :Fonction ET

=AND(COUNT(SEARCH($E$3:$E$5,B3))>0,COUNT(SEARCH($F$3:$F$4,B3))=0)

=AND(TRUE,TRUE)

=TRUE

Fichier exemple

Cliquez pour télécharger le fichier exemple

Formules associées

• Vérifier si une cellule contient un texte spécifique
  Pour vérifier qu’une cellule contient au moins un des textes de la plage A tout en n’en contenant aucun de la plage B, utilisez une formule matricielle combinant les fonctions NB, TROUVE et ET dans Excel.
• Vérifier si une cellule contient l’une des valeurs spécifiées tout en excluant d’autres valeurs
  Ce tutoriel vous présente une formule Excel efficace pour vérifier rapidement si une cellule contient l’une des valeurs indiquées, tout en excluant d’autres, et détaille chaque argument de cette formule.
• Vérifier si une cellule contient l’une des valeurs spécifiées
  Supposons qu’Excel contienne une liste de valeurs dans la colonne E. Vous souhaitez vérifier si les cellules de la colonne B contiennent l’une des valeurs de la colonne E et renvoyer VRAI ou FAUX.
• Vérifier si une cellule contient un nombre
  Vous avez parfois besoin de savoir si une cellule contient un nombre. Ce tutoriel vous propose une formule qui renvoie VRAI si c’est le cas, et FAUX sinon.