Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Resumen
Para probar una celda y ver si contiene una de las muchas cadenas, puede usar una fórmula basada en las funciones SEARCH, ISNUMBER y SUMPRODUCT. La fórmula en C5, copiada, es:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
donde cosas es el rango con nombre E5: E9.
Explicación
Queremos probar cada celda en B5: B11 para ver si contiene alguna de las cadenas en las cosas del rango con nombre (E5: E9). La fórmula que estamos usando en C5, copiada, es:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Esta fórmula se basa en una fórmula (explicada aquí) que busca una sola subcadena en una celda. Si la celda contiene la subcadena, la fórmula devuelve VERDADERO. Si no, la fórmula devuelve FALSO:
ISNUMBER(SEARCH(things,B5))
Sin embargo, en este caso, le damos a SEARCH una lista de cadenas. Dado que hay 5 cadenas en las cosas , SEARCH devuelve 5 resultados en una matriz como esta:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Cuando SEARCH encuentra una cadena, devuelve la posición de esa cadena. Si SEARCH no encuentra una cadena, devuelve un #VALUE! error. Debido a que "amarillo" aparece como la primera palabra en B5, vemos un 1. Dado que no se encuentran las otras cadenas, los otros 4 elementos son errores.
Esta matriz se devuelve directamente a la función ISNUMBER. ISNUMBER luego devuelve una matriz de valores VERDADERO / FALSO:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Si tenemos incluso un VERDADERO en la matriz, sabemos que una celda contiene al menos una de las cadenas que estamos buscando. La forma más fácil de verificar si es VERDADERO es sumar todos los valores. Podemos hacer eso con SUMPRODUCT, pero primero necesitamos forzar los valores TRUE / FALSE a 1s y 0s con un doble negativo (-) como este:
--ISNUMBER(SEARCH(things,B5))
Esto produce una nueva matriz que contiene solo 1 y 0:
(1;0;0;0;0)
entregado directamente a SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Con solo una matriz para procesar, SUMPRODUCT agrega los elementos de la matriz y devuelve un resultado. Cualquier resultado distinto de cero significa que tenemos un "acierto", por lo que agregamos> 0 para forzar un resultado final de VERDADERO o FALSO:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Con una lista codificada
No es necesario utilizar un rango para buscar la lista de cadenas. También puede utilizar una constante de matriz. Por ejemplo, para buscar "rojo", "azul" y "verde", puede usar una fórmula como esta:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Evitar coincidencias falsas
Un problema con este enfoque es que puede obtener coincidencias falsas de subcadenas que aparecen dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir "dr", también puede encontrar "Andrea", "beber", "seco", etc., ya que "dr" aparece dentro de estas palabras. Esto sucede porque SEARCH automáticamente hace una coincidencia "contiene".
Para un truco rápido, puede agregar espacio alrededor de las palabras de búsqueda (es decir, "dr" o "dr") para evitar encontrar "dr" en otra palabra. Pero esto fallará si "dr" aparece primero o último en una celda, o aparece con puntuación.
Si necesita una solución más precisa, una opción es normalizar el texto primero en una columna auxiliar, teniendo cuidado de agregar también un espacio inicial y final. Luego, usa la fórmula de esta página en el texto resultante.
