Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Resumen
Para resaltar las celdas que contienen una de las muchas cadenas de texto, puede utilizar una fórmula basada en las funciones ESNÚMERO y BÚSQUEDA, junto con la función SUMPRODUCTO. En el ejemplo que se muestra, el formato condicional aplicado a B4: B11 se basa en esta fórmula:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Explicación
Trabajando desde adentro hacia afuera, esta parte de la fórmula busca en cada celda en B4: B11 todos los valores en el rango llamado "cosas":
--ISNUMBER(SEARCH(things,B4)
La función BUSCAR devuelve la posición del valor si se encuentra y el error #VALOR si no se encuentra. Para B4, los resultados vuelven en una matriz como esta:
(8;#VALUE!;#VALUE!)
La función ISNUMBER cambia todos los resultados a VERDADERO o FALSO:
(TRUE;FALSE;FALSE)
El doble negativo delante de ISNUMBER fuerza VERDADERO / FALSO a 1/0:
(1;0;0)
La función SUMPRODUCTO luego suma los resultados, que se prueban contra cero:
=SUMPRODUCT((1;0;0))>0
Cualquier resultado distinto de cero significa que se encontró al menos un valor, por lo que la fórmula devuelve VERDADERO, lo que activa la regla.
Ignora las cosas vacías
Para ignorar las celdas vacías en el rango llamado "cosas", puede probar una fórmula modificada como esta:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Esto funciona siempre que los valores de texto que está probando no contengan la cadena "FALSE". Si es así, puede ampliar la función SI para incluir un valor si se sabe que no aparece falso en el texto (es decir, "zzzz", "####", etc.)
Opción sensible a mayúsculas y minúsculas
La búsqueda no distingue entre mayúsculas y minúsculas. Para comprobar el caso también, reemplace SEARCH con FIND de esta manera:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Evitar coincidencias falsas
Un problema con este enfoque es que puede ver coincidencias falsas causadas por 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 realiza automáticamente una coincidencia "contiene".
Para una solución parcial, 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 al lado de la puntuación. Esto se puede abordar parcialmente agregando espacio también alrededor del texto original. Para agregar espacio al inicio y al final de ambos al mismo tiempo, puede probar una fórmula como esta:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Sin embargo, esto no solucionará los problemas causados por la puntuación.
Si necesita una solución más completa, una opción es normalizar el texto primero en una columna auxiliar, teniendo cuidado de agregar también un espacio inicial y final. Luego, puede buscar palabras completas rodeadas de espacios.
