Encontrar y Extraer Números de una Cadena – Excel y Google Sheets
Descargar el Libro de Ejemplo
Este tutorial le mostrará cómo encontrar y extraer números del interior de una cadena en Excel y Google Sheets.
Encontrar y Extraer Números de una Cadena
A veces sus datos pueden contener números y texto y desea extraer los datos numéricos.
Si la parte numérica está a la derecha o a la izquierda de la cadena, es relativamente fácil dividir el número y el texto . Sin embargo, si los números están dentro de la cadena, es decir, entre dos cadenas de texto, tendrá que utilizar una fórmula mucho más complicada (que se muestra a continuación).
UNIRCADENAS – Extraer Números en Excel 2016+
En Excel 2016 se introdujo la Función UNIRCADENAS, que puede extraer los números de cualquier parte de la cadena de texto. Aquí está la fórmula:
=UNIRCADENAS("";VERDADERO;SI.ERROR((EXTRAE(B3;FILA(INDIRECTO("1:"&LARGO(B3)));1)*1);""))
Veamos cómo funciona esta fórmula.
Las funciones FILA, INDIRECTO y LARGO devuelven una matriz de números correspondientes a cada carácter de su cadena alfanumérica. En nuestro caso, «Monday01Day» tiene 11 caracteres, por lo que la función FILA devolverá la matriz {1;2;3;4;5;6;7;8;9;10;11}.
=UNIRCADENAS("";VERDADERO;SI.ERROR((EXTRAE(B3;{1;2;3;4;5;6;7;8;9;10;11};1)*1);""))A continuación, la Función EXTRAE extrae cada carácter de la cadena de texto, creando una matriz que contiene su cadena original:
=UNIRCADENAS("";VERDADERO;SI.ERROR(({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a";"y"}*1);""))Multiplicando cada valor de la matriz por 1 se creará una matriz que contendrá errores Si el carácter de la matriz era texto:
=UNIRCADENAS("";VERDADERO;SI.ERROR(({#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;0;1;#¡VALOR!;#¡VALOR!;#¡VALOR!});""))A continuación, la Función SI.ERROR, elimina los valores de error:
=UNIRCADENAS("";VERDADERO;{"";"";"";"";"";"";0;1;"";"";""})Dejando sólo la Función UNIRCADENAS que une los números restantes.
Note que la fórmula le dará todos los caracteres numéricos juntos de su cadena. Por ejemplo, si su cadena alfanumérica es Monday01Day01, el resultado será 0101.
Extraer Números – Antes de Excel 2016
Antes de Excel 2016, podías utilizar un método mucho más complicado para extraer números del texto. Puedes utilizar la Función ENCONTRAR junto con las funciones SI.ERROR y MIN para determinar tanto la primera posición de la parte numérica como la primera posición de la parte de texto después del número. Entonces simplemente extraemos la parte numérica con la función EXTRAE.
=EXTRAE(B3;MIN(SI.ERROR(ENCONTRAR({0\1\2\3\4\5\6\7\8\9};B3);999999999));MIN(SI.ERROR(
HALLAR({"a"\"b"\"c"\"d"\"e"\"f"\"g"\"h"\"I"\"j"\"k"\"l"\"m"\"n"\"o"\"p"\"q"\"r"\"s"\"t"\"u"\"v"\"w"\"x"\"y"\"z"};
B3;MIN(SI.ERROR(ENCONTRAR({0\1\2\3\4\5\6\7\8\9};B3);999999999)));999999999))-
MIN(SI.ERROR(ENCONTRAR({0\1\2\3\4\5\6\7\8\9};B3);999999999)))
Nota: Esta es una fórmula Array, debes ingresar la fórmula presionando CTRL + MAYÚSC + ENTRAR, en lugar de sólo ENTRAR.
Veamos paso a paso como funciona esta fórmula.
Encontrar el Primer Número
Podemos usar la Función ENCONTRAR para localizar la posición inicial del número.
=MIN(SI.ERROR(ENCONTRAR({0\1\2\3\4\5\6\7\8\9};B3);999999999))
Para el argumento texto_buscado de la función ENCONTRAR, utilizamos la constante de matriz {0\1\2\3\4\5\6\7\8\9}, lo que hace que la Función ENCONTRAR realice una búsqueda independiente para cada valor de la constante de matriz.
El argumento dentro_del_texto de la Función ENCONTRAR en nuestro caso es Monday01Day, en el que se puede encontrar 1 en la posición 8, y 0 en la posición 7, por lo que nuestra matriz de resultados será:
{7\8\#¡VALOR!\#¡VALOR!\#¡VALOR!\#¡VALOR!\#¡VALOR!\#¡VALOR!\#¡VALOR!\#¡VALOR!}.
Usando la Función SI.ERROR reemplazamos los errores #VALOR por 999999999. Luego simplemente buscamos el mínimo dentro de este array y obtenemos por tanto el lugar del primer número (7).
Ten en cuenta que la fórmula anterior es una fórmula de matriz, tienes que pulsar Ctrl+Shift+Enter para activarla.
Encontrar el Primer Carácter de Texto Después del Número
De forma similar a la localización del primer número dentro de la cadena, utilizamos la función HALLAR para determinar dónde comienza de nuevo el texto después del número haciendo buen uso también del argumento núm_inicial de la función.
=MIN(SI.ERROR(HALLAR({"a";"b";"c";"d";"e";"f";"g";"h";"I";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z"};B3;C3);999999999))
Esta fórmula funciona de forma muy similar a la anterior utilizada para localizar el primer número, sólo que utilizamos letras en nuestra constante de array y por tanto los números provocan errores #VALOR. Tenga en cuenta que comenzamos a buscar sólo después de la posición determinada para el primer número (éste será el argumento núm_inicial) y no desde el principio de la cadena.
No olvide pulsar Ctrl+Mayúsc+Entrar para utilizar la fórmula anterior.
No queda más que unir todo esto.
Extraer Número de Dos Textos
Una vez que tenemos la posición inicial de la parte numérica y el comienzo de la parte textual a continuación, simplemente utilizamos la Función EXTRAE para extraer la parte numérica deseada.
=EXTRAE(B3;C3;D3-C3)
Otro Método
Sin explicarlo con más detalle, también puedes utilizar la siguiente fórmula compleja para obtener el número del interior de una cadena.
=SUMAPRODUCTO(EXTRAE(0&B3;K.ESIMO.MAYOR(INDICE(
ESNUMERO(--EXTRAE(B3;FILA(INDIRECTO("1:"&LARGO(B3)));1))*
FILA(INDIRECTO("1:"&LARGO(B3)));0);FILA(INDIRECTO("1:"&
LARGO(B3))))+1;1)*10^FILA(INDIRECTO("1:"&LARGO(B3)))/10)
Tenga en cuenta que esta fórmula le dará 0 cuando no haya ningún número en la cadena y que los ceros a la izquierda se omitirán.
Encontrar y Extraer un Número de una Cadena en Google Sheets
Los ejemplos anteriores funcionan de la misma manera en Google Sheets que en Excel.
