En ocasiones tenemos celdas en nuestras hojas de Excel que contienen una cadena de texto alfanumérica y necesitamos extraer solamente la parte numérica contenida dentro de dicha cadena. La solución a este problema la obtendremos en dos pasos.
- Encontrar la posición inicial del número dentro del texto.
- Encontrar la longitud del número a extraer.
Por ejemplo, para extraer los números de una celda que tiene el texto ABC4567DEF debemos encontrar la posición del primer número que es la posición 4 y a partir de esa posición extraer 4 caracteres.
1. Encontrar la posición inicial del número
Comenzamos por encontrar el primer carácter dentro de la cadena que sea un dígito entre cero y nueve. Este procedimiento lo haremos utilizando tres funciones. La primera función a utilizar será la función ENCONTRAR donde el primer argumento será una matriz que contenga los nueve dígitos buscados:
=ENCONTRAR({0,1,2,3,4,5,6,7,8,9},A1)
Es muy importante que utilices el separador de listas configurado en tu equipo. Yo utilizo la coma (,) pero es probable que tu debas utilizar el punto y coma (;) para crear tanto la matriz de dígitos como para separar los argumentos de la función. Al terminar de escribir la fórmula pulsaré la combinación de teclas Ctrl + Mayús + Entrar ya que ésta es una fórmula matricial. Si la cadena de texto que tenemos en la celda A1 es “ABC4567DEF”, entonces esta fórmula nos devolverá una matriz con diez resultados:
Para cada dígito que hemos buscado obtendremos un resultado y por esa razón los primeros cuatro elementos del resultado son el error #¡VALOR! que significa que la función no encontró los dígitos 0,1,2 y 3. Sin embargo, para los dígitos 4,5,6, y 7 la función ENCONTRAR nos devuelve su posición dentro de la cadena. Para los dígitos 8 y 9 obtenemos de nuevo un error porque no se encuentran dentro de la cadena.
Si eres nuevo en Excel, es probable que el tema de fórmulas matriciales sea un tanto avanzado para ti, pero continúa leyendo porque al final tendremos la fórmula exacta que debes utilizar para resolver este problema.
Eliminar los errores del resultado
La segunda función que utilizaremos será la función SI.ERROR de manera que podamos eliminar los errores contenidos en la matriz de resultados. En caso de que la función SI.ERROR encuentre el error #¡VALOR! le pediremos que lo sustituya por una cadena de texto vacía:
=SI.ERROR(ENCONTRAR({0,1,2,3,4,5,6,7,8,9},A1),"")
De esta manera, la matriz devuelta ya no contiene los errores, sino que en su lugar tenemos una cadena de texto vacía donde antes aparecía el error #¡VALOR!:
Ahora que ya tenemos la matriz de resultados formada solo por números y cadenas vacías, podemos utilizar la función MIN para conocer la posición menor dentro de la cadena donde se encuentra un dígito.
Obtener el valor mínimo
Solo debemos agregar la función MIN a la fórmula anterior para obtener la posición donde comienza el número dentro de la cadena alfanumérica.
=MIN(SI.ERROR(ENCONTRAR({0,1,2,3,4,5,6,7,8,9},A1),""))
Ya que esta fórmula me devuelve un solo resultado, será la fórmula que introduciré en la celda B2 para obtener la posición inicial. Recuerda que debemos pulsar las teclas Ctr + Mayús + Entrar al terminar de introducir la fórmula.
Este resultado es correcto porque nos indica que el primer dígito está ubicado en la posición 4 de nuestra cadena alfanumérica. Con esto hemos solucionado la primer parte del problema, ahora solo nos resta saber la cantidad de dígitos a extraer.
2. Encontrar la longitud del número
Para obtener la cantidad de dígitos a extraer utilizaremos también tres funciones de Excel y el método para obtener la solución será utilizar también una fórmula matricial. El primer paso es obtener cada carácter de manera individual con la función EXTRAE:
=EXTRAE(A1,FILA($1:$99),1)
La parte más interesante de esta fórmula es el segundo argumento de la función EXTRAE donde debemos colocar la posición del carácter que deseamos extraer. Para ese segundo argumento utilizo la función FILA con el rango $1:$99 lo cual hará que la función EXTRAE me devuelva cada carácter de la cadena original como parte de una matriz hasta un máximo de 99 caracteres.
He colocado el número 99 pensando que no tendremos una cadena de texto mayor a esa longitud, pero si tienes una cadena más grande será suficiente con remplazar el número 99 por un número más grande. Cada elemento de la matriz de resultados será de tipo texto pero necesito convertirlo en número para poder contar cada elemento numérico correctamente, por lo que debo agregar una multiplicación por uno a la fórmula anterior:
=1*EXTRAE(A1,FILA($1:$99),1)
Al hacer la multiplicación, los valores en la matriz de resultados se modifican. Aquellos caracteres que no son números devuelven un error, pero aquellos que sí son números permanecen con su valor numérico dentro de la matriz:
Esta multiplicación era necesaria porque ahora utilizaré la función CONTAR para saber cuántos elementos de la matriz de resultado son números. La fórmula a utilizar es la siguiente:
=CONTAR(1*EXTRAE(A1,FILA($1:$99),1))
No olvides que debemos pulsar las teclas Ctrl + Mayús + Entrar al terminar de introducir la fórmula. Yo colocaré esta fórmula en la celda C1 donde obtengo el siguiente resultado:
De esta manera concluimos con la segunda parte de la solución. Ahora solo nos resta utilizar ambos resultados para extraer el número completo.
Extraer números de una celda en Excel
Ya hemos solucionado la parte más compleja de este problema. Solo debemos utilizar la función EXTRAE para extraer los números de la cadena de texto que se encuentra en la celda A1. La fórmula es muy sencilla:
Para probar que esta técnica funciona para cualquier otra cadena aplicaremos nuestras fórmulas a varias celdas que contienen una cadena alfanumérica:
Para finalizar te mostraré la fórmula integrada, es decir, la fórmula que nos ayuda a extraer números de una celda en un solo paso y que es simplemente la integración de las fórmulas desarrolladas anteriormente:
=EXTRAE(A2, MIN(SI.ERROR(ENCONTRAR({0,1,2,3,4,5,6,7,8,9},A2),"")), CONTAR(1*EXTRAE(A2,FILA($1:$98),1)))
Recuerda que esta es una fórmula matricial por lo que debemos pulsar la combinación de teclas Ctrl + Mayús + Entrar. El resultado será el mismo que hemos obtenido con las fórmulas anteriores.
Como puedes observar en la columna E, los números extraídos siguen siendo texto. Si deseas convertirlos en números se puede multiplicar el resultado de la fórmula anterior por 1 de manera puedan ser incluidos en cálculos numéricos. Es importante mencionar que esta fórmula funciona solamente en caso de tener un solo número (de varios dígitos) dentro de una cadena alfanumérica y no considera los puntos decimales que pudiera tener.
Este artículo se ha enfocado en encontrar una solución utilizando funciones de Excel pero es posible obtener resultados similares utilizando macros lo cual seguramente será el tema de alguna publicación futura. Mientras tanto descarga el libro de trabajo y experimenta con los ejemplos desarrollados en este artículo.
Artículos relacionados
Extraer contenido de una celda en Excel