Descarga el libro de trabajo de ejemplo
Este tutorial le mostrará cómo encontrar y extraer un número dentro de una cadena de texto en Excel y Google Sheets.
Encontrar y extraer el número de la cadena
A veces, sus datos pueden contener números y texto y desea extraer los datos numéricos.
Si la parte del número está en el lado derecho o izquierdo 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, deberá utilizar una fórmula mucho más complicada (que se muestra a continuación).
TEXTJOIN - Extraiga números en Excel 2016+
En Excel 2016 se introdujo la función TEXTJOIN, que puede extraer los números de cualquier parte de la cadena de texto. Aquí está la fórmula:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) * 1), "")) |
Veamos cómo funciona esta fórmula.
Las funciones ROW, INDIRECT y LEN devuelven una matriz de números correspondientes a cada carácter en 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}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1) * 1), "")) |
A continuación, la función MID extrae cada carácter de la cadena de texto, creando una matriz que contiene su cadena original:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "} * 1)," ")) |
Multiplicar cada valor en la matriz por 1 creará una matriz que contiene errores Si el carácter de la matriz era texto:
| 1 | = TEXTJOIN ("", VERDADERO, SI ERROR (({# ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; 0; 1; # ¡VALOR!; # ¡VALOR!; # VALOR !}), "")) |
A continuación, la función IFERROR elimina los valores de error:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Dejando solo la función TEXTJOIN que une los números restantes.
Tenga en cuenta que la fórmula le dará todos los caracteres numéricos juntos de su cadena. Por ejemplo, si su cadena alfanumérica es Monday01Day01, le dará 0101 como resultado.
Extraer números: antes de Excel 2016
Antes de Excel 2016, podía usar un método mucho más complicado para extraer números del texto. Puede utilizar la función BUSCAR junto con las funciones SI.ERROR y MIN para determinar tanto la primera posición de la parte del número como la primera posición de la parte del texto después del número. Luego simplemente extraemos la parte numérica con la función MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"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 de matriz, debe ingresar la fórmula presionando CTRL + MAYÚS + ENTRAR, en lugar de solo ENTRAR.
Veamos paso a paso cómo funciona esta fórmula.
Encontrar el número de puño
Podemos usar la función ENCONTRAR para ubicar la posición inicial del número.
| 1 | = MIN (SI ERROR (ENCONTRAR ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Para el argumento find_text de la función FIND, usamos la constante de matriz {0,1,2,3,4,5,6,7,8,9}, que hace que la función FIND realice una búsqueda separada para cada valor en la constante de matriz.
El argumento within_text de la función FIND en nuestro caso es Monday01Day, en el que 1 se puede encontrar en la posición 8 y 0 en la posición 7, por lo que nuestra matriz de resultados será: {8, # VALUE, # VALUE, # VALUE, # VALOR, # VALOR, # VALOR, #VALOR, # VALOR, 7}.
Usando la función IFERROR reemplazamos los errores #VALUE por 999999999. Luego simplemente buscamos el mínimo dentro de esta matriz y obtenemos por lo tanto el lugar del primer número (7).
Tenga en cuenta que la fórmula anterior es una fórmula de matriz, debe presionar Ctrl + Shift + Enter para activarla.
Buscar el primer carácter de texto después del número
De manera similar a ubicar el primer número dentro de la cadena, usamos la función ENCONTRAR para determinar dónde comienza el texto nuevamente después del número haciendo un buen uso también del argumento núm_inicio de la función.
| 1 | = MIN (SIERROR (ENCONTRAR ({"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 manera muy similar a la anterior utilizada para ubicar el primer número, solo que usamos letras en nuestra constante de matriz y, por lo tanto, los números causan errores #VALUE. Tenga en cuenta que comenzamos a buscar solo después de la posición determinada para el primer número (este será el argumento start_num) y no desde el principio de la cadena.
No olvide presionar Ctrl + Shift + Enter para usar la fórmula anterior.
No queda nada más que poner todo esto junto.
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 de texto después de eso, simplemente usamos la función MID para extraer la parte numérica deseada.
| 1 | = MEDIO (B3, C3, D3-C3) |
Otro método
Sin explicarlo con más detalle, también puede usar la fórmula compleja a continuación para obtener el número dentro de una cadena.
| 1 | = SUMPRODUCTO (MID (0 & B3, LARGE (INDEX (ISNUMBER (- MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1)) * ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3)))) + 1,1) * 10 ROW (INDIRECT ("1:" & LEN (B3))) / 10) |
Tenga en cuenta que esta fórmula anterior le dará 0 cuando no se encuentre ningún número en la cadena y que los ceros iniciales se omitirán.
Encuentre y extraiga el número de una cadena a un texto en Google Sheets
Los ejemplos que se muestran arriba funcionan de la misma manera en Google Sheets que en Excel.
