martes, 26 de enero de 2010

Función ESTEXTO y ESNUMERO en Excel.

En relación al post donde mostré una manera de ENCONTRAR y reemplazar ciertos caracteres, continuamos hoy dando una vuelta de tuerca al asunto, ya que pretendemos encontrar el último caracter tipo "alpha" no numérico desde la derecha antes del guión en el caso que exista:
...la letra puede ser cualquiera del abecedario y pueden ser hasta 4:
por ejemplo
20T-29
33AXH-300
250MBXH-300A
56LFV1
¿existe alguna manera de que busque el último caracter tipo "alpha" no numérico desde la derecha antes del guión en el caso que exista?...

Nos aseguramos que la estructura de los códigos es única y homogénea, es decir, que todos los códigos como máximo pueden tener uno y sólo un caracter 'letra' por la derecha; además, de acuerdo a lo explicado en el post anterior, el número de dígitos en la segunda parte del código es un máximo de cuatro; esto es en total, la segunda parte del código tendrá un máximo de cinco caracteres. Para mi planteamiento esta hipótesis es básica.

Vamos a emplear las funciones ESTEXTO(valor) y ESNUMERO(valor) para localizar el caracter 'texto' buscado. Las funciones ES, son funciones lógicas, por lo que devolverán VERDADERO o FALSO.
Para homogeneizar todos los códigos, le quitaremos el guión intermedio de éstos. Mediante la función
=SUSTITUIR(Código;"-";"")
Nuestra tabla de datos de partida es:


Una vez que tenemos nuestro 'Códigos' sin guiones, podemos empezar a buscar nuestro caracter Texto. Sabiendo que la posición máxima en la que puede estar situado este caracter es la sexta por la derecha (un posible primer caracter texto por la derecha, y luego un máximo de cuatro numéricos) construiré una tabla auxiliar con esas posiciones en columnas, invirtiendo su posición de izquierda a derecha uno a uno:


haz click en la imagen


Lo que hacemos en esta tabla auxiliar de seis columnas es identificar caracter a caracter de cada 'Código' empezando por la derecha:

=SI(ESNUMERO((EXTRAE($B2;LARGO($B2);1)*1));(EXTRAE($B2;LARGO($B2);1)*1);EXTRAE($B2;LARGO($B2);1))

analizamos paso a paso la fórmula para el primer valor por la derecha:
  • con la función EXTRAE(texto; posición de inicio; número caracteres a extraer):
    EXTRAE($B2;LARGO($B2);1) consigo discriminar o extraer del 'Código' un caracter, empezando a contar en este caso por el último del 'Código'

  • al multiplicarlo por uno, si el caracter es numérico se transforma en número

  • al aplicarle la función ESNUMERO y usar la respuesta como prueba lógica de la función SI condicional, obtendré dos posibles respuestas

    1. si la respuesta es VERDADERO, esto es, si el caracter extraido es un número, entonces la función SI nos mostrará el número en formato número

    2. si la respuesta es FALSO, esto es, si el caracter extraido NO es un número, entonces la función SI nos mostrará el caracter en formato texto


Repetiremos esta fórmula para cada una de las columnas de nuestra tabla auxiliar, pero con una mínima modficación, ya que para la función EXTRAE le cambiaremos la posición de inicio.
Para la segunda columna, es decir, el segundo caracter por la derecha del 'Codigo' tendremos la fórmula:
=SI(ESNUMERO((EXTRAE($B2;LARGO($B2)-1;1)*1));(EXTRAE($B2;LARGO($B2)-1;1)*1);EXTRAE($B2;LARGO($B2)-1;1))
vemos que la posición de incio viene dada por la función LARGO('Código')-1;
para la tercera columna será LARGO('Código')-2 y así sucesivamente.

Observad cómo el primer 'Código', en su columna sexta da un error; lógico, puesto que este 'Código' sólo tiene cinco caracteres (después de quitarle el guión!!).

Comenzamos con la segunda parte de la tarea. Ya tenemos en nuestra primera tabla auxiliar los caracteres alfanuméricos colocados de izquierda a derecha de la segunda parte de nuestros 'Códigos'. Como sabemos que existe la posibilidad que el primer caracter por la derecha, es decir, la primera columna de nuestra tabla auxiliar, sea tipo 'Texto', para la construcción de nuesta segunda tabla auxiliar (es la última, no desespereis!) vamos a obviarla.
Construimos de una manera muy sencilla esta segunda tabla, aplicando la función ESTEXTO sobre las celdas de la primera:



Y llegamos al final, descubrir en una celda, para cada 'Código' cuál es el caracter 'Texto' buscado. Para ello aplicamos un SI condicional sobre los valores de nuestra segunda tabla:


Con la función
=SI(E8;E2;SI(F8;F2;SI(G8;G2;SI(H8;H2;SI(I8;I2;"SIN LETRA")))))
lo que hacemos es encontrar el primer valor VERDADERO de la segunda tabla auxiliar, es decir, el primer valor tipo 'Texto' y mostrarlo; lo buscamos columna a columna de izquierda a derecha en nuestras tablas auxiliares; acabando el condicional con un 'Sin letra' para aquellos casos que no encuentre un tipo 'Texto'.
Y eso es todo, tenemos para cada 'Código' el último caracter tipo "alpha" no numérico desde la derecha antes del guión en el caso que exista.

40 comentarios:

  1. tengo una duda estoy haiendo un directorio me preguntaba como hacer que se acomode solo alfabeticamente sin necesidad de yo estarlos acomodando

    ResponderEliminar
  2. Hola...
    bueno, seguro sabes que existe una opción de Ordenar bases de datos en Excel, donde sólo le debes indicar cuales son tu prioridad de orden respecto a los campo.
    Echa un vistazo a esta entrada
    http://excelforo.blogspot.com/search/label/Ordenar
    Con esta opción Excel te lo ordenará de manera automática siguiendo tus criterios.
    Slds

    ResponderEliminar
  3. como puedo extraer el segundo caracter de un nombre ejemplo:

    MARCO--->A
    PEDRO-->E
    les estare muy agradecido por su ayuda.

    ResponderEliminar
  4. Hola,
    existe una función llamada EXTRAE que sirve exactamente para eso; en tu caso deberás aplicarla, suponiendo el nombre en la celda A1:
    en otra celda escribimos =EXTRAE(A1;2;1)
    lo que nos extrae de la celda A1 contando desde el segundo caracter por la izquierda uno; en tu caso la segunda letra...
    Espero te sirva.
    Slds

    ResponderEliminar
  5. Hola, tengo una duda, en un fichero en algunas celdas hay caracteres ocultos, no los he podido determinar de ninguna forma.
    algo así ~1.1.2~ los símbolos "~" (que uso para ejemplificar porque realmente no se que simbolos sean) no se muestran y me generan problemas al descargarlos en un sistema.

    He tratado de probar =SI(ESNUMERO(EXTRAE(D30,1,1)),"ok","no") pero siempre me da como respuesta "no", no importando que solo haya un 1 en la celda de referencia.

    Estoy usando mal los argumentos de alguna función?

    Saludos.

    ResponderEliminar
    Respuestas
    1. Hola, con tu fórmula estás evaluando si el símbolo en cuestión (sea el que sea) es un número..., además sólo estás comprobando el primer caracter.
      Otra cuestión es que al utilizar la función EXTRAE siempre obtienes un tipo texto, por lo que al evaluar si ESNUMERO Excel lo entenderá siempre como FALSO, esto es, que no es un número, por eso siempre, en tu condicional, te dice 'no'.

      Para eliminar esos caracteres o bien los quitas con fórmulas (má bien te quedas con los valores válidos) SIEMPRE QUE ESTÉN AL PRINCIPIO Y FINAL, con la fórmula:
      =EXTRAE(A1;2;LARGO(A1)-2)

      o bien te toca hacer un Reemplazar por nada con ese caracter en cuestión.
      Slds

      Eliminar
  6. Hola, gracias por anticipado.

    Tengo una serie temporal de datos (caudales de salida de una balsa ) con un intervaalo de tiempo regular, en mi caso cada segundo. Para posterires anàlisis, desearia reducir el tamaño de la serie, obteniendo los valores en un intervalo mas amplio ( por ejemplo cada cinco minutos) simplemente eliminando los valores intermedios. Agradeceria que me indicarais como pudeo hacer esto en excel 2007. Gracias.
    Lluís

    ResponderEliminar
    Respuestas
    1. Hola Lluís...
      tendrías que añadir una columna auxiliar que determinara esos registros cada cinco minutos, por ejemplo con una fórmula (suponiendo columna de tiempos en A:A), comenzando en B1 y luego arrastrando:
      =SI(RESIDUO(CONTAR($A$1:A1);300)=0;"cinco minutos";"")
      A continuación tienes que aplicar un filtro sobre ambas columnas para obtener de la columna auxiliar sólo los registros con 'cinco minutos'.
      Estoy suponiendo que cada fila corresponde con un registro de un segundo...
      Slds

      Eliminar
    2. Muchas gracias.

      Es exactamente lo que necesitava.

      Eliminar
  7. Hola,
    Estoy tratando de hacer una formula asi
    =SI(ESNÚMERO(A1);(A1);"") pero que reconozca textos alfanuméricos ya que en la celda no es siempre o numérica/alfabética.

    desde ya muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      debo entender que tratas de identificar celdas numéricas, pero que si se trata de 'alfanumérica' también la verifique...
      si es así, tenemos un pequeño inconveniente, ya que Excel sólo identifica dos tipos de valores, onuméricos puros o texto, un 'alfanumérico' es a todos los efectos una celda de texto.
      Por tanto sólo podremos trabajar con las funciones ESNUMERO o ESTEXTO...
      Para identificar un alfanumérico como 'numérico' tendríamos que generar una función VBA personalizada que revisara caracter por caracter del 'texto alfanumérico', de tal forma que si encuentra al menos un 'número' lo de como tal...

      Quizá viendo los textos o valores con los que trabajas se pudiera construir alguna fórmula en la hoja de cálculo, sin necesidad de recurrir a VBA.
      Slds

      Eliminar
  8. Son textos numéricos con o sin barras/guiones (9305354 o 6608-650/2) o alfanuméricos (HK-01215/3) o alfabéticos (VROX)

    ResponderEliminar
    Respuestas
    1. Hola, si es siempre esa la forma de los registros, parece que se cumple la norma que si el 1er caracter por la derecha es numérico verifica lo que quieres, asi que, entiendo la fórmula que buscas podría ser:
      =SI(ESNUMERO(VALOR(DERECHA(A1;1)));A1;"")
      Slds

      Eliminar
  9. Muchas gracias por tu respuesta. El problema es que los registros no cumplen una norma, varían en cantidad de caracteres y formatos.
    Con el poco conocimiento que tengo hice la siguiente fórmula (la cual no funcionó):
    =SI(O(ESNÚMERO(VALOR(DERECHA(A1;1))));(ESTEXTO(VALOR(DERECHA(A1;1))));(A1);"")
    Lo que intento hacer es que si el 1er caracter de A1 es número o texto la celda de la fórmula sea igual a A1. Pero no consigo hacer una fórmula correcta

    ResponderEliminar
    Respuestas
    1. Hola...
      con esa fórmula estás diciendo que si el primer caracter por la derecha o es texto o es número (por tanto se dará siempre) devuelva el valor de A1.
      Si aclaras la regla que necesitas para determinar cuando quierres que te devuelva esa celda, sería más fácil encontrar una fórmula.
      :-)
      Slds

      Eliminar
  10. Lo único que necesitaría, por mas tonto que suene, es que me devuelva la celda A1 si esta escrita (ya sea numerico, alfabetico o alfanumerico). Pero con la formula anterior no me lo permite, no se porque.

    ResponderEliminar
  11. Ismael, muchas gracias por tu ayuda. Igual ya encontre la formula y era por demas de facil en comparacion a las que estaba haciendo:
    =SI((A1<>0);(A1);"")
    Muchas gracias por tu tiempo

    ResponderEliminar
    Respuestas
    1. Correcto,
      estabas complicando mucho el tema mezclando el tipo de celda 'numérico' o 'texto'.
      Por cierto, no hacen falta tanto paréntesis, funciona igual sin ellos:
      =SI(A1<>0;A1;"")
      Slds

      Eliminar
  12. Hay alguna función para extraer un número de una cadena?
    Ej. laakun292, AMR0K_21F, 001Kas_eT, donde para cada valor devuelva 292, 0 ó 21, 001 o 1?

    ResponderEliminar
    Respuestas
    1. Hola Mefisto,
      igualemente un gusto saludarte.
      Se podría emplear la función EXTRAER si tuvieramos la certeza que siempre están situados en la misma posición dentro de la cadena de texto... o también si cumplieran alguna regla de localización, por ejemplo, antes de cad coma o guión bajo, etc...

      Tal cual planteas el ejemplo es dificil saber.
      Un cordial saludo y muchas gracias

      Eliminar
  13. Tengo el siguiente problema necesito que en el IVSS Semanal no coloque nada si cumple una de estas condiciones que sea mayor a 55 y el sexo sea "F" de femenino y que sea mayor de 60 si el sexo es "M" masculino, si no cumple las condiciones debe dar el calculo
    Fecha de Nacimiento Edad Sexo Sueldo Quincena IVSS Semanal
    2 10 1989 26 F 3.000,00 1.500,00 #¡VALOR!
    hice esto pero no me da
    =SI(Y(F15<55;G15="F");(I15*$N$1/$N$2););SI(Y(F15>60;G15="M");(I15*$N$1/$N$2);)

    ResponderEliminar
    Respuestas
    1. Hola Orosman,
      un placer saludarte igualmente.
      Creo entender que lo que necesitas es:
      =SI(O(Y(F15<55;G15="F");Y(F15>60;G15="M"));(I15*$N$1/$N$2);0)
      saludos

      Eliminar
  14. Hola tengo una duda existe formula para separar letras de numeros cuando estas estan en una celda como por ejemplo "100:129-145S" y q solo me salga "S" pero la siguente celda q la sigue es "100:129-13XL" y como hacer q me salga "XL".
    Ya que use una formula =EXTRAE(B4;HALLAR("-";B4;1)+1;1) Pero solo me funciona para la primera celda y no para la segunda.

    ResponderEliminar
    Respuestas
    1. Hola Heber,
      para tal cosa se suelen emplear funciones personalizadas con VBA...
      Intentaré subir un ejemplo en breve.
      Un saludo

      Eliminar
  15. Hola, quisiera saber cómo poder extraer el último caracter numérico contado de izquierda a derecha de una cadena alfanumérica de una celda, cuya longitud de texto es variable, por ejemplo:
    En la serie AA-001-AA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie 2-AA-001A, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie 02-AAA-01, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie AAA-001-A, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie A-001-AA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie A-2A-01, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie 02-AA-1, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie A-01-AAA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).

    ResponderEliminar
    Respuestas
    1. Hola
      puedes aplicar la función personalizada (UDF) expuesta en
      http://excelforo.blogspot.com.es/2015/12/vba-una-funcion-para-extraer-caracteres.html
      modificándola mínimamente (añadiendo la negación NOT), y una vez tengas el valor sólo de números, quedarte con el primer dígito por la DERECHA.

      Saludos

      Eliminar
  16. Buenos días,

    Antes de nada gracias por esta entrada! Tengo una pregunta ¿Cómo hago para validar una celda la cual quiero que el primer dígito sea alfanumérico y los otros 7 numéricos? Es decir, el código por ejemplo sería "A0000000", si no cumple esas trazas que aparezca un mensaje de error y no permita su inserción.

    Si no me he explicado disculpa, cualquier cosa que necesites puedes preguntarme.

    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola,
      prueba con una Validación de datos personalizada con la siguiente fórmula (por ejemplo para la celda C3):
      =Y(NO(ESNUMERO(VALOR(IZQUIERDA(C3;1))));ESNUMERO(VALOR(DERECHA(C3;7))))

      Saludos

      Eliminar
  17. Hola.
    Quisiera saber como podria usar una formula para buscar un valor en una base de datos.. buscarv no me sirve ya que el resultado muestra la primera coincidencia y si no es exacta no arroja valor. mi valor es numerico y esta en celdas que contienen texto y otros simbolos, ademas puede estar en mas de una celda y puede estar completo como también los ultimos cinco numeros.. la idea es que muestre cada celda en la que encuentre mi numero y traiga como resultado un dato que tengo en otra columna que lo referencia unico y diferente a los otros que encuentre coincidentes.. como el concepto basico del buscarv pero añadiendo el funcionamiento del buscador ctrl mas b que muestra la ventana donde pones el cada numero, te da opciones de buscar todos y despliega todas las coincidencias en un listado.. espero haberme hecho entender..muchas gracias..

    ResponderEliminar
    Respuestas
    1. Hola,
      si el valor devuelto es numérico, y la combinación doble de búsqueda retorna un solo dato, entonces puedes buscar ese dato empleando la función SUMAR.SI.CONJUNTO o también BDEXTRAER

      En todo caso, si necesitas desplegar todas las coincidencias (y no solo una de ellas) deberás emplear fórmulas matriciales...

      Todo depende de cómo estén distribuidos los datos en tu hoja

      Slds

      Eliminar
  18. Hola. Quisiera poder extraer un valor numérico dentro de una cadena de texto cuya longitud y posición es variable. Por ejemplo tengo: "Plex12und" y necesito extraer solo el 12, pero también hay casos como "maquinex45 rol" y necesito extraer el 45. Existe alguna formula para hacerlo de forma general y poderla correr?. Muchas gracias.

    ResponderEliminar
  19. Hola que tal como hago en Excel que sólo permite en una celdas o varias celdas se digiten un rango 4 letras de hay solo 7 digitos. Por ejemplo: HJRR1234567. Muchas gracias espero respuesta.

    ResponderEliminar
    Respuestas
    1. Hola,
      hay alguno similar en comentarios anteriores:
      prueba con una Validación de datos personalizada con la siguiente fórmula (por ejemplo para la celda C3):
      =Y(NO(ESNUMERO(IZQUIERDA(C3;4)));ESNUMERO(VALOR(DERECHA(C3;7))))
      debería funcionarte
      Saludos

      Eliminar
  20. HOLA, quisiera saber que formula en excel 2013 me serviría para extraer la letra E de los nuemros de mis facturas,y arrastrar la formula a todos los documentos ejemplo E102,

    ResponderEliminar
    Respuestas
    1. Hola
      la función IZQUIERDA:
      =IZQUIERDA(num_fra;1) tendrás la E
      con
      =VALOR(DERECHA(num_fra;LARGO(num_fra)-1)) tendrás el número 102

      Saludos

      Eliminar
  21. Buenas tardes,
    Tengo una base de dtos de mas de 20 mil registro, necesito identificar la sexo o genero de la persona, es decir identificar por el nombre si la persona es hombre o mujer. Que formula puedo utilizar para esto?
    Agradezco mucho sus aportes.

    ResponderEliminar
    Respuestas
    1. Hola,
      difícil... se me ocurre que tuvieras una base de datos de todos los nombres existentes identificados como hombre o mujer, y luego cruzarlo con tu BD de 20.000 nombres...

      A priori, que yo sepa, es imposible saber si un nombre es de H o M

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.