Este artículo le explicará cómo extraer los números de una celda que contiene números y letras.
Comprender la lógica aplicada.
Para extraer los números de una misma celda con números y letras, debemos seguir la siguiente lógica
- Extraiga cada carácter, número y letra en una sola celda
Esta tarea ahora es posible con la nueva función SECUENCIA y la función MID
- Crea un caso de prueba para cada personaje.
En esta situación, probaremos si el carácter es numérico o no.
- Mantenga solo el resultado VERDADERO
La función FILTRO nos ayudará a mantener solo el caso de prueba cuando el resultado sea VERDADERO
- Agrupa todos los números en una sola celda.
Finalmente, la función TEXTJOIN recopilará cada número encontrado.
Paso 1: extrae cada carácter de la celda
La función SECUENCIA genera una serie de números entre 2 valores. Por ejemplo, para crear una serie de números entre 1 y 5 en columnas, escribirás
El truco aquí es usar la función SECUENCIA para dividir cada carácter de las celdas usando esta fórmula, que utiliza la función MEDIO. Además, Función LEN devuelve el número exacto de caracteres en cada celda.
=MEDIO(A2,SECUENCIA(,LARGO(A2)),1)
Paso 2: prueba si cada carácter es un número o no
Luego necesitamos realizar una prueba en cada uno de estos caracteres para determinar si es un número o no.
Por lo tanto, usar la función ESNUMERO aquí parece lógico. Sin embargo, en esta etapa, cada celda contiene texto.
=ESNÚMERO(MEDIO(A2,SECUENCIA(,LEN(A2)),1))
Podemos corregir esto fácilmente usando la función VALOR. VALOR convertirá automáticamente un carácter en un número si es necesario.
=ESNÚMERO(VALOR(MEDIO(A2,SECUENCIA(LARGO(A2)),1)))
Para resaltar las celdas cuando el resultado es verdadero, he usado lo siguiente regla de formato condicional
=B2=VERDADERO
Paso 3: aísla los números
Ahora, usamos la función FILTRO para agrupar solo los números (cuando la prueba es VERDADERA).
- El primer argumento de la función FILTRO es la primera fórmula que hemos construido en el paso 1.
- El segundo argumento es la prueba construida en el paso 2
=FILTER(MID(A2,SEQUENCE(,LEN(A2)),1),ISNUMBER(VALUE(MID(A2,SEQUENCE(,LEN(A2)),1))))
Paso 4: agrupa los números
Este es el paso final 😀. Necesitamos combinar el resultado anterior con el TEXTJOIN función.
=TEXTJOIN("",,FILTER(VALUE(MID(A2,SEQUENCE(LEN(A2)),1)),ISNUMBER(VALUE(MID(A2,SEQUENCE(LEN(A2)),1)))))
Otra solucion
Rick Rothsein, otro MVP de Microsoft Excel, tiene un enfoque alternativo para resolver este problema. La fórmula que propone es la siguiente:
=CONCAT(SIERROR(0+MEDIO(A1,SECUENCIA(LARGO(A1)),1),""))
Reutilizó la técnica para combinar MID, SECUENCIA y LEN, pero en lugar de utilizar la función FILTRO, simplemente hizo un cálculo con 0.
- Entonces, cuando es letra + 0, es un error.
- Cuando es 0 + número, la fórmula devuelve un número.
- Por eso lo utilizó IFERROR mantener solo los números (inteligente 😉)
Muchas gracias Rick por compartir tu fórmula 👏👏👏
20/04/2023 a las 09:53
Muy bueno
18/08/2022 a las 13:07
Tengo una celda que contiene **9, ¿cómo obtener solo el número en otra celda? me refiero a 9 en otra celda
19/08/2022 a las 10:56
El símbolo * es siempre una pesadilla porque también significa "todos los caracteres". En esta situación no sé si hay solución.
13/11/2021 a las 07:35
Creo que esta fórmula también funcionará...
=CONCAT(SIERROR(0+MEDIO(A1,SECUENCIA(LARGO(A1)),1),""))
18/11/2021 a las 17:22
¡BRILLANTE! Felicidades por esta fórmula Rick 😉
18/11/2021 a las 18:57
Gracias... me alegro que te haya gustado.