En días pasados he visto ésta consulta de forma recurrente. Lo que se intenta lograr es extraer de una celda sólo los números de una cadena de texto, sin importar en que posición aparezcan. Como siempre, en Excel existen varias formas de lograr una tarea. Hoy veremos como hacerlo de cuatro formas, las cuales, siguiendo un orden de dificultad de la más sencilla a la más compleja, son:

1. Utilizando relleno rápido.
2. Con funciones matriciales anidadas.
3. Mediante una función definida por el usuario.
4. Con funciones normales anidadas.

Cada opción tiene sus ventajas y desventajas, al final ustedes como usuarios deben decidir cual se adapta mejor a su proyecto. Como veremos, la clave está en entender como están trabajando en conjunto todas las fórmulas y funciones que aplicaremos.

Utilizando relleno rápido

Aunque tiene ciertas limitaciones, la verdad es que no existe una forma más sencilla de hacerlo. Relleno rápido es una función incluida a partir de Excel 2013 que detecta patrones en los cambios que vas realizando y te sugiere auto completar tus entradas siguientes.

Presta atención a las instrucciones que se muestran a continuación para que veas lo fácil que es aplicar el Relleno Rápido.

Aunque Excel es bastante inteligente para detectar que sólo queremos extraer los números en el caso que planteamos, cuando los datos empiezan a ser más aleatorios dentro del texto, los cambios no son detectados adecuadamente. Para que practiques el uso de esta función te dejamos el archivo que hemos utilizado en nuestro ejemplo aquí.

De igual forma te recuerdo que si aún no estás al tanto de los avances en las versiones de Excel más recientes, lo mejor es que actulices tu versión de Office a cualquiera de las opciones que te ofrecemos en la sección «Lo mejor de Excel en Amazon», y ya que estas de paso te consultes alguno de los mejores manuales sobre Excel que cubren temas como Relleno Rápido (Flash Fill) y muchos más.

Extraer texto con funciones matriciales

Aprovechando el poder de las fórmulas matriciales, veamos a continuación una que se vale de seis (6) funciones anidadas para completar la tarea. La fórmula extrae los números del texto que tengamos en la celda A2 y es una adaptación de varias fórmulas que podemos ubicar fácilmente en línea:

=--UNIRCADENAS("",1,SI.ERROR(--EXTRAE(A2,FILA(INDIRECTO("1:"&LARGO(A2))),1),""))

Si la copias y pegas en tu hoja, recuerda modificar la celda A2 por tu celda objetivo e introducirla presionando Ctrl + Mayús (Shift) + Entrar (Enter). En nuestro archivo de ejemplo quedaría como sigue:

Como observamos, estamos utilizando las siguientes funciones:

1. UNIRCADENAS
2. SI.ERROR
3. EXTRAE
4. FILA
5. INDIRECTO
6. LARGO

Veamos en detalle como funciona esta fórmula. Analizando la fórmula de dentro hacia afuera, primero tenemos la función LARGO. Esta función nos da el total de caracteres que posee la celda desde la cual queremos extraer los números (celda objetivo). El uso de esta función es lo que permite trabajar con textos de prácticamente cualquier extensión. Este total nos arroja catorce (14) con lo cual la fórmula va quedando así:

=--UNIRCADENAS("",1,SI.ERROR(--EXTRAE(A2,FILA(INDIRECTO("1:"&14)),1),""))

Seguidamente vemos que tenemos una unión con el símbolo et (&), el cual concatena la cadena «1:» con el resultado anterior. Esto genera lo siguiente:

=--UNIRCADENAS("",1,SI.ERROR(--EXTRAE(A2,FILA(INDIRECTO("1:14")),1),""))

Luego vemos que sigue la función INDIRECTO, la cual, es este caso, nos sirve para construir una referencia válida que pueda ser reconocida por la función FILA. Si la evaluamos tendriamos lo siguiente:

=--UNIRCADENAS("",1,SI.ERROR(--EXTRAE(A2,FILA($1:$14)),1),""))

Aquí es donde nuestra función se vuelve interesante, ya que al dar a la función FILA la referencia construida en el paso anterior, la misma nos devolverá una matriz de números continuos hasta el total de los caracteres de la celda objetivo. Su evaluación nos arroja lo siguiente:

=--UNIRCADENAS("",1,SI.ERROR(--EXTRAE(A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14}),1),""))

La siguiente fórmula EXTRAE es la que hace el trabajo importante de esta fórmula. Al evaluarla creará una matriz donde los valores serán cada uno de los caracteres que forman parte de la celda objetivo. Esto es lo que permite evaluar cada caracter de forma separada para saber si es un número. El doble signo negativo es vital para obtener nuestro resultado. Al negar doblemente valores no numéricos (es decir los caracteres de texto), esta porción de la fórmula arrojará un error, pero mantendrá sólo los números. Veamos estos dos pasos de forma separada:

Resultado de EXTRAE:

=--UNIRCADENAS("",1,SI.ERROR(--{"M";"F";"D";"F";"1";"5";"5";"J";"D";"K";"F";"F";"6";"6"},""))

Resultado del doble signo negativo:

=--UNIRCADENAS("",1,SI.ERROR({#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;1;5;5;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;6;6},""))

En este caso, vemos como el doble signo afectó a la matriz de valores creado por la función EXTRAE. En este punto, sólo quedan los números. Los caracteres que antes eran texto ahora son un error estándar de Excel.

Con la función SI.ERROR que continúa, sustituiremos los errores por «», es decir un texto vacío. Su evaluación quedaría así:

=--UNIRCADENAS("",1,{"";"";"";"";1;5;5;"";"";"";"";"";6;6})

Ya casi tenemos nuestro resultado. Para finalizar esta fórmula, con la función UNIRCADENAS (disponible sólo para Office 365), uniremos los valores de la matriz, omitiendo los vacíos:

=--"15566"

Sin embargo, como el resultado es una cadena de texto, utilizaremos nuevamente el doble negativo para convertirla en número. La evaluación final arroja el resultado que esperamos:

=15566

Con esto hemos logrado extraer los números de nuestra cadena de texto utilizando una fórmula matricial.

Otra variante sobre la misma fórmula es incluir espacios para cada sección de caracteres encontrado en la celda objetivo. Por ejemplo, si tomamos en cuenta el texto de la celda A2 en el ejemplo, el resultado sería «155 66» como texto, en lugar de 15566. La fórmula sería la siguiente:

=ESPACIOS((UNIRCADENAS("",VERDADERO,SI.ERROR(VALOR(EXTRAE(A2,FILA(INDIRECTO(1&":"&LARGO(A2))),1))," "))))

Sus resultados se muestran a continuación:

En caso que cortes y pegues la fórmula para tu libro, te recordamos actualizar tu celda objetivo e insertar la fórmula con Ctrl + Mayús + Entrar. Si no lo haces, la fórmula no funcionará.

Recuerda que en nuestra sección «Lo mejor de Excel en Amazon», podrás encontrar el mejor libro sobre funciones matriciales que existe a la fecha.

Extraer texto con función definida por el usuario

La siguiente solución proviene de los foros de Mr. Excel. En este caso, con una breve función definida por el usuario podremos lograr nuestro objetivo. La función original es del usuario schilern:

'***FUNCION EXTRAEX por Rob Schiele***
'Descripción: Separa datos numéricos y alfabéticos (por ejemplo: Alpha 123, Alpha123)
'             Verdadero ó 1 devolverá los datos alfabéticos, Falso ó 0 devolverá los datos numéricos
'Ejemplo:   El valor en A1 es Rob12Schiele34
'           =EXTRAEX(A1,1)      Devuelve:    RobSchiele
'           =EXTRAEX(A1,0)      Devuelve:    1234
Function EXTRAEX(txt As String, flg As Boolean) As String
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(flg = True, "\d+", "\D+")
    .Global = True
    EXTRAEX = .Replace(txt, "")
End With
End Function

Esta función utiliza expresiones regulares en VBA para lograr la separación de los números. Vale destacar que incluso va más allá, pues provee un segundo parámetro para especificar si queremos extraer los números (el argumento es 0) o el texto (el argumento es 1), lo cual la hace mucho más flexible que las soluciones anteriores.

Para introducir este código en tu archivo, presiona Alt + F11. Esto abre la ventana del editor de VBA. Luego debes insertar un nuevo módulo con «Alt, i, m». En la ventana en blanco que se abre del lado derecho, debes pegar el texto anterior.

Deberías terminar con algo similar a esto:

Ahora puedes cerrar el editor de VBA y utilizar la formula EXTRAEX como cualquier otra función de Excel según observarás a continuación:

De todas las formas aquí presentadas, consideramos que ésta es la más poderosa. La desventaja está en que si compartimos nuestro archivo, algunos usuarios pueden quedar confundidos por el uso y resultados de esta función, ya que la misma no estará documentada en Excel de ninguna forma. De la misma manera, no estará disponible para ellos en otros archivos, salvo que la desarrollemos como un complemento.

¿Quieres aprender más sobre VBA? Te recomendamos dos recursos que te darán todas la herramientas para convertirte en un usuario avanzado de Excel, además de mantener una referencia fuera de línea a la mano durante tus desarrollos. El primero es Excel VBA  – 24 Hour Trainer del genial Tom Urtis, y para cuando ya quieras probar conceptos más avanzados tienes disponible Excel 2016 Power Programming with VBA de Michael Alexander y Dick Kusleika. Allí podrás aprender todo lo que necesitas sobre VBA.

Extraer texto con funciones no matriciales

La fórmula no matricial que veremos hoy es una modificación realizada por Mike Girvin (Excel MVP) de las fórmulas propuestas por los usuarios Ron Coderre (Excel MVP) y Domenic de los foros de Mr. Excel. Tiene ya varios años publicada y de seguro la encontrarás en tus búsquedas al intentar realizar esta tarea.

Para lograr la extracción de los números se utilizan ocho (8) funciones:

1. SUMAPRODUCTO
2. K.ÉSIMO.MAYOR
3. EXTRAE
4. ESNUMERO
5. INDICE
6. INDIRECTO
7. FILA
8. LARGO

Cada función indicada arriba tiene un hipervínculo a su descripción completa en caso que la desconozcas. La fórmula es la siguiente:

=SUMAPRODUCTO(EXTRAE(0&A2,K.ESIMO.MAYOR(INDICE(ESNUMERO(--EXTRAE(A2,FILA(INDIRECTO("1:"&LARGO(A2))), 1)) * FILA(INDIRECTO("1:"&LARGO(A2))), 0),FILA(INDIRECTO("1:"&LARGO(A2))))+1, 1) * 10^FILA(INDIRECTO("1:"&LARGO(A2)))/10)

Puedes cortar y pegar en tu hoja directamente, esta vez no necesitas presionar Ctrl + Mayús + Entrar, pues es una fórmula regular.

La hemos dejado para el final porque realiza operaciones matemáticas mucho más complejas que las realizadas por la función matricial vista anteriormente y su proceso de cálculo no es tan evidente. La explicación que sigue la dejamos para los más curiosos.

Si observamos detalladamente esta fórmula veremos que tiene una sección que ya hemos utilizado en la fórmula matricial analizada anteriormente. Dicha estructura es la siguiente

FILA(INDIRECTO("1:"&LARGO(A2)))

Cada vez que tengamos esa expresión el resultado será una matriz con los números desde uno (1) hasta el largo total de nuestra celda objetivo (En nuestro ejemplo, resultará en una matriz de catorce (14) elementos desde uno (1) hasta catorce (14)).

De igual forma, ya sabemos que la función EXTRAE crea una matriz cuyos elementos son los caracteres de nuestra celda objetivo. También hemos visto que el doble negativo convierte los elementos no numéricos ya extraidos en error.

Esa matriz de valores con error, es lo que alimenta a la función ESNUMERO, la cual arrojará una matriz de valores lógicos Verdaderos (números) y Falsos (errores). Esto nos dejará a la función INDICE como sigue:

INDICE({FALSO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO} * {1;2;3;4;5;6;7;8;9;10;11;12;13;14}, 0)

La multiplicación de la matriz de valores lógicos por la matriz con el total de elementos, resultará en la posición de cada número dentro de la celda objetivo. En este momento, nuestra fórmula va quedando así:

=SUMAPRODUCTO(EXTRAE(0&A2,K.ESIMO.MAYOR({0;0;0;0;5;6;7;0;0;0;0;0;13;14},{1;2;3;4;5;6;7;8;9;10;11;12;13;14})+1, 1) * 10^{1;2;3;4;5;6;7;8;9;10;11;12;13;14}/10)

Seguidamente, la función K.ÉSIMO.MAYOR nos devolverá una matriz las posiciones de cada valor ordenadas de forma descendente. Es decir, la fórmula buscará el primer valor más grande dentro de la matriz provista por INDICE (resultando en 14), luego buscará el segundo mayor valor (resultando en 13), hasta cubrir los 14 caracteres.

El resultado hasta ahora quedaría asi:

=SUMAPRODUCTO(EXTRAE(0&A2,{14;13;7;6;5;0;0;0;0;0;0;0;0;0}+1, 1) * 10^{1;2;3;4;5;6;7;8;9;10;11;12;13;14}/10)

Continúa ahora una función EXTRAE, y aquí debemos prestar atención a lo que sucede. Si observamos la matriz de valores descendentes que tenemos hasta ahora, sus valores después del quinto elemento son cero.  Así no podemos incluirla como argumento de EXTRAE, ya que la misma generará un error al no existir una posición inicial cero. Para salvar este obstáculo se suma uno (1) a dicha matriz.

=SUMAPRODUCTO(EXTRAE(0&A2,{15;14;8;7;6;1;1;1;1;1;1;1;1;1}, 1) * 10^{1;2;3;4;5;6;7;8;9;10;11;12;13;14}/10)

Aunque resolvimos el problema de la posición inicial cero para EXTRAE, ahora tenemos otro obstáculo porque hemos incrementado la posición un dígito. Esa es la razón de concatenar el cero (0) al inicio de la celda objetivo. De esa forma compensamos el valor que añadimos. Ahora nuestro valor objetivo ahora tendrá quince (15) caracteres y las posiciones iniciales se mantendrán sin problema.

Ahora si podremos evaluar la fórmula EXTRAE sin errores. El resultado sería el siguiente:

=SUMAPRODUCTO({"6";"6";"5";"5";"1";"0";"0";"0";"0";"0";"0";"0";"0";"0"} * 10^{1;2;3;4;5;6;7;8;9;10;11;12;13;14}/10)

Ya estamos cerca del final. Observemos que ya tenemos sólo los números como parte de la primera matriz. Veamos ahora lo que ocurre en la segunda sección de la fórmula (después del signo de multiplicación). Lo que se trata de hacer es ordenar los dígitos en las unidades correspondientes (decenas, centenas,…), pero partiendo desde el uno (1). Esto para lidiar con el cero (0) que hemos añadido al inicio de nuestro valor objetivo. En este caso estamos elevando el valor 10 a todas las potencias incluidas en la matriz de catorce dígitos y luego se divide entre diez (10). Esta operación genera lo siguiente:

=SUMAPRODUCTO({"6";"6";"5";"5";"1";"0";"0";"0";"0";"0";"0";"0";"0";"0"} * {1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000})

Ahora es momento que la función SUMAPRODUCTO realice su tarea. Esta función devuelve suma del producto de dos matrices. Si sólo incluimos una matriz, la fórmula devolverá la suma de sus elementos. Veamos el resultado de la multiplicación.

=SUMAPRODUCTO({6;60;500;5000;10000;0;0;0;0;0;0;0;0;0})

Como podemos observar, la suma de los valores de la matriz totaliza 15566, que es el resultado que estamos buscando.

Hemos incluido esta fórmula el final, porque su determinación, explicación y análisis son más complejos que todas las formas vistas anteriormente. Es la solución que incluye más funciones y de seguro es la más difícil de explicar a personas que no sean usuarios avanzados de Excel. Sin embargo, la hemos incluido y explicado porque es una fórmula que funciona perfectamente. Es ideal si no deseamos utilizar funciones matriciales o alguna de las alternativas anteriores.

Si has leído hasta aquí te felicito, el análisis de funciones es una de las mejores formas de aprender Excel. Esperamos puedas aplicar alguna de las soluciones planteadas cuando te enfrentes a la tarea de extraer números de cadenas de texto.