Necesito extraer los últimos 7 valores numéricos (no caracteres) de una cadena en una celda de Excel. La cadena contiene un nombre de archivo (que puede ser una combinación de letras, números y un guión) y luego 7 números que deben moverse a otra columna.
p.ejJohnson Set1- 0 0 1 14 9 54 0
Los valores numéricos varían de 0 a 999, por lo que no es posible extraer simplemente los últimos 7 caracteres.
Respuesta1
Por eso, existen algunos trucos que puedes utilizar para conseguir lo que deseas. Haré todo lo posible para explicarlos aquí y luego te daré una fórmula que utiliza estos trucos para darte lo que deseas.
La clave es encontrar el espacio antes de que comience la lista de números. Si su ejemplo es lo suficientemente general, este espacio siempre será el séptimo espacio desde la derecha. El problema es que el séptimo desde la derecha puede ser cualquier espacio numérico desde la izquierda porque parece que no hay reglas sobre que el nombre del archivo tenga un número determinado de espacios. Y la SUBSTITUTEfunción le permite especificar qué instancia (desde la izquierda) de una cadena reemplazar.
Para obtener el séptimo espacio desde la derecha, tenemos que traducirlo a espacios desde la izquierda. Para hacer esto, necesitamos saber la cantidad de espacios que hay en toda la cadena. Una vez que tengamos eso, es sólo cuestión de restar.
Para encontrar el número total de espacios en la cadena, existe un truco que utiliza las funciones SUBSTITUTEy LEN. La idea es reemplazar todos los espacios en la cadena con nada y luego comparar la longitud de la salida con la longitud de la cadena original. La diferencia es, por supuesto, el número de espacios en la cadena original. Entonces, para una cadena en A1, lo siguiente devolverá el número de espacios:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Entonces, la instancia numérica del espacio de la izquierda que desea siempre tendrá 6 espacios más a su izquierda.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
Ahora, el siguiente problema es que solo SUBSTITUTEte permite especificar el número de instancia que deseas cambiar. FIND, que sería más conveniente para extraer los números, no tiene esta característica. El truco entonces es usar SUBSTITUTEpara generar una cadena que te permita usarla FINDcomo quieras. Si reemplaza el séptimo espacio desde la derecha con una cadena que no aparece en ningún otro lugar de la cadena, tendrá una subcadena única para buscar. Entonces, por ejemplo, puedes usar lo siguiente para reemplazar el espacio deseado con "REPLACE!ME":
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
Ahora, sólo necesitas encontrar el índice de "REPLACE!ME" en la cadena y devolver todo a su derecha. Esto se puede hacer con las funciones RIGHT, LENy FIND. La función final es algo repetitiva, pero hace el trabajo.
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)