Formule générique
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sommaire
Pour tester une cellule pour voir si elle contient l'une des nombreuses chaînes, vous pouvez utiliser une formule basée sur les fonctions SEARCH, ISNUMBER et SUMPRODUCT. La formule en C5, copiée vers le bas, est:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
où les choses est la plage nommée E5: E9.
Explication
Nous voulons tester chaque cellule de B5: B11 pour voir si elle contient l'une des chaînes de la plage nommée choses (E5: E9). La formule que nous utilisons dans C5, copiée vers le bas, est:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Cette formule est basée sur une formule (expliquée ici) qui vérifie une cellule pour une seule sous-chaîne. Si la cellule contient la sous-chaîne, la formule renvoie TRUE. Sinon, la formule renvoie FALSE:
ISNUMBER(SEARCH(things,B5))
Cependant, dans ce cas, nous donnons à SEARCH une liste de chaînes. Puisqu'il y a 5 chaînes dans les choses , SEARCH renvoie 5 résultats dans un tableau comme celui-ci:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Lorsque SEARCH trouve une chaîne, elle renvoie la position de cette chaîne. Si SEARCH ne trouve pas de chaîne, il renvoie une #VALUE! Erreur. Parce que "jaune" apparaît comme premier mot dans B5, nous voyons un 1. Parce que les autres chaînes ne sont pas trouvées, les 4 autres éléments sont des erreurs.
Ce tableau est renvoyé directement à la fonction ISNUMBER. ISNUMBER renvoie alors un tableau de valeurs TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Si nous avons même un VRAI dans le tableau, nous savons qu'une cellule contient au moins une des chaînes que nous recherchons. La façon la plus simple de vérifier TRUE est d'ajouter toutes les valeurs ensemble. Nous pouvons le faire avec SUMPRODUCT, mais nous devons d'abord forcer les valeurs TRUE / FALSE à 1s et 0s avec un double négatif (-) comme ceci:
--ISNUMBER(SEARCH(things,B5))
Cela donne un nouveau tableau contenant seulement 1 et 0:
(1;0;0;0;0)
livré directement à SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Avec un seul tableau à traiter, SUMPRODUCT ajoute les éléments du tableau et renvoie un résultat. Tout résultat différent de zéro signifie que nous avons un "hit", donc nous ajoutons> 0 pour forcer un résultat final VRAI ou FAUX:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Avec une liste codée en dur
Il n'est pas nécessaire d'utiliser une plage pour la liste des chaînes à rechercher. Vous pouvez également utiliser une constante de tableau. Par exemple, pour rechercher "rouge", "bleu" et "vert", vous pouvez utiliser une formule comme celle-ci:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Empêcher les fausses correspondances
Un problème avec cette approche est que vous pouvez obtenir de fausses correspondances à partir de 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 un piratage rapide, vous pouvez ajouter de l'espace autour des mots de recherche (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 avec une ponctuation.
Si vous avez besoin d'une solution plus précise, 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 utilisez la formule de cette page sur le texte résultant.
