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.
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...
Insertamos nuestro simple procedimiento Function en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:
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:
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.
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...
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.
Realmente no sé como se haría pero estoy viendo la posibilidad de hacer lo contrario. (pero valdría igual)
ResponderEliminarhacer 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
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
Eliminary poderlo cambiar de uno a otro.
Un saludo.
Sabiendo que tiene "/" tiene formato fecha
Eliminarsi tiene ":" {chr(58)} tiene formato hora
si tiene ";" {chr(59)} tiene formato lista de valores separados por ";"
Un saludo.
Hola Manuel
Eliminarentiendo que quieres quedarte sólo con los números ¿¿??
Buen día.
EliminarNo 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. ;)
Hola de nuevo ;-)
Eliminarpara 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
Buen día.
EliminarCompletamente 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.