9 mayo, 2021

COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

Hola a todos:

Hace unos días me preguntaban una fórmula para poder averiguar si una celda contiene un carácter o cadena específica. La respuesta es que existen varias y con distinta combinación de fórmulas.

Comenzamos con ejemplo para buscar, imaginad que queremos comprobar si en "Excel Signum" se encuentra la cadena de texto siguiente: "Sig".

Comenzamos con la fórmula más conocida y que encontraréis en todas las web y manuales. Donde con la función HALLAR buscamos "Sig" en el contenido de la celda y si lo encuentra nos devolverá un número com resultado de la posición. Si utilizamos la función ESNUMERO nos devolverá un verdadero o falso, que combinado con la función SI nos permitirá incluir un texto cuando encuentre el valor.

La función Hallar permite comodines y no distingue entre mayúsculas y minúsculas:

=SI(ESNUMERO(HALLAR("Sig";A1));"Contiene valor";"No lo contiene")

La siguiente fórmula (que es la que suelo usar), se compone de la combinación de CONTAR.SI y SI.

Cuando ejecutamos CONTAR.SI nos va a devolver el número de veces que cuenta el valor buscado, con la función SI indicamos que cuando el resultado sea verdadero (superior a 0) indique que contiene el valor buscado.

Esta fórmula permite comodines y en este caso son necesarios dado que para indicar que busque en todo el contenido de la celda debemos especificar los asteriscos al inicio y al final del valor, pero podríamos especificar que solo sea al inicio o al final. No distingue entre mayúsculas y minúsculas:

=SI(CONTAR.SI(A2;"*Sig*");"Contiene valor";"No lo contiene")

Otra posibilidad es una variante de la primera fórmula pero usando la función ENCONTRAR. Tiene la limitación de no permitir comodines y distingue entre mayúsculas y minúsculas:

=SI(ESNUMERO(ENCONTRAR("Sig";A5));"Contiene valor";"No lo contiene")

Por último, otra función que tampoco permite comodines y se debe respetar las mayúsculas y minúsculas, es la siguiente:

=SI(SUSTITUIR(A4;"Sig";"")<>A4=VERDADERO;"Contiene valor";"No lo contiene")

Aquí, lo que evaluamos es que cuando la fórmula sustituir sea verdadera, es cuando nos indicará que contiene el valor (dado que si lo sustituye es porque lo ha encontrado).

Este sería el resultado:

COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

Estoy seguro que existen más combinaciones para obtener el mismo resultado y con otras fórmulas, pero creo que estas cuatro os pueden servir en prácticamente todos los casos.

Y eso es todo, espero que os resulten de utilidad.

Descarga el archivo de ejemplo pulsando en: COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Comparte este post

69 comentario en “COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

  1. Hola,
    gracias por tan buena explicación.
    Te traslado un caso que me cuesta hacer funcionar con estas fórmulas que indicas.
    Quiero buscar un caracter "?" en dos una casilla que puede contener estos dos tipos de texto:

    https://dominio.es
    https://dominio.es/ca/?1=1

    Pero siempre me devuelve VERDADERO

    he probado

    =SI(CONTAR.SI(T1;"*?*");"Sí contiene ?";"No contiene ?")

    1. Claro, porque ? es un comodín en sí mismo, y la otra función con Hallar (le ocurre lo mismo con la interrogación.

      Para el caso de indicas, que es especial, debes usar las dos últimas:

      =SI(SUSTITUIR(A4;"?";"")A4=VERDADERO;"Contiene valor";"No lo contiene")

      =SI(ESNUMERO(ENCONTRAR("?";A5));"Contiene valor";"No lo contiene")

      Saludos.

      1. Hola Segu! Muchas gracias por tu rápida respuesta.
        Fenomenal. Probaré las dos opciones.
        Por mi parte seguí buscando y, finalmente, logre hacerlo funcionar poniendo antes del signo "?" una virgulilla "~" lo que hace que el comodín no se comporte como tal...
        Otra cosa que aprendí ayer... jeje.
        Gracias de nuevo.

  2. Hola. Tengo un alista con mas de 16.000 filas, y de esas debo filtrar las que contienen ciertas palabras (una lista de 21), no es facil filtarlo manualmente, pues las palabras pueden estar al principio o en la mitad de la celta. Sabes si existe alguna forma de hacer un buscar que busque no una palabra si no varias? Este es el ejemplo corto:
    La fuente:
    AMPLIFIER-MONITOR
    SPRING, HELICAL, COMPRESSION
    PUP JOINT, WELL DRILLING
    O-RING
    VALVE, AIR PRESSURE RELIEF
    ADAPTER VALVE
    SEAT, HELICAL COMPRESSION SPRING
    MODULE VALVE
    Quiero una formula para saber si en cada una de esas está la palabra "Valve, o Gasket, o así, 21 opciones), para reducir las 16.000 a una lista corta sobre la que tengo que hacer algo.
    Se reciben ideas!

    1. Lo normal sería hacerlo con una macro o con una UDF. Si lo quieres hacer con una fórmula, puedes usar el operador lógico 0 y usarlo en una de las funciones anteriores (ligeramente modificada):

      =SI(O(ESNUMERO(ENCONTRAR("VALVE";A1));ESNUMERO(ENCONTRAR("GASKET";A1));ESNUMERO(ENCONTRAR("HOLA";A1)));1;0)

      Ahora debes ir ampliando la fórmula por cada palabras hasta 21. Será larga, pero funcionará, cada vez que una de las palabras coincida en A1, marcará un 1 si no lo hace ninguna será un 0.

      Saludos

      1. Segu, hola, pero en mi caso no necestio que me salga " 1 " necesito que me salga el nombre: "Valve"; "Gasket", osea una respuesta corta de lo que encontró

  3. Buenas tardes,

    Estoy tratando de encontrar una formula que me indique si una celda contiene cierto texto (lista de varias fracciones de texto) y de ser así me inidique que corresponde a un "RECAUDO" de lo contrato a un "EGRESO"

    1. Hola Angélica: Eso lo debes hacer con la fórmula del comentario anterior: Por ejemplo: =SI(O(ESNUMERO(ENCONTRAR("EGRESO";A2));ESNUMERO(ENCONTRAR("REGRESO";A2));ESNUMERO(ENCONTRAR("OTRAS";A2)));"EGRESO";"RECAUDO").

  4. Hola, mi duda es si lo que va entre entre las comillas y los asteriscos puede ser una celda referenciada. Por ejemplo, si quiero contar/sumar/hallar las celdas que contengan la palabra "boli", meto en la fórmula "*boli*" y funciona. Sin embargo, si escribo en la celda B2 "boli" y en mi formula para contar/sumar/hallar las celdas que contengan la palabra "boli", meto "*B2*" y no funciona. ¿Sabes si es posible hacerlo de esta manera?

    1. Hola íñigo: si lo que deseas es incluir los asterisco en la fórmula, debes usar el ampersand y las comillas:

      =SI(CONTAR.SI(A2;"*" & B2 & "*");"Contiene valor";"No lo contiene")

      Saludos.

  5. Hola buenas tardes,

    Si, es muy funcional la formula sin embargo, necesito una formula que me diga si una celda contiene específicamente una lista de combinacion de palabras ejemplo:

    Lista:
    Cr Ach
    Cr Ach Banco
    Abono transferencia por Internet
    Abono por recaudo realizado en oficina
    Abono por recaudos con comprobante
    Abono dispersion pago a proveedores

    Si esta celda contiene: Alguna de la combinación de palabras mencionadas anteriormente me coloque "Recaudo" si no en " "

    Gracias!!

  6. Hola Angélica: Eres la misma persona a la que le respondí el 18 de febrero??. Si es así solo tienes que modificar la fórmula con los nuevos datos:

    De todas formas te dejo parte de la fórmula hecha, el resto lo puedes finalizar fácilmente, solo queda introducir "Abono dispersion pago a proveedores".

    Saludos.

    =SI(O(ESNUMERO(ENCONTRAR("Cr Ach";A1));ESNUMERO(ENCONTRAR("Cr Ach Banco";A1));ESNUMERO(ENCONTRAR("Abono transferencia por Internet";A1));ESNUMERO(ENCONTRAR("Abono por recaudo realizado en oficina";A1));ESNUMERO(ENCONTRAR("Abono por recaudos con comprobante";A1)));"RECAUDO";" ")

  7. Hola Segu, la primera fórmula no sirve si la palabra tiene tildes... ¿Hay alguna fórmula que no discrimine palabras con tildes? Quiero que me de positivo buscando "raul", tanto si en la celda en la que busco está escrito "Raul" o "raúl". Muchas gracias!!

  8. Hola Chicafísica:

    Para eso debes anidar la fórmula de esta manera:

    =SI(CONTAR.SI(A2;"Raul");"Contiene valor";SI(CONTAR.SI(A2;"raúl");"Contiene valor";"No lo contiene"))

  9. Hola,

    Tengo una duda, la voy a simplificar porque es mas compleja.

    Hay 2 columnas, columna AA y AC , ambas son formulas, donde si no encuentran lo que buscan salen "".

    Ej ( columna AA): =SI(BUSCARV(A134;DATA_IN_CAPA!$D$3:$CE$500;COLUMNA(DATA_IN_CAPA!BT:BT)-3;FALSO)="";"";BUSCARV(A134;DATA_IN_CAPA!$D$3:$CE$500;COLUMNA(DATA_IN_CAPA!BT:BT)-3;FALSO))

    Quiero contar cuando AA está vacía y AC llena o a la inversa. ( el problema es que "" lo entiende como texto, ya que función esblanco, no da afirmativo, en cambio contar.blanco SI, es raro, lo he leído en otros foros)

    He incluido esta formula, ya que me daba problemas en v0 cambio "" por ">0", esa parte la arregle pero la de "", no. He probado en vez de comillas ("") con la función largo con caracteres 0, o "0.

    Formula incluida: CONTAR.SI.CONJUNTO('CHEQUEO IMPORT-EXPORT CAPA'!AA4:AA500;">0";'CHEQUEO IMPORT-EXPORT CAPA'!AC4:AC500;"")+CONTAR.SI.CONJUNTO('CHEQUEO IMPORT-EXPORT CAPA'!AA4:AA500;"";'CHEQUEO IMPORT-EXPORT CAPA'!AC4:AC500;">0")

    No se si me he explicado bien, como podría subsanar esto? Igual es una tontería…..

    Muchas gracias de antemano

    1. Perdón, en este párrafo me refería a que cambio "" por ">0".

      "He incluido esta formula, ya que me daba problemas en v0 cambio "" por ">0", esa parte la arregle pero la de "", no. He probado en vez de comillas ("") con la función largo con caracteres 0 y otras cosas."

      Saludos

  10. Hola Ibon:

    Para analizar lo que indicas necesitaría que me enviases un archivo con la fórmula que estás utilizando y qué es lo que esperas conseguir. Saludos.

  11. Buenas tardes

    Me parecen muy buenos tus aportes, gracias por compartirlos, de igual manera me gustaria pedir de tu apoyo con un caso particular, tengo en una columna montos de un concepto, a su vez esta columna tiene sumas de cada capitulo por asi llamarlo, esta lista no esta anidada, y quiero encontrar una combinacion de funciones para poder sumar unicamente las sumas parciales

    Me puedes ayudar?

  12. Hola, muy buenos aportes.

    me ayudas con algo?

    necesito una función vb que busque un rango de celdas si tiene el caracter "." y que luego si no lo tiene que escriba el mismo texto con el caracter "." incluido

    luego de eso que busque si tiene dos caracteres del 0 al 9 luego del punto, si no lo tiene que le ponga 00 luego del caracter ".".

    te explico

    debo pasar a una celda de formato texto números y deben tener os decimales y los datos introducidos son en formato numero, lo utilizo para exportarlo a txt y poder subirlo a un sistema los datos.

    Gracias anticipadas

  13. Buenas noches, hay alguna manera que en la funcion HALLAR, se pueda colocar varios textos a buscar (un bloque), o se tiene que hacer uno por uno para cada texto a buscar, seria de mucha ayuda.

    muchas gracias

    1. Hola Victor:

      Con fórmulas únicamente lo podrás realizar con un si anidado o condicional y con el operador lógico "o". Otras soluciones requieren programación y desarrollo específico para tu caso concreto.

      Saludos

      1. Hola, se me ha borrado parte de la consulta. Lo que estaría necesitando es una formula que me traiga la palabra suppliers si A1 es menor a cero y en G1 contiene la palabra cheque o la palabra pago prov y que me traiga la palabra collections si A1 es mayor a cero y en G1 contiene la palabra deposito o la palabra transferencia. Por otra parte que me traiga la palabra Taxes si A1 es menor a cero y en G1 contiene la palabra IVA o la palabra percepción. Sería esto posible?

        1. Hola María:

          Sería así: =SI(Y(A1<0;O(G1="cheque";G1="pago prov";));"collections";SI(Y(A1>0;O(G1="deposito";G1="transferencia";));"indica palabra";SI(Y(A1<0;O(G1="IVA";G1="percepcion";));"Taxes")))

          Pero no has definido qué palabra quieres que te traiga en la segunda condición. Este tipo de ejercicios se realiza con condicionales anidados.

          Saludos

          1. Muchas gracias.Solo tengo un tema mas, con esta formula mencionada entiendo que G1 debería ser igual a pago prov, pero el tema es que necesitaría que contenga la palabra pago proveedores ya que también tiene otras palabras adicionales que no tienen nada en común

            G1 : NMSCNONREF//2020073002492938/LTC/2377-PAGO PROVEEDORES INTERBANKING EXTE/HTC/MSC/AIN/

            G2: NMSCNONREF//2020073002629151/LTC/2377-PAGO PROVEEDORES INTERBANKING EXTE/HTC/MSC/AIN/

            Mil gracias por la ayudaaaaa

          2. lo que necesitaría es que cada vez q reference contenga echeq y en importe sea mayor a "0" en reclasificacion arroje Collection y si es menor a cero arroje suppliers. Si reference contiene la palabra deposito y en importe el valor es menor a cero que aparezca suppliers y si es mayor a cero que aparezca collection, si reference contiene la palabra IVA /percepcion que en reclasificacion arroje taxes

            Reference Importe Reclasificacion
            NMSC1NONREF//2020073000000000/LTC/3036-DEPOSITO E- 391.584,19 collections
            NMSC1NONREF//2020073000000000/LTC/3036-DEPOSITO E- -20.000,00 suppliers
            NMSC2084157483//2020073084157483/LTC/3043-ECHEQ CL -2299 suppliers
            NMSC2084157483//2020073084157483/LTC/3043-ECHEQ CL 3000 collections
            NMSC10NONREF//2020073000000000/LTC/1923-PERCEPCION -28,66 taxes
            NMSC11NONREF//2020073000000000/LTC/3254-IVA TASA G -601,94 taxes
            NMSC14NONREF//2020073000000000/LTC/1923-PERCEPCION -32,3 taxes

            espero haber sido mas clara

          3. Hola María:

            Creo que con la respuesta que te dí en la consulta anterior puedes resolver perfectamente la consulta actual. Inténtalo, es la mejor forma de aprender. Saludos

          4. muchas gracias lo probé pero solo funciona si en G1 dice solo cheque, transferen,etc. El tema es que como tiene otros datos no me lo toma .

            G1: NMSC36NONREF//2020073002490308/LTC/3002-TRANSFEREN

            muchas gracias igual por el tiempo

  14. Tengo que realizar una practica pero necesito una formula en donde no me cuente el promedio.

    si tiene 10 es acreedor a la beca del 100%
    si tiene menos de 10 y mas de 9 tiene la beca del 75%
    si tiene mas de 8 y menos de 9 tiene la beca del 50%
    Si tiene menos de 8 " NO TIENE BECA"
    si no hay matricula "NO HAY MATRICULA" (EN ESTA PARTE AL MOMENTO DE COLOCAR QUE NO HAY MATRICULA, ME CUENTA EL PROMEDIO Y POR LO TANTO NO ME MANDA EL MENSAJE DE QUE NO HAY MATRICULA, SI NO EL DE LA BECA QUE LE CORRESPONDE)
    QUE PUEDO HACER..

  15. buenas tardes, requiero una formula no se si uds la sepan ,,,, la regla me dice> no debe permitir mas de 2 caracteres especiales iguales consecutivos en excell... pero encontre esta y no se como adaptarla a lo que me dice =SI(CONTAR.SI(R2;"*#*");"Contiene valor";"No lo contiene")
    los caracteres son #.-'º/

    1. Hola Andrés:

      Para hacer lo que indicas es necesario utilizar programación, no se puede realizar con fórmulas, en estos momentos tengo varias consultas a las que responder, cuando termine trataré de buscar una solución. Saludos

  16. Hola, tengo una hoja excel con 3 columnas (Nombre, Recurso, Status) obtenidas de otro libro "=Nombre!N2:N50"; "=Recurso!K2:K50" ; "=Status!K2:K50". El nombre se puede repetir varias veces pero con diferente valor en la columna Recurso.

    Nombre | Recurso| Status
    Juan Perez | 0.5 | confirmado
    Rosa Flores | 0.2 | confirmado
    Juan Perez | 0.5 | liberado
    Juan Perez | 0.3 | confirmado
    Maria Diaz | 0.5 | confirmado
    Juan Perez | 0.8 | liberado 2019
    Juan Perez | 0.8 | 2020 liberado

    Necesito tener un reporte que agrupe el Nombre que se dupliquen y sumar los recursos siempre y cuando en status NO contenga la palabra liberado, de tal manera de tener el siguiente resultado:

    Juan Perez 0.8
    Rosa Flores 0.2
    Maria Diaz 0.5

    he intentado con lo siguiente colocando en la celda E3 la siguiente formula =IF(ISBLANK(E3);"";SUM(FILTER($B$2:B;$A$2:A=E3))), pero me falta incluir la condición para solo sumen si la columna Status (C) no contiene la palabra "liberado"

    Muchas gracias por tu ayuda.

    1. Hola Delsy:

      Envíame esa fórmula en un archivo para verificar los datos. La he probado y me genera un error y quiero verificar que la estás colocando correctamente.

      Saludos

  17. Buenas noches, los he leído y he visto que saben bastante de esto por lo que quería aprovechar de que me ayuden con algo: Quiero sumar una columna siempre y cuandoc otra columna tenga una letra en especifico... que formadla debería utilizar?? ayuda amigos y muchas gracias de antemano

  18. Hola Segu,

    Antes de nada agradecerte tu post, ha sido muy instructivo.

    Mi duda es la siguiente (no sé si será factible).

    Quisiera hallar diferentes palabras en un listado de palabras y agruparlar y que el resultado sea para más de 2 opciones, por ejemplo; Lapices, Rotuladores, Cuadernos y Gomas.

    Según tu ejemplo lo he podido hacer para diferenciar entre 2 categorías pero no consigo ni sé cómo hacer para agruparlar en más.

    La que quiero aplicar en fórmula es "Si encuentras lápices en el texto devuelveme la categoría "lapices", si encuentras "rotuladores" devuelveme la categoría "rotuladores"..y así sucesivamente.

    Gracias y un saludo,

    1. Supongo que es esto lo que necesitas, una fórmula anidada, la frase está en A3:

      =SI(CONTAR.SI(A3;"*Lapices*");"Lapices";SI(CONTAR.SI(A3;"*Rotuladores*");"Rotuladores";SI(CONTAR.SI(A3;"*Cuadernos*");"Cuadernos";SI(CONTAR.SI(A3;"*Gomas*");"Gomas"))))

      Saludos

  19. tENGO UNA BASE DE DATOS CON 45MIL REGISTROS Y NECESITO NORMALIZAR LA INFORMACIÓN, REQUIERO BUSCAR EN UNA LISTA SI CONTIENE UNA PALABRA TODA LA INFROMACIÓN A NORMALIZAR ESTA EN LA MISMA COLUMNA Y DEBO Y HACER QUE ME ASIGNE UN NUMERO A CADA OPCIÓN, ES DECIR, SI Axx CONTIENE REPARTO, ASIGNE 1, SI Axx CONTIENE LECTURA, ASIGNE 2, SI CONTIENE COVE ASIGNE 3, Y ASÍ SUSCESIVAMENTE HASTA NORMALIZAR CON LAS 21 OPCIONES, ME PUEDEN AYUDAR, MIL GRACIAS

    1. Hola Vilma, tienes ejemplos de si condicional en los comentarios que te debería servir perfectamente.

      No obstante, si ves que no lo consigues, puedes enviarme un archivo de ejemplo detallando lo que necesitas.

      Saludos

  20. Hola que tal, tengo una lista de de items pero solo quiero en una celda aparte que me busque las palabras: master, cajeta y fundas, solo eso necesito, osea de todos esos item que solo me busque los items que tienen master y que solo en una celada aparte me diga " master "

    1. Por ejemplo así:

      =SI(CONTAR.SI(A2;"master");"master";SI(CONTAR.SI(A2;"cajeta");"cajeta";"No lo contiene"))

      Pero únicamente devolverá un resultado, el primero que encuentre.

  21. Hola! Aplico la 2da formula, sin embargo, necesito que las palabras que si coinciden me las muestre en un listado, cómo podría realizarse? He intentado con buscarv, con hallar, y no he podido lograr lo que quiero. Necesito que al escribir parte de la palabra en una celda pueda mostrarme todas las coincidencias del listado.
    Muchas gracias, saludos.

    1. Para hacer lo que indicas se tendría que programar, no existe una función nativa que permita hacerlo directamente. Si tengo tiempo intento programarlo. no obstante es posible que alguien lo haya desarrollado, así que te recomiendo que busques en la red mientras tanto.

      Saludos.

  22. Hola, muchas gracias por la información, quiero usar la fórmula "contar si" con un buscar v anidada, sabes por qué no funciona y que opciones tengo? Saludos!

  23. Hola!. Buscando una formula encontre excelentes explicaciones en este post..mu agradecida!....Tengo una consulta que no logro encontrar por aquí. Tengo una columna encabezado con los meses del año, y en filas voy indicando con un texto "Cierre de Proyecto" cuando cada proyecto finaliza. Necesito una formula que en una columna final me arroje el mes en que ese proyecto cierra. He intentado con buscarh, introduciendo el texto a buscar y el indicador de fila donde tiene que encontrar el valor pero no he podido.

    Agradezco mucho su ayuda!!

  24. Hola buenas tardes, te hablo desde
    Tengo lo siguiente, quiero que una formula encuentre un texto en especifico y me informe si cumple o no cumple.
    =SI(O(O28="";H28="Nil Cargo");"";SI(O28<=H28;"CUMPLE";"NO CUMPLE"))
    Pero quiero que me busque un texto ejemplo (OK;OFFLOAD;PENDIENTE)
    y otra Formula que si se cumple la misma hora actual (Ahora()) automáticamente me informe si cumple o no cumple.

    1. Tienes varios ejemplos contestados en los comentarios:

      Por ejemplo:

      Si tienes en A1 alguna de las palabras te lo mostrará.

      =SI(O(ESNUMERO(ENCONTRAR("OK";A1));ESNUMERO(ENCONTRAR("OFFLOAD";A1));ESNUMERO(ENCONTRAR("PENDIENTE";A1)));"CUMPLE";"NO CUMPLE")

      Saludls

Si te ha gustado o tienes alguna duda, puedes dejar aquí tu comentario.

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies

ACEPTAR
Aviso de cookies