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:
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.
¡¡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
David
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 ?")
Segu
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.
David
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.
Segu
Hola David!. Muchas gracias por contestar y dar la solución!. Un abrazo!
Tulio Leon
Muchas gracias!! muy útil !!
Patricia
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!
Segu
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
Angelica Maria Lopez
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"
Segu
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").
MARV
EXCELENTE
jaime
no tengo palabras para agradecer lo mucho que me has ayudado con este artículo! a mi me ha sido útil la de hallar
Segu
Muchas gracias Jaime. Me alegra que te haya sido de utilidad. Saludos.
Iñigo
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?
Segu
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.
Ivan
Gracias, a mí me ha servido para mi proyecto.
Segu
OK, perfecto
Angelica Maria Lopez cubides
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!!
Segu
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";" ")
monica
tengo una base de datos en una fila, quiero encontrar a todas las yahoo,com.ar por ejemplo. Como debo hacer?
Segu
Hola Mónica: no comprendo tu pregunta.
Si te refieres a cómo encontrar esas palabras tienes en la respuesta anterior una solución. Saludos
chicafisica
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!!
Segu
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"))
Ibón
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
Ibón
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
Ibón
No me deja poner el símbolo de distinto de "", con el mayor, menor.
Segu
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.
Catalina
Que formula puedo utilizar para poder contar la cantidad de 6 que escribo de 0 a 1536? aclaración, 66 cuenta por 2 veces
Segu
Hola Catalina:
Aquí tienes la respuesta: https://excelsignum.com/2020/07/05/contar-las-veces-que-se-repite-un-numero-hasta-generar-otro-numero/
Saludos.
Miguel Castro
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?
Segu
Hola Miguel:
Necesitaría que me enviases un ejemplo con lo que tienes y cómo quieres la información. Saludos.
Adalberto Cruz
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
Segu
Hola Alberto,
Con un ejemplo creo que lo entendería mejor, gracias.
Victor Miguel Ortiz Cueva
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
Segu
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
maria
Hola necesito armar una formula que me traiga la palabra Suppliers si la celda E1 es 0
espero puedan ayudarme
Segu
Una forma sería esta: =SI(Y(ESNUMERO(E1)=VERDADERO;E1=0);"Suppliers";"")
Considerando la celda vacía no como un 0
maria
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?
Segu
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
maria
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
Maria
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
Segu
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
maria
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
Maria
Mil gracias, finalmente pude lograrlo. Agradezco un montón la ayuda
CARLA
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..
Segu
Hola Carla:
Envía el ejemplo de lo que necesitas al correo electrónico con un archivo excel. No comprendo bien tu consulta.
Saludos
andres gonzalez gallego
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 #.-'º/
Segu
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
Delsy
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.
Segu
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
Omar
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
Segu
Hola Omar:
Deberías utilizar un condicional y la fórmula de este post. Si se cumple la condición, si es verdadera, entonces sumas las columnas. Saludos.
Mikel
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,
Segu
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
VILMA
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
Segu
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