martes, 8 de diciembre de 2015

VBA: Una función para extraer caracteres de texto de un valor alfanumérico.

Un caso frecuente es querer trabajar únicamente con los caracteres de texto dentro de una cadena alfanumérica.
Por este motivo hoy veremos una sencilla UDF en VBA para Excel que nos permitirá extraer las letras de nuestra celda, dejando al lado los valores numéricos.
Hola tengo una duda existe formula para separar letras de números cuando estas están en una celda como por ejemplo "100:129-145S" y q solo me salga "S" pero la siguiente 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.


En el caso propuesto por el lector tenemos un pequeño 'handicap' añadido, y es que contamos con dos caracteres tipo texto(: y -) que no queremos extraer, y tendremos que evitar...

VBA: Una función para extraer caracteres de texto de un valor alfanumérico.



Insertamos nuestro simple procedimiento Function en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Function ExtraeTexto(celda As Range) As String
Dim txt As String

'recorremos cada caracter
For i = 1 To Len(celda.Value)
    'evaluamos si NO es numérico
    If Not IsNumeric(Mid(celda.Value, i, 1)) And _
        Mid(celda.Value, i, 1) <> ":" And _
        Mid(celda.Value, i, 1) <> "-" Then
        'en caso afirmativo (NO numérico) concatenamos con resultados anteriores...
        txt = txt & Mid(celda.Value, i, 1)
    End If
Next
'finalmente devolvemos los caracteres textuales...
ExtraeTexto = txt
End Function



Adicionalmente a la solución propuesta podemos obtener lo mismo empleando la función SUSTITUIR anidada sobre sí misma tantas veces como la necesitemos.
En un primer paso 'eliminaremos los caracteres de texto no deseados (: y -), en la celda E2:E3:
=SUSTITUIR(SUSTITUIR(A2;":";"");"-";"")

Para luego construir una función algo larga con 10 funciones SUSTITUIR anidadas entres sí (una por cada número 0-9), aplicada sobre las celdas anteriores E2 y E3:
=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(E2;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"")
Larga pero eficiente ;-)


Una segunda alternativa más técnica de nuestra función personalizada 'ExtraeTexto' vendría del uso del operador Like:
Function ExtraeTexto2(celda As Range) As String
Dim txt As String

'recorremos cada caracter
For i = 1 To Len(celda.Value)
    'evaluamos si está entre A y Z
    If Mid(celda.Value, i, 1) Like "[A-Z]" Then
        'en caso afirmativo concatenamos con resultados anteriores...
        txt = txt & Mid(celda.Value, i, 1)
    End If
Next
'finalmente devolvemos los caracteres textuales...
ExtraeTexto2 = txt
End Function



Fijémonos en la comparativa realizada:
Like "[A-Z]"
y como al probar la función deja automáticamente fuera los caracteres no deseados : y -

Es importante saber que por defecto está predefinido como método de comparación de cadenas la instrucción: Option Compare Binary; esto significa para nuestro ejemplo que nuestro intervalo de letras [A-Z] entiende y compara con A, B, C, D .. Z.

Para una coincidencia más concreta deberíamos haber empleado y definido al inicio de nuestro módulo la opción:
Option Compare Text, donde el intervalo [A-Z] buscaría coincidencias entre con A, a, À, à, B, b, C, c, D, d, E,e, etcétera.

7 comentarios:

  1. Realmente no sé como se haría pero estoy viendo la posibilidad de hacer lo contrario. (pero valdría igual)

    hacer un bucle for i=1 to len("cadena")
    asignar cada letra a una variable_Caracter as string
    y mediante Chr(xx)-Chr(zz) verificar su valor

    if variable_Caracter = Chr(xx)-Chr(zz) then
    Nueva_Cadena = nueva_Cadena & variable_Caracter
    obtener la cadena limpia.

    los códigos de Chr(44)-Chr(59) que corresponden a:
    coma ","
    punto "."
    signo menos "-"
    división "/" (este se mete en medio pero simplifica el código, supongo)
    los caracteres numéricos de 1 a 9

    para la cuestión inversa que es lo que piden habría que cambiar
    if variable_Caracter = Chr(44)-Chr(59) then
    por
    if variable_Caracter <> Chr(44)-Chr(59) then
    o
    if variable_Caracter = Chr(65)-Chr(90) _
    or _
    variable_Caracter = Chr(97)-Chr(122)then
    que corresponden en el primer caso a las letras en mayúsculas
    y en el segundo caso a as minúsculas.

    Aún estoy intentando hacerlo, pero como no veo VBA desde el milenio pasado, voy muy lento (reaprendiendo).
    Si a alguien se le ocurre el código y lo pasa me ahorraría un largo recorrido de curva de aprendizaje.
    Un saludo

    ResponderEliminar
    Respuestas
    1. También sería interesante que contase el número de veces que aparece la coma o el punto para ver si el número está en formato España o USA
      y poderlo cambiar de uno a otro.
      Un saludo.

      Eliminar
    2. Sabiendo que tiene "/" tiene formato fecha
      si tiene ":" {chr(58)} tiene formato hora
      si tiene ";" {chr(59)} tiene formato lista de valores separados por ";"

      Un saludo.

      Eliminar
    3. Hola Manuel
      entiendo que quieres quedarte sólo con los números ¿¿??

      Eliminar
    4. Buen día.
      No necesariamente.
      Me explico, un número (como todos saben) no solo son los dígitos, sino los signos de puntuación que llevan, el punto o coma decimal y el signo indicativo de negativo.
      Lo que estoy intentando (reaprender Vba Excel) es por un lado limitar la introducción de datos no validos, eso ya lo tengo (esta chupado); por otro detectar dichos signos de puntuación para poder reformatear de manera automática al sistema regional, de esta manera será posible trabajar la parte entera y la fraccionaria (también ya la tengo clara) para trabajar sobre cada una de forma independiente.
      Como comenté al principio, el objetivo fundamental es reaprender algo que en los 90s ya hacía, de hecho todo esto lo había hecho para una función de cambio de base de numeración (decimal a cualquier otra) pero como lo había hecho, esa ya es otra historia.
      el caso es que buscando información encontré este post, y pensé que quizás la idea que tuve hace años fuese una solución adecuada, ya que con el método chr(xxx) y por medio de comparación de caracteres (¿¿chrB o chrW??)) o de cadena completa (aquí aún no sé como (valor global de cadena)) permitiría limpiar una cadena, separar números, letras y otros símbolos, hacer cambios de formato, etc.
      En su tiempo la usaba para esto último fundamentalmente (por aquello del copy/paste) y para cambiar de base.
      Perdón por el rollazo, pero era necesario explicarme adecuadamente.
      En resumen, lo que interesa realmente es el método de hacerlo (para reaprender) no solo el resultado.
      ¡Gracias! y un fuerte abrazo.

      P.S. Y como veo que os gusta rizar el rizo al igual que yo, pues...eso. ;)

      Eliminar
    5. Hola de nuevo ;-)
      para recuperar textos o números dentro de una cadena 'alfanumérica' lo normal es emplear el método descrito, habitualmente un loop sobre caracteres de la cadena de caracteres y una comparativa con un like [A-Z] o like [0-9], el problema de las comas, putos, barras lo gestionaríamos con condicionales sobre chr(xxx) (como has indicado tu) o directamente sobre el valor ( =".")...
      Pero siempre tendrás un problema que esas comas, puntos, etc... queden fuera de la parte numérica (ejem: AB.1,234.25.XY donde el número es 1.234,25 en formato 'español')

      La mejor forma (más simple al menos) es la descrita... en mi opinión... pero no infalible, claro

      Saludos

      Eliminar
    6. Buen día.
      Completamente de acuerdo.
      Dejo por ahora este tema aquí, para no sobrecargar el tema.
      Seguiré desarrollando mi función() o sub(), para según que uso se le de.
      ¿Hay alguna forma de contactarte para alguna consulta puntual que no sea a través de el foro?
      Un saludo cordial.

      Eliminar

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