Formule générique
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sommaire
Pour mettre en surbrillance les cellules contenant l'une des nombreuses chaînes de texte, vous pouvez utiliser une formule basée sur les fonctions ISNUMBER et SEARCH, ainsi que la fonction SUMPRODUCT. Dans l'exemple illustré, la mise en forme conditionnelle appliquée à B4: B11 est basée sur cette formule:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Explication
En travaillant de l'intérieur vers l'extérieur, cette partie de la formule recherche dans chaque cellule de B4: B11 toutes les valeurs de la plage nommée «choses»:
--ISNUMBER(SEARCH(things,B4)
La fonction SEARCH renvoie la position de la valeur si elle est trouvée, et l'erreur #VALUE si elle n'est pas trouvée. Pour B4, les résultats reviennent dans un tableau comme celui-ci:
(8;#VALUE!;#VALUE!)
La fonction ISNUMBER change tous les résultats en TRUE ou FALSE:
(TRUE;FALSE;FALSE)
Le double négatif devant ISNUMBER force TRUE / FALSE à 1/0:
(1;0;0)
La fonction SUMPRODUCT additionne ensuite les résultats, qui sont testés par rapport à zéro:
=SUMPRODUCT((1;0;0))>0
Tout résultat différent de zéro signifie qu'au moins une valeur a été trouvée, donc la formule renvoie VRAI, déclenchant la règle.
Ignorer les choses vides
Pour ignorer les cellules vides dans la plage nommée «choses», vous pouvez essayer une formule modifiée comme celle-ci:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Cela fonctionne tant que les valeurs de texte que vous testez ne contiennent pas la chaîne "FALSE". Si tel est le cas, vous pouvez étendre la fonction IF pour inclure une valeur si false connue pour ne pas apparaître dans le texte (c'est-à-dire "zzzz", "####", etc.)
Option sensible à la casse
SEARCH n'est pas sensible à la casse. Pour vérifier également la casse, remplacez SEARCH par FIND comme suit:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Empêcher les fausses correspondances
Un problème avec cette approche est que vous pouvez voir de fausses correspondances causées par des sous-chaînes qui apparaissent à l'intérieur de mots plus longs. Par exemple, si vous essayez de faire correspondre "dr", vous pouvez également trouver "Andrea", "drink", "dry", etc. puisque "dr" apparaît à l'intérieur de ces mots. Cela se produit parce que SEARCH effectue automatiquement une correspondance "contient".
Pour une correction partielle, vous pouvez ajouter un espace autour des mots recherchés (par exemple, "dr" ou "dr") pour éviter d'attraper "dr" dans un autre mot. Mais cela échouera si "dr" apparaît en premier ou en dernier dans une cellule, ou apparaît à côté de la ponctuation. Cela peut être partiellement résolu en ajoutant de l'espace également autour du texte d'origine. Pour ajouter de l'espace au début et à la fin des deux en même temps, vous pouvez essayer une formule comme celle-ci:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Cependant, cela ne résoudra pas les problèmes causés par la ponctuation.
Si vous avez besoin d'une solution plus complète, une option consiste à normaliser le texte d'abord dans une colonne d'aide, en prenant soin d'ajouter également un espace de début et de fin. Ensuite, vous pouvez rechercher des mots entiers entourés d'espaces.
