Clases Excel
Lunes, 26 Mayo 2014 03:01

Cómo identificar las celdas que contienen un texto específico

Valora este artículo
(18 votos)

En ocasiones podemos necesitar que una fórmula nos diga si una celda contiene un texto específico dentro de sí y o si no lo contiene. No hay ninguna función de Excel que directamente haga esto. Pero podemos simular su efecto mediante la combinación de otras funciones. ¿Cuál es la forma más fácil de hacerlo?

Identificar celdas con textos específicos 1

 

Nos referimos aquí al caso en que buscamos un texto dentro de otro texto mayor. Por ejemplo, en un listado de camisetas de diferente talle y color, ¿cómo podemos identificar las celdas que contienen la palabra "ROJA" ? Hay varias formas de lograr esto, pero vamos a explicar la más sencilla, que involucra las funciones HALLAR, ESERROR y NO.

 

Comencemos hablando de la función HALLAR, que es la que resuelve el centro del problema.

 

 

La función HALLAR

La función HALLAR busca un texto dentro de otro. Si lo encuentra, devuelve la posición donde comienza el texto buscado. Si no lo encuentra, devuelve el mensaje de error #¡VALOR!

 

Su sintaxis es la siguiente:

HALLAR(texto buscado; celda en la que se busca)

Identificar celdas con textos específicos 2

 

 

Este es un modo simple de averiguar si el texto buscado se encuentra o no dentro de otro. Pero la información retornada por la función (ya sea un número o un mensaje de error) no es la más adecuada.

 

Debemos entonces interceptar estos resultados y cambiarlos por otros, como, por ejemplo, VERDADERO y FALSO. Aquí entra en juego la función ESERROR.

 

 

La función ESERROR

La función ESERROR es especialmente útil para saber si una fórmula está dando como resultado un mensaje de error. Si es así, devuelve VERDADERO, de lo contrario devuelve FALSO:

ESERROR(fórmula)

Identificar celdas con textos específicos 3

 

 

Por lo tanto, es buena idea combinar ambas fórmulas y evaluar la función HALLAR con la función ESERROR. Esta fórmula anidada nos retornará VERDADERO si el texto buscado no se encuentra en la celda y FALSO si se encuentra.

Identificar celdas con textos específicos 4

 

 

Si no nos hemos mareado demasiado a esta altura, nos daremos cuenta de que el resultado que buscamos es exactamente el opuesto al que retorna la fórmula. En este momento es que recurrimos a la función NO.

 

 

La función NO

La función NO acepta como único argumento una expresión lógica y devuelve la opuesta: si el argumento es VERDADERO retorna FALSO y viceversa.

NO(expresión lógica*)

*Casi siempre la expresión lógica es el resultado de una fórmula.

 

Colocando como argumento de la función NO la fórmula que teníamos, obtendremos el resultado correcto.

=NO(ESERROR(HALLAR(texto buscado; celda en la que se busca)))

 

La combinación de las funciones HALLAR, SIERROR y NO, que hemos efectuado, devuelve el valor lógico VERDADERO si la celda indicada contiene el texto especificado; de lo contrario, devuelve FALSO.

Identificar celdas con textos específicos 5

 

 

Diferenciar mayúsculas de minúsculas

Una particularidad de la función HALLAR es que, al buscar un texto dentro de otro, no hace distinción entre mayúsculas y minúsculas. Si queremos que la búsqueda sea más exacta y que se respeten las mayúsculas y minúsculas, debemos sustituir la función HALLAR por la función ENCONTRAR.

=NO(ESERROR(ENCONTRAR(texto buscado; celda en la que se busca)))

Identificar celdas con textos específicos 6

 

 

Personalizar la respuesta

Otra opción a la fórmula que desarrollamos en este artículo, es:

=SI(ESNUMERO(HALLAR(texto buscado; celda en la que se busca));"Correcto"; "Incorrecto")

 

La ventaja de esta fórmula, aunque más compleja, es que permite personalizar el mensaje devuelto. En el ejemplo los mensajes retornados son los que aparecen en verde: “Correcto” e “Incorrecto”; podemos cambiarlos por los que queramos.

Identificar celdas con textos específicos 7

 

 

Como ves, aún en los casos en que no disponemos de una función específica para llevar a cabo una tarea, podemos usar la combinación de varias de ellas para obtener el resultado esperado.

 

Temas como estos son los que abarcamos en nuestros cursos personalizados. Puedes obtener más información sobre ellos aquí.

 

Visto 93377 veces Modificado por última vez en Lunes, 11 Abril 2016 03:32

Deja un comentario

Los campos imprescindibles están marcados con asterisco (*). Tu dirección de correo nunca será publicada.

69 comentarios

  • Enlace al Comentario Clases Excel Domingo, 30 Abril 2017 00:56 publicado por Clases Excel

    Hola, Cabetxo.

    Podrías usar la función BUSCARV. Te paso el enlace a una página de Office donde se explica cómo usar esta función en detalle:
    https://support.office.com/es-es/article/Funci%C3%B3n-BUSCARV-0bbc8083-26fe-4963-8ab8-93a18ad188a1

    Copia y pega el enlace con la dirección url en el navegador, porque si haces clic en él te da error. Espero te sirva.
    Saludos

  • Enlace al Comentario Cabetxo Sábado, 29 Abril 2017 20:45 publicado por Cabetxo

    Muy buenas,
    Existen dos archivos, uno escrito por un usuario y otro con un formato determinado. Lo que quiero es que realice una búsqueda en el primero de los archivos y cuando localice el criterio aplicado en la búsqueda traslade los datos que corresponden a este criterio al segundo de los archivos. El primero aunque lo realice un usuario, debe guardar cierta estructura para poder trabajar.
    Una vez localizados los datos, en el segundo archivo debe colocarlo en la línea correspondiente.
    Si en el primero encuentra ‘pasivo neto’ y tres valores en las celdas A11, B11, C11 y D11, quiero que los lleve al segundo archivo y los coloque en las celdas C4, D4, E4, F4, porque previamente sabemos que en la celda C4 debe ir la cadena de caracteres ‘pasivo neto’, porque ya está escrita.
    Un saludo

  • Enlace al Comentario Clases Excel Jueves, 27 Abril 2017 23:33 publicado por Clases Excel

    Hola, cabetxo
    No entiendo bien cuál es tu consulta. Te agradezco si pudieras ampliarla un poco.
    Saludos

  • Enlace al Comentario cabetxo Martes, 25 Abril 2017 18:51 publicado por cabetxo

    Hola buenas,
    tengo una columna con diferentes nombres y en otra columna quiero ir poniendo esos mismos nombres pero con un orden estipulado, de forma que si en la segunda quiero que vaya 'juan', busque en la primera columna todas las celdas hasta encontrarlo.
    Muchas gracias

  • Enlace al Comentario Clases Excel Lunes, 24 Abril 2017 21:27 publicado por Clases Excel

    Hola, Joe78man.

    Cellhasformula parece ser una función del tipo UDF ("función definida por el usuario"); es decir, no es una función nativa de Excel. De hecho, buscando en la web encontré el código VBA de una función UDF llamada precisamente CellHasFormula: https://excel.tips.net/T002766_Coloring_Cells_with_Formulas.html

    No obstante, para detectar si una celda contiene una fórmula, existe una función nativa de Excel que en español se llama ESFORMULA y que como argumento lleva solamente la celda que se quiere evaluar. Su resultado es un valor lógico (VERDADERO o FALSO).

    Podrías usar esa función para establecer un formato condicional.

    La función NOT (en español se llama NO) simplemente devuelve el valor lógico contrario al que se coloca como argumento.

    Por ejemplo:
    =ESFORMULA(A1)
    devolverá VERDADERO si en A1 hay una fórmula.

    En cambio:
    =NO(ESFORMULA(A1))
    devolverá VERDADERO si en A1 no hay una fórmula.

    Espero haberte ayudado. Saludos.

  • Enlace al Comentario Joe78man Lunes, 24 Abril 2017 20:52 publicado por Joe78man

    Buenas. Estoy buscando poner un condicional para cuando una celda tiene formula o texto. (es un formulario con formulas que se puede sobre escribir, y necesito destacar lo que es un dato duro o lo trae una formula).
    En la version en ingles hay una formula y es =NOT(Cellhasformula), pero no encuentro la misma en version español.
    Estoy interesado en buscar una forma evitando usar VB. Gracias

  • Enlace al Comentario Clases Excel Martes, 20 Diciembre 2016 20:15 publicado por Clases Excel

    Hola, Orlando.
    Estuve pensando una solución a la situación que describes, pero no encontré ninguna que fuera lo suficientemente sencilla como para poder explicarla en este pequeño espacio. Además, pese a tu buena descripción, al no ver la planilla me surgen algunas dudas. Por ejemplo: ¿los meses se detallan por su número o por su nombre? ¿puede haber períodos cortados o son siempre continuos?, etc. De esos y otros detalles depende que se pueda resolver de una u otra forma. Aún así, creo que lo mejor sería resolverlo por medio de una macro.
    ¡Saludos!

  • Enlace al Comentario ORLANDO LINO Miércoles, 14 Diciembre 2016 00:03 publicado por ORLANDO LINO

    Hola, tengo un archivo (Vacaciones de trabajadores), el archivo tiene el siguiente formato; en las primeras filas se detallan los días y mes (Ejm Columna/Fila C3 = 01, D3 = 02, E3 = 03 y la fila 2 esta combinada desde C2:AG2 detallando allí el mes, y así sucesivamente); las vacaciones están descritas con un solo digito (A--Z/1-9) dentro de cada celda (En las primera columna esta el nombre del trabajador/DNI). Un ejemplo: el trabajador Orlando / 1234567 (A4 / B4) tiene vacaciones del 03ENE - 10ENE, los campos de las celdas desde E4:L4 están llenos con dígito "1", de esa manera se visualizan las vacaciones en el archivo. De que manera (Formula/Macro) puedo obtener los valores de las vacaciones en fechas; quiero decir que el resultado que deseo obtener es, que día inician las vacaciones y que día terminan (03/01/2016 - 10/01/2016). De antemano muchas Gracias por tu apoyo!!!

  • Enlace al Comentario Mansef Viernes, 07 Octubre 2016 15:26 publicado por Mansef

    Muy bueno

  • Enlace al Comentario Clases Excel Domingo, 18 Septiembre 2016 06:32 publicado por Clases Excel

    Hola, Seni.

    La fórmula tendría que ser:
    =AÑO(A1)=2016

    Saludos

  • Enlace al Comentario seni abella Viernes, 16 Septiembre 2016 02:59 publicado por seni abella

    Hola que tal quiero hacerle una consulta tengo en una columna(digamosle A) escrito la fecha y hora por ejemplo 12/09/2016 19:30:35 y quiero que en la columna C se ponga de cierto color.
    Se que tengo que hacer una nueva regla de formado condicional por ejemplo =Y(A1=”2016″). Pero con esa nueva regla que hice lo que me hace es me pone C en color si solo esta la palabra 2016 y no tiene encuenta si pertence, solo se cumple si esta esta palabra sola.
    Agredezco la ayuda ya que lo necesito para una presentacion. Saludos

  • Enlace al Comentario Clases Excel Miércoles, 27 Julio 2016 07:42 publicado por Clases Excel

    Hola, Carlos.
    Podrías usar la fórmula mencionada en el artículo. Suponiendo que la lista empiece en A1 y siga hasta A50, podrías escribir algo así en alguna columna de la fila 1:

    =SI(ESNUMERO(HALLAR("LUIS"; A1));"Encontrado"; "")

    Luego debes arrastrar la fórmula hasta A50.
    Saludos

  • Enlace al Comentario Carlos Rojas Lunes, 25 Julio 2016 21:20 publicado por Carlos Rojas

    Estimado tal vez me puedas apoyar, tengo una lista y dentro de esa lista tengo a varios que se llaman LUIS, quiero hacer una busqueda de todos los luis que haya en la lista..
    Gracias

  • Enlace al Comentario Clases Excel Martes, 28 Junio 2016 17:56 publicado por Clases Excel

    Hola, Dai

    No comprendo totalmente el problema que se te presenta. Pero te puedo sugerir dos funciones que tal vez te ayuden:

    La función DIASEM devuelve el día de la semana en formato numérico. Su sintaxis más sencilla es:
    =DIASEM(fecha)

    Devolverá un número entre 1 y 7. El 1 corresponde al domingo y así sucesivamente.

    Con la función ELEGIR puedes sustituir el número del día por su nombre:
    =ELEGIR(DIASEM(fecha);"domingo";"lunes";"martes";"miércoles";"jueves";"viernes";"sábado")

    Espero te sirva.

  • Enlace al Comentario Dai Lunes, 27 Junio 2016 19:21 publicado por Dai

    Hola!
    Buenas tardes
    Quisiera consultarle si existe alguna formula para evaluar la siguiente planilla:
    En A1 tengo una fecha con el sig formato (DD/MM/AAAA HH:MM)
    En A2 tengo una referencia a la celda A1 pero la visualizó con el formato Personalizado (dddd), el cual me arroja como resultado el día de la semana correspondiente.
    En A3 tengo una formula con la condición "=SI(A1="LUNES";"EL DIA ES LUNES"; SI(A1="MARTES"; EL DIA ES MARTES"; etc....)
    Mi problema es que el resultado de la referencia de A2 es un numero que determina el dia de la semana exacto en un mes año y hora y minuto determinado, el cual varia con el correr de los días.
    Existe una formula para que el Lunes no sea un numero y si una cadena de texto que diga Lunes??

    Desde ya agradezco mucho las molestias, aguardo rta...

    un gentil saludos

  • Enlace al Comentario Clases Excel Jueves, 09 Junio 2016 02:32 publicado por Clases Excel

    Hola, Andrés. No encontré todavía ninguna forma sencilla de resolver tu consulta. Si logro dar con una te lo comunico.
    Saludos

  • Enlace al Comentario Andres Villa Miércoles, 08 Junio 2016 16:33 publicado por Andres Villa

    Hola, buen día... su ayuda por favor.

    Tengo dos columnas, en la columna 1 tengo varias celdas con varias frases al azar, y en la columna 2 un catalogo de palabras únicas y distintas.

    Deseo identificar en cualquier celda de la columna 1, identifico cualquier palabra del catalogo de la columna 2

    Esto con la finalidad de validar que en la cualquier celda de la columna 1 tengo una posible palabra del catalogo de mi columna 2.

    Existe alguna celda que me permita hacer esto al no tener un celda especifica? por el contrario un rango de celdas a buscar?

    Les agradezco mucho su orientación.

  • Enlace al Comentario Clases Excel Jueves, 14 Abril 2016 20:03 publicado por Clases Excel

    Muchas gracias, eDaniel. Me alegra tu comentario.
    Saludos

  • Enlace al Comentario eDaniel Jueves, 14 Abril 2016 19:44 publicado por eDaniel

    Su solución me sirvió con un problema similar que tenía. Muchas gracias y felicidades por su buen tutorial.

  • Enlace al Comentario Clases Excel Viernes, 01 Abril 2016 19:36 publicado por Clases Excel

    Hola, Hugo.
    Me alegra que te haya gustado el artículo.

    Para evaluar varias condiciones en la misma fórmula tendrías que usar lo que se conoce como SI anidados, es decir, un SI dentro de otro SI, que te permita evaluar más de una condición.

    Otra opción, dependiendo de tu objetivo, podría ser, en lugar de escribir el texto buscado directamente en la fórmula, escribirlo en otra celda y en la fórmula solo hacer referencia a dicha celda. De este modo, cuando cambies el texto de la celda referenciada, la fórmula buscará ese nuevo texto.

    Espero haberme explicado bien.

    Saludos

Suscríbete a nuestro Boletín gratuito

Recibe periódicamente en tu email los últimos artículos, trucos, tutoriales y otras novedades de Excel.


Tus datos son confidenciales, nunca serán compartidos con nadie.

Inicio    Contacto    Términos y condiciones    Mapa del sitio

Ir arriba

Desarrollado por www.espectradesign.com