EXCEL: La celda contiene una de muchas cosas

Para probar una celda para ver si contiene una de las muchas cadenas, puede usar una fórmula basada en las funciones HALLAR, ESNUMERO y SUMAPRODUCTO. La fórmula en C5, copiada, es:

=SUMAPRODUCTO(--ESNUMERO(HALLAR(things,B5)))>0

donde las cosas es el rango nombrado E5: E9.

Cómo funciona esta fórmula

Queremos probar cada celda en B5: B11 para ver si contiene alguna de las cadenas en las cosas de rango con nombre (E5: E9). La fórmula que estamos usando en C5, copiada, es:

=SUMAPRODUCTO(--ESNUMERO(HALLAR(things,B5)))>0

Esta fórmula basa una fórmula (explicada aquí) que verifica una celda para una sola subcadena. Si la celda contiene la subcadena, la fórmula devuelve VERDADERO. Si no, la fórmula devuelve FALSO:

ESNUMERO(HALLAR(things,B5))

Sin embargo, en este caso, le damos a HALLAR una lista de cadenas. Como hay 5 cadenas en las cosas, HALLAR devuelve 5 resultados en una matriz como esta:

{1;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Cuando HALLAR encuentra una cadena, devuelve la posición de esa cadena. Si HALLAR no encuentra una cadena, ¡devuelve un #VALOR! error. Debido a que "amarillo" aparece como la primera palabra en B5, vemos un 1. Debido a que no se encuentran las otras cadenas, los otros 4 elementos son errores.

Esta matriz se devuelve directamente a la función ESNUMERO. NUMBER luego devuelve una matriz de valores VERDADERO / FALSO:

{VERDADERO;FALSO;FALSO;FALSO;FALSO}

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 VERDADERO es agregar todos los valores juntos. Podemos hacer eso con SUMAPRODUCTO, pero primero necesitamos forzar los valores VERDADERO / FALSO a 1s y 0s con un doble negativo (-) como este:

--ESNUMERO(HALLAR(things,B5))

Esto produce una nueva matriz que contiene solo 1s y 0s:

{1;0;0;0;0}

entregado directamente a SUMAPRODUCTO:

=SUMAPRODUCTO({1;0;0;0;0})

Con solo una matriz para procesar, SUMAPRODUCTO agrega los elementos en la matriz y devuelve un resultado. Cualquier resultado distinto de cero significa que tenemos un "hit", por lo que agregamos> 0 para forzar un resultado final de VERDADERO o FALSO:

=SUMAPRODUCTO({1;0;0;0;0})>0 // returns TRUE

Con una lista codificada

No es necesario usar un rango para la lista de cadenas a buscar. También puede usar una constante de matriz. Por ejemplo, para verificar "rojo", "azul" y "verde", puede usar una fórmula como esta:

=SUMAPRODUCTO(--ESNUMERO(HALLAR({"red","blue","green"},B5)))>0

Prevenir falsas coincidencias

Un problema con este enfoque es que puede obtener coincidencias falsas de las subcadenas que aparecen dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir "dr", también puede encontrar "Andrea", "drink", "dry", etc., ya que "dr" aparece dentro de estas palabras. Esto sucede porque HALLAR 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 atrapar "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.