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:
- Descomponer la cadena alfanumérica en caracteres independientes.
- Determinar si hay algún número en la cadena descompuesta.
- Determinar la posición del número en la cadena alfanumérica.
- 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.
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.
