Tengo una tabla que almacena tanto un número como algo de texto en una celda y quiero hacer una operación aritmética basada en los números. Entonces mi mesa se ve así:
Number1 | Number2 | Product
2 | 3 | 6
3 (some text) | 40 | 120
4 | 5 (here too) | 20
Entonces, en presencia de esos textos feos, todavía quiero poder generar la columna 3 automáticamente. Supongo que necesito una función que analice la subcadena hasta el primer carácter no numérico en un número. ¿Puedo hacer esto con Excel?
PD:Los números pueden tener más de 1 dígito. Es seguro asumir que son números enteros, pero sería bueno ver una solución que también se aplique a los puntos flotantes :)
Gracias,
Respuesta1
Para obtener los números si están al principio de la cadena podemos usar esto:
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
como nuestra fórmula base, esto encontrará el final del número y lo devolverá como el final de la función MID().
Muchas cosas están pasando aquí:
SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))A medida que esta parte recorre los números, reemplaza la última instancia de cada número con }}}.
El tercer criterio de SUSTITUCIÓN es la instancia. Encontramos el número de instancias con el LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")). Recorre los números y reemplaza cada uno a la vez con nada. Luego encuentra la diferencia de longitud entre la cadena original y la nueva.
Entonces, en el caso de A2, cuando itera hasta el, 2encuentra 2 y el Sustituto externo reemplaza el último con }}}. Esto es sólo un marcador de posición temporal.
La función Agregado es una función multifunción. El 14 indica las funciones que estamos usando Large(). Le 6dice a la función que ignore los errores. Esto es importante porque muchas de las iteraciones no encontrarán nada y devolverán un error.
Con el 1al final, le dice a la función que queremos el mayor rendimiento de la función de búsqueda que busca aquellos temporales }}}que se colocan mediante iteración en la última instancia de cada número.
Entonces el Agregado devuelve el número máximo encontrado. Lo cual pasamos al criterio de longitud en la función Mid.
Ahora hemos encontrado el número al principio de la cadena.
Entonces podemos multiplicar dos de estos para obtener el resultado deseado (cualquier función matemática convertirá la cadena devuelta en un número):
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
Una advertenciaLa función Agregar se introdujo en Excel 2010. Es posible que no funcione con versiones anteriores.
Si tiene una versión anterior, deberá utilizar esta fórmula más larga:
=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))
Hace más o menos lo mismo que el anterior, acepta que primero debe probar los errores antes de encontrar el máximo.
Respuesta2
Suponiendo que el número siempre esté separado de cualquier texto por un espacio, podrías usar algo como esto. Digamos que su ejemplo tiene datos que comienzan en A2, por lo que su primer resultado en C2 normalmente se vería así:
=A2*B2
Necesitamos reemplazar las referencias de celda simples con una fórmula que trate la celda de origen como un número si es solo un número, pero extraiga el número si también hay texto. Entonces C2 se convertiría en:
=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))
Si la celda de origen no es un número, busca el primer espacio y devuelve los caracteres que le preceden. Técnicamente, lo que devuelve es un valor de texto que contiene dígitos, pero Excel es lo suficientemente inteligente como para tratarlo como un número al calcular el producto (no es necesario anidarlo dentro de otra función para convertirlo en un número). Y tenga en cuenta que si el número es de punto flotante, esto seguirá funcionando.
Advertencia: esto se basa en datos de origen "limpios", es decir, una entrada numérica o una entrada de texto que comienza con un número seguido de un espacio. Si hay una celda que no se ajusta a eso, deberá limpiar los datos o incluir algún tipo de verificación de errores. La mejor solución sería dividir las dos columnas de datos para que el texto quede en una celda separada.