por 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:
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:
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:
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
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
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!
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.
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
Muchas gracias por tu valiosa ayuda me fue de mucha utilidad esta macro.
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!!!
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.
Muchas graciass! saludos desde Chile
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….
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.
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
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
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
Hola Marcelo
Lo he visto, pero estoy de vacaciones. Hasta dentro de unas semanas no podré ponerme trabajar con la info.
Cuando lo tenga resuelto te los envío. Saludos
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….
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.
Te agradezco mucho tu pronta y favorable respuesta.
quedo a tus ordenes
saludos
Hola Marcelo:
Te he enviado un correo con una propuesta para tu consulta.
Saludos
hola Segu, vi tus comentarios y sos un genio en excel, quizas me puedas dar una mano. Mi consulta es:
tengo una columna con numeros y letras y quiero sumar solo los numeros:
ejemplo
goma de borrar 178
lapices 45
sellos 100
plasticola grande 10
plasticola chica 35
en una celda me tire el total en numero.
y otra consulta con el mismo ejemplo.
Necesito extraer solo los numeros de cada celda.
Desde ya muchas gracias.
Saludos
Hola Ova:
Te he enviado un correo con el mismo archivo del ejemplo, y con una pequeña modificación para que sume los valores dentro de la macro y los muestre en una celda. Saludos
Segu, muchas gracias por tu ayuda y tu tiempo. Estoy aprendiendo formulas en excel. Hay alguna formula para usar directamente en la celda y no tener que hacer una macro con modulos.? para llegar al mismo resultado?
Desde ya muchas gracias.
Hola Ova:
Te envío dos fórmulas que hacen lo indicas en tu ejemplo. Sin embargo, para esta clase de tareas te aconsejo VBA.
Saludos.
SEGU OJALA E PUEDAS APOYAR COMO PUEDO RESTAR DOS CANTIDADES ALFANUERICAS DE UNA BASE DE DATOS DE DOS COLUNAS DE MATERIALES QUE NO ESTAN ADYACENTES POR EJEPLO Ci – Gj SIENDO C2 = 30 PZ, G2= 20 PZ ; C3 = 10 TR, G3 = 5 TR, C4 = 200 M, G4 = 150 M HASTA n CANTIDADES Y DE DIFERENTES UNIDADES COO SE VE EN LA LISTA
Hola Daisem:
Necesitaría ver un ejemplo de lo que necesitas. Puedes enviarlo a info@excelsignum.com en un archivo excel explicando qué deseas obtener. Saludos.
Muchas gracias por su valiosa colaboracion, me sirvio de mucho, gracias
Hola. Buen día. Si tengo un código como este H004567 REFERENCIA 250 G, ¿se puede seleccionar solo H004567 por un lado y el resto por el otro? De igual forma este tipo de datos: 123456789 REFERENCIA XXX 1000 G, que de un lado separe 123456789 y por en otra columna REFERENCIA XXX 1000 G. Agradezco su ayuda.
Hola:
Puedes hacerlo con una fórmula:
Si tienes los datos en la celda A2, para sacar el primer dato usa esto en B2:
=EXTRAE(A2;1;HALLAR(" ";A2)-1)Y en B3 para los otros datos:
=EXTRAE(A2;HALLAR(" ";A2)+1;LARGO(A2))