16 de octubre de 2007

Validar la existencia de determinado texto en una celda

La consulta a tratar en esta ocasión es la siguiente: Quiero una fórmula que me devuelva 1 si una celda contiene el texto "mitexto" y que me devuelva 0 si no lo contiene. ¿Cómo?

Desconozco la utilidad que este usaurio daría a la fórmula. Si pensabas usar estos unos y ceros para hacer un filtro, podías activar el Autofiltro (Filtro - Autofiltro), después seleccionar Personalizar... y de la lista desplegable la opción Contiene (o bien Empieza con o Termina con) y escribir "mitexto". En los tiempos que corren ya casi no es necesario el uso de columnas de unos y ceros.

De cualquier modo, aquí va la respuesta:

Hay tres posibilidades: determinar si una celda comienza con "mitexto", termina con "mitexto" o si contiene "mitexto" en cualquier ubicación.

Para el primer caso nos auxiliamos de la función IZQUIERDA:

=IZQUIERDA(A1, 7), la cual nos da las 7 primeras letras del extremo izquierdo del primer argumento (A1).

Ahora la anidamos en la función SI, y pedimos a Excel que devuelva 1 si el resultado de la anterior fórmula es "mitexto", 0 en cualquier otro caso:

=SI(IZQUIERDA(A1,7)="mitexto",1,0)

En el segundo caso, utilizamos la función DERECHA y procedemos de la misma forma:

=SI(DERECHA(A1,7)="mitexto",1,0)

Para el tercer caso, podemos auxiliarnos de la función HALLAR, o bien, de la función ENCONTRAR. Ambas devuelven la posición inicial en la que se encuentra el texto buscado, dentro de un texto especificado, con la diferencia de que ENCONTRAR es sensible a mayúsculas y minúsculas. Utilizan tres argumentos: texto_buscado ("mitexto"), dentro_del_texto (A1) y núm_inicial (1). Núm_inicial se refiere a la posición de dentro_del_texto a partir de la cual queremos que empiece la búsqueda, contando de izquierda a derecha; si lo omitimos, Excel supondrá que es 1:

Suponiendo que en la celda A1 hemos escrito "abc mitexto", la siguiente fórmula nos devuelve 5:

=HALLAR("mitexto", A1, 1)

El problema con HALLAR es que si la celda analizada no contiene el texto buscado, en lugar de devolvernos 0, nos devuelve el valor de error #¡VALOR!

Utilizamos entonces la función ESERR. Esta función devuelve VERDADERO si el resultado de una fórmula es un error del tipo #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO! (o sea todos excepto los errores #¡N/A. En cambio, ESERROR sí toma en cuenta los errores #N/A). Si el resultado de una fórmula no es un valor de error, devuelve FALSO.

Está fórmula devuelve FALSO si "mitexto" se encuentra en A1. Devuelve VERDADERO si la celda no contiene "mitexto":

=ESERR(HALLAR("mitexto", A1, 1))

Ahora la anidamos en una función SI para indicar a Excel que devuelva el 1 o el 0 buscados:

=SI(ESERR(HALLAR("mitexto", A1, 1)), 0, 1)

En los tres casos, podemos elaborar fórmulas más cortas:

=--IZQUIERDA(A1,7)="mitexto"

=--DERECHA(A1,7)="mitexto"

=--NO(ESERR(HALLAR("mitexto",A1)))

En futuras entradas veremos cómo funcionan estas últimas fórmulas, y debatiremos la conveniencia de usar fórmulas reducidas o extensas.

5 comentarios:

  1. Quiero saber cómo hago para que por medio de una validación, no se pueda dejar vacía la celda (sin texto),gracias.

    ResponderEliminar
  2. Gracias por esta fórmula, sin embargo ahora me gustaría saber como poner varias funciones Hallar dentro de un SI... probé con =SI(HALLAR("GUAYAQUIL";Y17;1);1;SI(HALLAR("MILAGRO";Y17;1);2;0)) pero no funciona..Saludos espero que me ayuden con esto...

    ResponderEliminar
    Respuestas
    1. Marcos:
      Utiliza la función ESERR como indica la nota:

      =SI(ESERR(HALLAR("GUAYAQUIL";Y17;1));1;SI(ESERR(HALLAR("MILAGRO";Y17;1));2;0))
      Saludos.

      Eliminar
  3. hola
    quiero una fórmula que me busque un texto contenido en una matriz y que me devuelva su valor contenido en otra columna. si uso buscarv no funciona porque me busca el texto exacto, pero yo lo que quiero es introducir una parte del texto tal y como se hace al filtrar una tabla que si pongo "contiene" me busca que contenga sólo esa palabra. ej,
    Que me busque la palabra sants en una matriz que dice Estación Sants y luego me devuelva el valor que está en otra columna, si el valor por ejm es un 5, devolverá ese número.

    ResponderEliminar

Creative Commons License
Esta obra está bajo una licencia de Creative Commons.
Excel, Excel 2003, Excel 2007 y el logo XL son marcas registradas de Microsoft Corporation.