Extraer números o letras de códigos alfanuméricos en Excel

martes, abril 24, 2007

En esta nota veremos cómo extraer con Excel los números, o las letras, de códigos alfanuméricos.
Muchas empresas usan códigos alfanuméricos, por ejemplo, para los números de catálogo, como por ejemplo 123ABC. A veces se presenta la necesidad de aislar la parte numérica o las letras de estos códigos.
En la primera parte de esta nota veremos una solución con fórmulas Excel, útil para los casos en los que las cifras y las letras están agrupadas.
En la segunda parte veremos una solución con una UDF (función definida por el usuario) para los casos en los que no hay agrupación de cifras y letras (por ejemplo, 12A3BC).

Solución con fórmulas (cifras y letras agrupadas)

Fuente: Extracting numbers from alphanumeric strings

Supongamos que en la celda A1 de nuestra hoja de Excel tenemos el código ABC123. Nuestra tarea es extraer, con fórmulas Excel, la parte numérica, es decir, 123.
Como los números y las letras están agrupados, nuestra fórmula deberá hacer lo siguiente:

1 – descomponer el código en sus componentes

Esto lo hacemos combinando las funciones EXTRAER y FILA de la siguiente manera

=EXTRAE(A1;FILA($1:$7);1)

Esta fórmula descompone el codigo ABC123 y pone cada uno de sus componentes en una matriz virtual definida por la función FILA. En este ejemplo FILA define 7 filas, pero podemos agregar más en caso de ser necesario.

2- determinar si hay un número en el código

Para esto combinamos la función ESNUMERO en la fórmula anterior

=ESNUMERO(1*EXTRAE(A1;FILA($1:$7);1))

Esta fórmula genera una matriz de valores VERDADERO y FALSO.

Al usar EXTRAE los números son convertidos en texto, por lo tanto tenemos que multiplicar la expresión por 1 para forzar la conversión nuevamente a números.

3 – encontrar la posición del primer número (dígito) en el código

Para esto usamos la función COINCIDIR de la siguiente manera:

=COINCIDIR(VERDADERO;ESNUMERO(1*EXTRAE(A1;FILA($1:$7);1));0)

Ésta es una función matricial y debe ser introducida pulsando simultáneamente Ctrl+Mayúsculas+Enter

4 – contar cuántos números hay en el código
Finalmente necesitamos contar cuantas cifras componen la parte numérica del código, lo que hacemos con la función CONTAR

={CONTAR(1*EXTRAE(A1;FILA($1:$8);1))}

Ahora combinamos todo junto en esta fórmula matricial

=EXTRAE(A1;COINCIDIR(VERDADERO;ESNUMERO(1*EXTRAE(A1;FILA($1:$8);1));0);CONTAR(1*EXTRAE(A1;FILA($1:$8);1)))


Si queremos extraer las letras tenemos que modificar la fórmula

- cambiando el argumento VERDADERO en la fórmula a FALSO
- agregando la función LARGO para calcular la cantidad de letras a extraer, reatando de ella la función CONTAR

=EXTRAE(A1;COINCIDIR(FALSO;ESNUMERO(1*EXTRAE(A1;FILA($1:$9);1));0);LARGO(A1)-CONTAR(1*EXTRAE(A1;FILA($1:$9);1)))

En hoja 1 del cuaderno con el ejemplo podemos ver los distintos casos.

Solución con UDF (función definida por el usuario)

Cuando las letras y las cifras no están agrupadas en el código, el resultado de las fórmulas es incorrecto. Para esos casos, podemos escribir una función definida por el usuario.

En un módulo del editor de Vba escribimos este código


Function ext_num_let(celda As Range, tipo As Boolean)
Dim iX As Integer, resultado As String, temp As String

Select Case tipo
Case 1
For iX = 1 To Len(celda)
temp = Mid(celda, iX, 1)
If IsNumeric(temp) Then
resultado = resultado & temp
End If
Next iX
Case Else
For iX = 1 To Len(celda)
temp = Mid(celda, iX, 1)
If Not IsNumeric(temp) Then
resultado = resultado & temp
End If
Next iX
End Select


ext_num_let = resultado

End Function


Esta función usa dos argumentos:
- Celda: la celda que contiene el código
- Tipo: 1 para extraer cifras, 0 para extraer letras



Technorati Tags:

24 comments:

F3R 05 febrero, 2010 03:21  

excelente trabajo !!! mil gracias por compartir la información, me salvaste la vida con algo para el trabajo, justo estan todos de vacaciones y necesitaba hacer justo esto para mañana.

gracias

Anónimo,  04 noviembre, 2010 18:58  

No me funciona me da #N/A

Jorge L. Dunkelman 04 noviembre, 2010 19:54  

Descargá el cuaderno con el ejemplo y fijate qué es lo que estás haciendo mal.

Anónimo,  10 noviembre, 2010 19:24  

el link del archivo no sirve da error

Jorge L. Dunkelman 11 noviembre, 2010 17:22  

Acabo de corregir el enlace. Estás invitado a descargar el cuaderno.
Además, asegurate de introducir la fórmula como fórmula matricial (apretando simultáneamente Ctrl+Mayúsculas+Enter).

FELIX 04 diciembre, 2010 01:38  

LA MACRO SI FUNCIONA PERO LA FORMULA NO FUNCIONA CON DATOS COMO ESTE A1C123

Jorge L. Dunkelman 04 diciembre, 2010 07:28  

Efectivamente, la fórmula supone que que los números y las letras están agrupados, como en ABC123 o 123ABC.

Marcos 14 abril, 2011 23:09  

Jorge, como estas, estoy ocupando la formula para extraer numeros, pero al momento de querer automatizarla en una macro no funciona, por lo que ¿como codifico en VB el Ctrl+Mayúsculas+Enter (matricial) para la formula?

Muchas gracias y que estes bien

Jorge L. Dunkelman 16 abril, 2011 14:29  

En la nota aparece una macro que hace esa tarea. ¿Por qué poner la fórmula en una macro?

Pablo Limeno,  25 julio, 2011 11:22  

Sin comentario !
Excelente trabajo, me ha ahorrado meses de estudio y me sirve para mi trabajo.
GRACIAS NUEVAMENTE

Jorge L. Dunkelman 24 septiembre, 2011 13:35  

Parece que por error he eliminao un comentario que decía

la verdad que muy buena la formula... pero en el caso de que no se encuentre ordenado el codigo? por ejemplo: 12ab34cd

O que se tengan codigos mas complicados como ser: AB.1234_list_01_90x90_SPA.FLV ??

La respuesta es usar la función UDF que aparece al final de la nota.

makuki 02 marzo, 2013 11:54  

Meter en un módulo esta función y usar la funcion.
Public Function GetNum(Valor As String) As Integer
Dim T As String
Dim Ch As String
For f = 1 To Len(Valor)
Ch = Mid(Valor, f, 1)
If IsNumeric(Ch) Then
T = T & Ch
End If
Next
GetNum = Val(T)
End Function

Jorge L. Dunkelman 02 marzo, 2013 19:33  

Hola Makuki, gracias por colaborar pero tengo que hacer dos observaciones:
1 - en el código falta declarar la variable "f", por lo que la función falla al intentar usarla (basta con agregar Dim f as ...);
2 - la función sólo extrae valores numéricos.

Celso Fonseca 14 junio, 2013 23:21  

oye me podrias en enviar el codigo ya corregido por favor nuevamente muchas gracias

Jorge Dunkelman 16 junio, 2013 00:04  

Todo lo que tienes que hacer es copiar el código que aparece en la nota.

Rolando Trigo 10 julio, 2013 02:03  

Hola, ¿como puedo extraer solamente numeros de 5 digitos que estan entre caracteres comunes pero en una sola linea de texto?. Y despues colocar estos numeros de 5 digitos en otro contexto en comun, por ejemplo entemedio de hola y chao.
Ej: abcd89808fghipoijifjdjabcd75009fghirwecbcbjabcd46892fghgfdgdabcd32987fghikllkjfd

extraer todos los numeros de 5 digitos y pasarlos entremedio de todos los hola y chao asi:

hola89808chao
hola75009chao
hola46892chao
hola32987chao

¿Es posible?. De antemano muchas gracias

CAMS-INAMBARI 12 julio, 2013 14:41  

Buena información!!
Tengo una caso muy practico, donde deseo extraer las primeras palabras de cada nombre científico:
Cedrela odorata = ceod

Jorge Dunkelman 13 julio, 2013 23:05  

Si el texto de las celdas sigue siempre esa estructura (dos palabras separadas por un espacio), podrías usar esta fórmula:

=IZQUIERDA(A1;2)&EXTRAE(A1;HALLAR(" ";A1)+1;2)

Jorge Dunkelman 14 julio, 2013 09:42  

Rolando,
la forma de hacerlo sería usando Vba (macros).

Juan R Garces 02 diciembre, 2013 17:16  

Buenos días. He copiado la formula "ext_num_let" en mi caso de la siguiente manera: =ext_num_let(D2,1) la hago matricial y nada, no funciona, me sale el error #¿NOMBRE? Tengo Excel 2010, ¿qué estaré haciendo mal? Muchas gracias. juanrgarces25@gmail.com

Jorge Dunkelman 02 diciembre, 2013 21:58  

Tienes que copiar el código de la función en un módulo del cuaderno que estás usando o del Personal. La función "ext_num_let" es una UDF (función definida por el usuario) y no se usa en forma matricial.
La que se usa en forma matricial es la fórmula que aparece en la primer parte de la nota.

También te recomiendo no poner tu mail en estos comentarios, excepto que te guste ver tu casilla de correo invadida de spam.

Antonio Mejía 14 julio, 2014 16:39  

Llevo semanas intentando hacer ésto con fórmulas, para unos códigos alfanuméricos medio complicados de mi empresa. Nunca se me ocurrió usar una función definida por el usuario! Mis respetos por tus conocimientos y un millón de gracias por tu aporte!!!!

Ale Gatito 21 octubre, 2014 18:18  

COMO PUEDO SACAR SOLAMENTE LOS NUMEROS DE UNA CELDA EJEMPLO 1236.21 USD

Jorge Dunkelman 22 octubre, 2014 17:42  

Usando alguna de las técnicas que muestro en esta nota.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP