martes, abril 24, 2007

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

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.

Actualización: Power Query nos permite hacer la tarea con más facilidad. Ver este post.

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:

49 comentarios:

  1. 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

    ResponderBorrar
  2. No me funciona me da #N/A

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

    ResponderBorrar
  4. el link del archivo no sirve da error

    ResponderBorrar
  5. 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).

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

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

    ResponderBorrar
  8. 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

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

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

    ResponderBorrar
  11. 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.

    ResponderBorrar
  12. 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

    ResponderBorrar
  13. 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.

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

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

    ResponderBorrar
  16. 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

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

    ResponderBorrar
  18. 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)

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

    ResponderBorrar
  20. 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

    ResponderBorrar
  21. 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.

    ResponderBorrar
  22. 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!!!!

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

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

    ResponderBorrar
  25. Si quiero extraer una agrupación de 10 números dentro de una linea me sirve? pero solo la primer agrupación de 10 números, si llega a tener mas no,

    Ejemplo:

    DEPOSITO VENTAS NETAS DE TAR POR PRONAD Referencia Nœmerica: 0001049191 Autorizaci—n: 00029725

    Si esa fuera la linea quiero extraer solamente los primeros que aparecen: 0001049191

    Gracias !! por toda su ayuda !!

    ResponderBorrar
  26. Jessica, en tu caso me parece que sería más fácil y practico usar Texto en Columnas, suponiendo que cada línea tiene la misma estructura. Tambien podrías usar Texto en Columnas usando el espacio como separador y eliminando las columnas innecesarias.

    ResponderBorrar
  27. Hola, quisiera saber cómo poder extraer el último caracter numérico contado de izquierda a derecha de una cadena alfanumérica de una celda, cuya longitud de texto es variable, por ejemplo:
    En la serie AA-001-AA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie 2-AA-001A, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie 02-AAA-01, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie AAA-001-A, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie A-001-AA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie A-2A-01, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie 02-AA-1, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).
    En la serie A-01-AAA, el último carácter numérico contado de izquierda a derecha corresponde al número uno (1).

    ResponderBorrar
  28. Hola

    podrías adaptar la UDF que aparece en el post de esta manera

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

    For iX = 1 To Len(celda)
    temp = Mid(celda, iX, 1)
    If IsNumeric(temp) Then
    resultado = resultado & temp
    End If
    Next iX

    If Len(resultado) = 0 Then
    ext_num_let = ""
    Else
    ext_num_let = CDec(Right(resultado, 1))
    End If

    End Function



    ResponderBorrar
  29. Gran aporte con esa UDF.enhorabuena por su labor!

    ResponderBorrar
  30. Hola Jorge, lo que requiero es extraer de una cantidad determinada de celdas de una columna en un libro de Excel un grupo determinado de valores alfanumericos, luego estos valores que cumplirian con una condicion determinada, una vez extraidos de las celdas en la matriz colocarlos en una nueva columna, identificada con un encabezado, a los valores de esta columna nueva, si cumplen con una condicion, asignarles una descripcion alfanumerica, y luego si cumplen con esta descrpcion alfanumerica, asignarles un codigo numerico que se asignaria a estos ultimos valores, si cumplen con una condicion dada.

    Se podria hacer esto con este codigo, o pordrias darme un codigo similar para poder lograr esto ?

    Gracias de antemano.

    ResponderBorrar
  31. Hola Félix,

    por favor fijate en lo que pongo en el enlcae Ayuda (en la parte superior del blog).

    ResponderBorrar
  32. Hola, Jorge, primero muchas felicidades por tu blog. Es un pozo infinito de sabiduría!

    A ver que idea se podría usar para extraer los números que están entre paréntesis, como este caso: L1AM2N1P1(2)/L1AM2N3P1(4)/LBM1N4P5(6)

    Seguramente será muy fácil, pero yo soy muy torpe!

    Gracias, de antemano.

    ResponderBorrar
  33. Si nos abstenemos de usar Vba, la solución sería usar Texto en columnas, primero usando ( como separador, para eliminar la primera parte de la cadena de texto y luego usar ) para ir extrayendo los números.

    ResponderBorrar
  34. hola esta es mi consulta.
    Ejemplo:
    - hugdtrfs2017jhjfgjl
    - 1475896352017458
    - jolyhg2017jryi35,
    entonces yo quiero solo sacar e otro solo los numeros que digan 2017, pero tengo una lista inmensa...dado el ejemplo

    ResponderBorrar
  35. Por favor, reescribe la frase: "entonces yo quiero solo sacar e otro solo los numeros que digan 2017".
    No logro entender lo que consultas.

    ResponderBorrar
  36. Hola Jorge
    ¿Cómo puedo extarer solo las consonantes de una palabra o palabras en una celda?

    ResponderBorrar
  37. Hola, Excel no tiene una función (o Vba un método) para reconocer consonantes. Por eso no veo ninguna forma práctica de hacerlo. Podrías tal vez adaptar el código de la UDF usando los códigos ASCII de las consonantes.

    ResponderBorrar
  38. Hola, y como se pueden extrarer digitos NO consecutivos de una celda de excel?
    MID o EXTRAE me devuelven una selección de dígitos de la celda pero solo si son consecutivos. Ejemplo CELDA 2934, necesito que me devuelva los dígitos 1o, 2o y 4o. Hasta ahora lo que hago es LEFT para obtener 29, RIGHT para obtener 4 y a posteriori CONCATENAR los dos resultado hasta obtener 294.
    Me sería de mucha utilidad.
    GRACIAS

    ResponderBorrar
  39. La celda ¿contiene sólo números o números y letras?
    Si contiene sólo números, tu solución es correcta. Podrías combinar todo en una única fórmula.
    Si las celdas contienen números y letras lo mejor sería usar una macro. La macro evaluaría cada símbolo en la celda; si no es número lo ignoraría; si es número lo pondría en la cadena a condición que sea el primero, el segundo o el cuarto que encuentra.

    ResponderBorrar
  40. Solo contiene números, puedo entonces conseguir lo que necessito con una sola fórmula? Cual sería?

    ResponderBorrar
  41. Como decía, combinando todo en una fórmula. En tu caso:

    =LEFT(celda,2)&MID(celda,4,1)

    y para convertirlo eb valor

    =VALUE(LEFT(celda,2)&MID(celda,4,1))

    ResponderBorrar
  42. Muchísimas gracias Jorge!!! No sabía q había la posibilidad d hacer esto! Sencillo y d gran ayuda, seguro lo usaré en otras fórmulas. Mil gracias y feliz miércoles.

    ResponderBorrar
  43. como hago para obtener lo siguiente:
    celda 1 19:00 PM
    celda 2 03:00 AM
    resultado esperado 7 horas
    Resultado mostrado 4 PM

    ResponderBorrar
  44. Hola, como no se trata de extraer números o letras de una cadena de texto te sugiero que veas este post sobre manejo de fechas y horas en Excel.
    ¿Por qué el resultado esperado es 7 horas? La diferencia de tiempo entre 01:19 PM y 02:03 AM (que es el día siguiente) es 12 horas y 44 minutos. Para calcularlo tienes que usar la función RESIDUO como muestro en este post.

    ResponderBorrar
  45. Hola, como podria extraer el numero telefonico sin codigo de pais? ejemplo:

    +51 987654321 solo quiero extraer el 987654321

    Gracias

    ResponderBorrar
  46. Si el patrón de los datos es constante, es decir, siempre hay un espacio entre el código de país y el número puedes usar sencillamente "Texto en columnas" en Datos-Herramientas de datos.

    ResponderBorrar
  47. Hola Jorge;

    Necesito sacar el texto de largos diferentes, como lo puedo hacer?, cuál sería la fórmula?

    CAMINOACASA4
    IPERSAN3
    RELAX49
    MENSHERITAGE32
    COLGATE77
    SURKANA6
    COOLKIDS12
    Façonnable55
    LNTHINGS22
    HCONCEPT1
    CORTEFIEL20
    MULTIBRAND9
    MMILANO1
    ILLYCAFE15
    OD_ALCORTA1
    JSIMORRA21
    SELENE21


    Gracias

    ResponderBorrar
    Respuestas
    1. Fijate en esta nota donde muestro como usar Power Query para hacerlo.
      También la técnica que muestro en esta otra nota.

      Borrar
  48. Maravilloso, me funciono perfectamente!!! Muchas gracias

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.