Extraer números de cadenas alfanuméricas


Icono de experto MVP

El autor de este artículo es Ashish Mathur, un experto MVP de Microsoft.

En el siguiente artículo explico una fórmula que extrae números de cadenas en las siguientes circunstancias:

  • Cuando los caracteres alfabéticos y numéricos están agrupados, como abc123 o 678sfr.
  • Cuando los caracteres alfabéticos y numéricos no están agrupados, como abc15tni.

Problema

Cómo extraer la parte numérica de una cadena alfanumérica. Por ejemplo: Si la celda A1 contiene la cadena "abc123", devolver el valor 123 en la celda B1.

Solución

La lógica que subyace a este problema es buscar y devolver el primer número de la cadena alfanumérica y después devolver sólo los números siguientes.

Algoritmo

La solución implica crear fórmulas que realicen las siguientes tareas:

  1. Descomponer la cadena alfanumérica en caracteres independientes.
  2. Determinar si hay algún número en la cadena descompuesta.
  3. Determinar la posición del número en la cadena alfanumérica.
  4. Contar los números de la cadena alfanumérica.

Consideraremos cada una de estas tareas por separado y, después, integraremos las fórmulas para obtener el resultado final.

Descomponer la cadena alfanumérica en caracteres independientes

Aquí utilizamos la función EXTRAE. EXTRAE devuelve un número específico de caracteres de una cadena de texto, empezando en la posición que se especifique, en función del número de caracteres definidos. La sintaxis de la función es:

EXTRAE(texto,núm_inicial,núm_caracteres)

  • Texto    La cadena de texto que contiene los caracteres que desea extraer.
  • Núm_inicial    La posición del primer carácter que desea extraer del texto. El primer carácter del texto tiene núm_inicial 1, y así sucesivamente.
  • Núm_caracteres    Especifica el número de caracteres que EXTRAE debe devolver del texto.

En nuestro ejemplo, la fórmula es:

=EXTRAE(A1,FILA($1:$9),1)

Esta fórmula descompone la cadena alfanumérica y coloca los caracteres (virtualmente) en filas diferentes de la hoja. Por ejemplo, en la cadena alfanumérica abc123, se descompondrían los seis caracteres.

 Nota   El número 9 puede aumentarse según corresponda a un número mayor para albergar cadenas más grandes. En este ejemplo, la longitud máxima de la cadena es 9.

Merece la pena tener en cuenta que al descomponer la cadena, "1", "2" y "3" estos caracteres se tratan como texto y no como números. Para convertir los números almacenados como texto en números, multiplique la fórmula por 1. Por ejemplo:

=1*EXTRAE(A1,FILA($1:$9),1)

Determinar si hay algún número en la cadena descompuesta

Aquí empleamos la función ESNUMERO, que determina si hay algún número en la cadena alfanumérica. La fórmula se convierte ahora en:

=ESNUMERO(1*EXTRAE(A1,FILA($1:$9),1))

Si hay algún número en la cadena, el resultado es VERDADERO; en caso contrario, es FALSO.

Determinar la posición del número en la cadena alfanumérica

Ahora determinaremos la posición del número buscando un valor VERDADERO en el resultado de la cadena descompuesta mencionada en el párrafo anterior. Aquí empleamos la función COINCIDIR. La fórmula se convierte ahora en:

=COINCIDIR(VERDADERO,ESNUMERO(1*EXTRAE(A1,FILA($1:$9),1)),0)

 Importante   Esta fórmula debe escribirse como una matriz presionando CTRL+MAYÚS+ENTRAR.

Si la cadena es abc123, esta fórmula dará como resultado 4, que es la posición del primer carácter numérico en la cadena alfanumérica.

Contar los números de la cadena alfanumérica

La tarea ahora es contar los números de la cadena para determinar los caracteres que deben devolverse después del primer número de la cadena alfanumérica.

Como se ha mencionado anteriormente, los números almacenados como texto en la cadena alfanumérica pueden convertirse en números multiplicándolos por 1. Por ejemplo: =1*EXTRAE(A1,FILA($1:$9),1)

Después de convertir los números almacenados como texto en números, es posible contarlos utilizando la función CONTAR. Los números se pueden contar con la fórmula siguiente:

=CONTAR(1*EXTRAE(A1,FILA($1:$9),1))

Integrar la fórmulas

Ahora empleamos la función EXTRAE para integrar las distintas partes de la fórmula, como se muestra en el ejemplo siguiente.

=EXTRAE(A1,COINCIDIR(VERDADERO,ESNUMERO(1*EXTRAE(A1,FILA($1:$9),1)),0),CONTAR(1*EXTRAE(A1,FILA($1:$9),1)))

En lenguaje sencillo, podemos plantear el problema del modo siguiente: Determínese la posición del primer número en la cadena alfanumérica (en la celda A1). Devuélvase ese número y los números siguientes.

Para convertir los caracteres resultantes en números, multiplique la fórmula por 1. Aunque esta operación no es estrictamente necesaria, debe realizarse si va a efectuar operaciones matemáticas en el resultado. Ésta es la fórmula final especificada en la celda B1:

=1*EXTRAE(A1,COINCIDIR(VERDADERO,ESNUMERO(1*EXTRAE(A1,FILA($1:$9),1)),0),CONTAR(1*EXTRAE(A1,FILA($1:$9),1)))

 Importante   Debe escribir estas fórmulas como matrices presionando CTRL+MAYÚS+ENTRAR.

Más ejemplos

Para probar más exhaustivamente la fórmula, escriba los datos de la siguiente imagen en las celdas A1:A7 de una hoja en blanco.

Ejemplo de conversión de una cadena

Escriba la fórmula en la celda B1 y, a continuación, utilice Autorrellenar para copiar la fórmula en las celdas B2:B7. (No olvide presionar CTRL+MAYÚS+ENTRAR.)

Merece la pena mencionar aquí que si la cadena es yur09875reew y utiliza la fórmula que se multiplica por 1, el resultado de la columna B será 9875 y no 09875. Como 0*1=0, se omite el 0 y el resultado devuelto es 9875. Si desea que el resultado sea 09875, no multiplique toda la fórmula por 1.

 
 
Corresponde a:
Excel 2003