EXTRAER NÚMEROS Y TEXTO DE UNA CADENA DE DATOS ALFANUMÉRICA

Hace unas semanas un lector me solicitó una macro para poder extraer de una cadena alfanumérica, por un lado los números y por otro los caracteres alfabéticos.

En su momento ya le envíe el código, pero lo tenía pendiente para subir y comentar al resto de la comunidad, creo que es un ejercicio muy interesante y además, de todo lo que he visto y leído no he encontrado nada que realizase el mismo trabajo. En algunos casos (las fórmulas) no completan la totalidad de los casos y las macros “cascaban” cuando pasabas por sus variables números de más de 15 cifras (desbordamiento).

Pues bien, el código que os propongo soluciona perfectamente ambas circunstancias. Veamos el ejemplo de una cadena alfanumérica y los datos que queremos extraer:

EXTRAER NUMEROS Y LETRAS

En este ejemplo, obtenemos a partir de la cadena alfanumérica “ALFANUM”, la totalidad de caracteres numéricos (en la columna “NUM”) y la totalidad de caracteres alfabéticos (en la columna “ALFA”). Este ejercicio que parece sencillo, es bastante complejo. Por una parte, si nos basamos en fórmulas, se complica bastante cuando los datos números no son consecutivos y por otra, con programación, debemos echar mano del ingenio para automatizar el proceso a partir de una función personalizada.

El código de la función que vamos a utilizar es el siguiente:

Public Function Numeros(Micelda As String)
With Sheets(1)
Dim i As Double, j As Double
Dim num As Variant
'lo que vamos a hacer es realizar un bucle for next dentro de la celda,
'de forma que vaya acumulando los valores numéricos en la variable "num"
For i = Len(Micelda) To 1 Step -1
If IsNumeric(Mid(Micelda, i, 1)) Then
j = j + 1
num = Mid(Micelda, i, 1) & num
End If
If j = 1 Then num = (Mid(num, 1, 1))
Next i
Numeros = (num)
End With
End Function

Así, con esta función podremos realizar la operación directamente en la hoja, como si se tratase de una fórmula. Un dato importante, es que debemos definir la variable “num” como variant, dado que si la definimos como interger, dará un error por desbordamiento según la cantidad de números que le pasemos y si usamos una double, el problema es que a partir de los 15 números, la variable devolverá valor “0” para los siguientes. Por lo tanto, el truco es escoger variant y será Excel el que se encargue de encontrar el método adecuado para mostrar todos los datos.

Ahora que tenemos la función ya construida, nos queda otra parte del código para finalizar la automatización, dado que lo normal es que no tengamos solo una celda para extraer la información, sino toda una columna de datos, en el ejemplo pongo 100 líneas. Para eso necesitamos incluir en el mismo módulo esta macro:

Sub extrae_num()
'Realizamos otro bucle for next para recorrer todas las fichas llamando a la función "Numeros"
'de forma que extraiga las letras hasta el final
Dim c As Double
With Sheets(1)
fin = Application.CountA(.Range("A:A"))
For c = 2 To fin
.Cells(c, 2) = Numeros(.Cells(c, 1))
.Cells(c, 2).NumberFormat = "@"
Next
End With
End Sub

Como podéis observar, tan solo se trata de un bucle que llama a la función “numeros” por cada línea que va recorriendo.

Con el ejemplo anterior se consiguen extraer todos los números:

EXTRAER NUMEROS Y LETRAS_1

Para realizar la misma operación, pero con letras, debemos realizar unos pequeños ajustes en la función, en primer lugar vamos a utilizar una variable acorde a los datos que vamos a extraer (string) y por otra parte, vamos realizar el condicional con aquellos datos que no son numéricos “if not isnumeric”.

Public Function Letras(Micelda As String)
With Sheets(1)
Dim i As Double, j As Double
Dim letr As String
'lo que vamos a hacer es realizar un bucle for next dentro de la celda,
'de forma que vaya acumulando los valores NO numéricos en la variable "letr"
For i = Len(Micelda) To 1 Step -1
If Not IsNumeric(Mid(Micelda, i, 1)) Then
j = j + 1
letr = Mid(Micelda, i, 1) & letr
End If
If j = 1 Then num = (Mid(letr, 1, 1))
Next i
Letras = (letr)
End With
End Function

Y la macro será prácticamente idéntica a la utilizada para los números:

Sub extrae_letr()
'Realizamos otro bucle for next para recorrer todas las fichas llamando a la función "Letras"
'de forma que extraiga las letras hasta el final
Dim c As Double
With Sheets(1)
fin = Application.CountA(.Range("A:A"))
For c = 2 To fin
.Cells(c, 3) = Letras(.Cells(c, 1))
.Cells(c, 3).NumberFormat = "@"
Next
End With

Y ahora ya podemos ejecutar los dos códigos:

EXTRAER NUMEROS Y LETRAS_2

Si os habéis fijado, los números que extraemos, los formateamos como texto, esto lo hago así para tener en cuenta los ceros al principio de la cadena, dado que de otra forma, Excel los eliminaría.

Pues este ha sido el ejercicio de hoy. Espero que os haya gustado y sobre todo, que lo podáis poner en práctica.

Descarga el archivo de ejemplo pulsando en: EXTRAER NUMEROS Y TEXTO DE UNA CADENA DE DATOS ALFANUMERICA

 

Anuncios

19 pensamientos en “EXTRAER NÚMEROS Y TEXTO DE UNA CADENA DE DATOS ALFANUMÉRICA

  1. Buenas tardes Segu. Muy interesante como resuelves el problema. Te planteo el míoa porque no sé como solucionarlo. Si tengo una celda con valor alfanumérico que contiene “24,5 Kg” necesito extraer el valor decimal 24,5 y que se guarde en una celda como número ya que después necesitaré multiplicarlo con otros valores. Se te ocurre como hacerlo? Muchas gracias

    Me gusta

    • Hola Jordi,

      Es muy sencillo, solo tendremos que modificar la macro para que permita extraer la coma “,” además de los datos numéricos, en el módulo de extraer números borra la macro y sustitúyela por esta:

      Public Function Numeros(Micelda As String)
      With Sheets(1)
      Dim i As Double, j As Double
      Dim num As Variant
      ' lo que vamos a hacer es realizar un bucle for next dentro de la celda,
      ' de forma que vaya acumulando los valores numéricos o las comas en la variable "num"
      For i = Len(Micelda) To 1 Step -1
      If IsNumeric(Mid(Micelda, i, 1)) Or Mid(Micelda, i, 1) = "," Then
      j = j + 1
      num = Mid(Micelda, i, 1) & num
      End If
      If j = 1 Then num = (Mid(num, 1, 1))
      Next i
      Numeros = (num)
      End With
      End Function
      Sub extrae_num()
      'Realizamos otro bucle for next para recorrer todas las fichas llamando a la función "Números"
      'de forma que extraiga las letras hasta el final
      Dim c As Double
      With Sheets(1)
      fin = Application.CountA(.Range("A:A"))
      For c = 2 To fin
      .Cells(c, 2) = Numeros(.Cells(c, 1))
      .Cells(c, 2).NumberFormat = "@"
      .Cells(c, 2) = .Cells(c, 2) * 1
      Next
      End With
      End Sub

      Como puedes observar, solo he añadido el el condicional que si es número o es una coma proceda a extraer los caracteres. Y en la macro, al final multiplico por 1 el resultado (así es un número).

      Funciona perfectamente 🙂

      Saludos!

      Me gusta

  2. Esta macro funciona perfecto, pero quisiera saber si se puede adecuar para que extraiga los datos numéricos de estén definidos con el símbolo $
    Ejemplo: tengo un texto de la siguiente forma ADFR45FGF$4510ASD3 Solo se necesita que la formula extraiga el valor de $4510 y que no tenga en cuenta los demás datos numéricos ni alfabéticos.

    Me gusta

    • Hola Juan:

      Sí, se puede:

      Copia este código en tu editor de VBA y ejecútalo:
      Está adaptado específicamente a la estructura de datos que comentas. Ya es un poco tarde ahora, mañana seguramente tenga un momento y comento el código. Prueba a ver.

      Editado: Te pongo el código con comentarios y un pequeño ajuste en la macro:

      Sub extrae_num()
      'Ejecutamos proceso que recorre todos los datos
      Dim c As Integer
      With Sheets(1)
      fin = Application.CountA(.Range("A:A"))
      For c = 2 To fin
      'Aplicamos formato texto a las celdas de la columna 2
      .Cells(c, 2).NumberFormat = "@"
      'aplicamos funcion extrae_cadena e insertamos resultado
      .Cells(c, 2) = extrae_cadena(.Cells(c, 1))
      Next
      End With
      End Sub

      Function extrae_cadena(Micelda As String)
      Dim numeros As String, i As Integer, j As Integer
      Dim largo As String
      'contamos hasta el $
      dolar = Application.WorksheetFunction.Search("$", Micelda)
      'extraemos los datos desde el dolar en adelante
      largo = Trim(Mid(Micelda, dolar, 1000))
      'iniciamos bucle para extraer los caracteres de la cadena anterior
      fin = Len(largo)
      For i = fin To 1 Step -1
      'si es $ o número lo extraemos, pero si es letra no extraemos
      If (Mid(largo, i, 1)) = "$" Or IsNumeric((Mid(largo, i, 1))) Then
      numeros = numeros & Mid(largo, i, 1)
      Else
      numeros = ""
      End If
      Next
      'invertimos el orden de los datos con otro bucle secundario
      sCadena = Len(numeros)
      For j = 1 To sCadena
      Resultado = Mid(numeros, j, 1) & Resultado
      Next j
      'y ya tenemos la informacion
      extrae_cadena = Resultado
      End Function

      Me gusta

  3. Pingback: EXTRAER INFORMACIÓN ESPECÍFICA DE UNA CADENA DE DATOS | EXCEL SIGNUM

  4. Buenas! quisiera saber si se puede modificar el codigo de tal manera que permita extraer dos numeros diferentes que corresponden a diferentes cosas de una misma cadena de texto, y almacenarlass en dos celdas diferentes, por ejemplo. “las condiciones ambientales del experimento fueron 24,5 grados y 1,5 atmosferas ” poder extraer ambos datos y almacenarlos en celdas contiguas.

    Gracias!!!

    Me gusta

    • Hola Daniel,

      Prueba con esta macro: hace exactamente lo que pides:

      Coloca las frases a partir de la celda B1 (en A1 pon un encabezado), de la Hoja1 y ejecuta el código:

      Sub Extrae_numeros()
      Dim i As Integer, j As Integer, Micelda As String, nCifra As Double
      With Sheets("Hoja1")
      fin = Application.CountA(.Range("A:A"))
      For j = 2 To fin
      Micelda = .Cells(j, 1)
      For i = Len(Micelda) To 1 Step -1
      If Not IsNumeric(Mid(Micelda, i, 1)) And Mid(Micelda, i, 1) <>"," Then Mid(Micelda, i, 1) = " "
      Next
      Micelda = Trim(Micelda)
      nCifra = Application.WorksheetFunction.Search(" ", Micelda)
      .Cells(j, 2) = Trim(Mid(Micelda, 1, nCifra)) * 1
      .Cells(j, 3) = Trim(Mid(Micelda, nCifra, 10000)) * 1
      Next
      End With
      End Sub

      Por cierto, los datos los paso a números multiplicando el dato * 1, si quieres que el dato se quede como texto, quita la multiplicación y listo.

      Saludos.

      Me gusta

  5. Buen día, se puede buscar de la columna “A” celda por celda y extraer un valor numérico de varias cadenas de texto pero con muchos espacios, por ejemplo extraer el numero de “CONTRATO”, El importe que contiene “REMANENTE” y pegarlo en una celda “X”, en el siguiente ejemplo es tal cual proviene de un texto, pero solo quiero extraer el valor de “REMANENTE” así como “VTO.REPORTO” extraer el segundo importe de este etc. de varios conceptos ahí mencionados

                                 TRANSACCIONES (GLOBAL)                      13:16
    ——————————————————————————–
    USUARIO       2007     xxxxxxxx   PERFIL: NO APLICA
    CONTRATO 350469456    XXXXXXX LARGE CAP ILCTRAC             HOLDING:     12779
    TRANSACCIONES DEL 01 / 01 / 2017  AL  01 / 01 / 2017
    MONEDA:  MXN  SALDO ACTUAL DE EFECTIVO :             2,080,863.17
    SEL FECHA  DESCRIPCION     EMISION  SERIE TITULOS/MONTO-REF.     IMPORTE
        4  8 REMANENTE                                                       94.52
        4  8 VTO.REPORTO     BONOS      10610            20,786       2,080,768.65
        4  8 REMANENTE                                                       1044.52

    gracias….

    Me gusta

    • Hola Marcelo:

      Los datos que presentas no se pueden tratar correctamente en este post. Para poder extraer la información numérica de esa cadena de texto es necesario que utilices la macro esta entrada:

      https://excelsignum.com/2017/04/04/extraer-informacion-especifica-de-una-cadena-alfanumerica-utilizando-texto-en-columnas/

      Con ese código vas a extraer todos los números contenidos en esos datos. En el caso de las fechas, no están correctamente formateadas en tu información, tienen espacios entre las barras por lo que solo va a extraer los número (sería necesarios componer la fecha a posteriori), pero dado que lo que buscas son los importes no tendrás problema y te servirá perfectamente.

      Saludos.

      Me gusta

      • hola que tal, agradezco tu pronta respuesta, pero en la macro que me mencionas no tengo manera de saber de donde proviene cada una de las cifras, ya que tengo que ingresar cada uno de los valores que me pide la platilla de TXT, por ejemplo, quiero extraer el “contrato” y pegarlo en una celda especificada o extraer “el remanente” y pegarlo en otra celda y así sucesivamente

        quedo a la espera, saludos

        Me gusta

        • Hola Marcelo.
          En la macro que te muestro obtienes todos los datos numéricos. Si la información es siempre la misma, con la misma estructura (Cosa que no has comentado) podrías saber en qué celda tienes los datos que te interesan, porque siempre se extraerse en la misma celda. Si los datos no tienen la misma estructura es necesario realizar un desarrollo a medida para capturar aquellos datos que definas previamente.

          Podrías enviar un ejemplo exacto en Excel a excelsignum@yahoo.es es decir la estructura de los datos desde donde se va a extraer la info y como y donde la quieres grabar.

          Desde ayer estaré de vacaciones durante varias semanas. A la vuelta te responderé.

          No obstante. Piensa que con la macro que te indico tienes los datos y si siempre es la misma estructura sabes por ejemplo que el contrato siempre está en una determinada celda y el remanente en otra sólo tendrías que implementar una segunda macro para importar esos datos.

          Saludos

          Me gusta

          • Listo, te hice llegar la información, espero le entiendas a mis comentarios de lo que pido en la hoja de calculo, cualquier duda o comentario quedo a tus ordenes

            Saludos desde Azcapotzalco

            Me gusta

              • te agradezco, ya lo intente, solo me queda la duda de; como buscar en la columna “A” y extraer y pegar los últimos caracteres alfanuméricos de los siguientes ejemplos (DC07 y/o A2NO) todas las filas que contienen estos caracteres comienzan con “TRANSACCION”

                TRANSACCION 588123884 LIQUIDACION FIDUCIARIO DC07
                TRANSACCION 588129286 ABONO A CUENTA DE CHEQUES (CLIENTES) A2NO

                QUEDO ATENTO A TU RESPUESTA GRACIAS….

                Me gusta

                • Hola Marcelo:

                  Puedes resolver tu consulta en este post que publiqué hace algún tiempo y con una fórmula que extrae la última palabra de una cadena de texto:

                  https://excelsignum.com/2014/02/23/extraer-la-ultima-palabra-de-un-texto/

                  Si lo quieres automatizar con una macro, te dejo esta función:

                  Public Function Numeros(Micelda As String)
                  With Sheets(1)
                  Dim i As Double, j As Double
                  Dim num As Variant
                  For i = Len(Micelda) To 1 Step -1
                  j = j + 1
                  If Mid(Micelda, i, 1) = " " Then Exit For
                  num = Mid(Micelda, i, 1) & num
                  If j = 1 Then num = (Mid(num, 1, 1))
                  Next i
                  Numeros = (num)
                  End With
                  End Function

                  Sub extrae_num()
                  Dim c As Double
                  With Sheets(1)
                  fin = Application.CountA(.Range("A:A"))
                  For c = 2 To fin
                  .Cells(c, 2) = Numeros(.Cells(c, 1))
                  .Cells(c, 2).NumberFormat = "@"
                  Next
                  End With
                  End Sub

                  Saludos.

                  Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s