Tengo una tabla que enumera plantas, cantidades y precios. De alguna manera, la mayoría de los precios tienen algunos espacios iniciales que, supongo, están causando el error #VALUE cuando intento aplicar un multiplicador de margen.
Intenté Recortar, Recortar (Limpiar), copiar/pegar especial, buscar/reemplazar, pero nada funciona; al final todavía tengo números con espacios iniciales.
Incluso exporté a csv y luego volví a importar a Excel con la idea de que había algún tipo de formato que causaba problemas.
¿Que demonios? ¿Y hay alguna manera de publicar una sección de mi hoja para su revisión?
Respuesta1
Hay diferentes tipos de espacios (espacios que no se separan) en sus números. Estos suelen ser CHAR(160). Ni TRIM ni CLEAN los eliminarán ya que Excel los ve como caracteres válidos. Pero eso hace que los "números" sean una cadena de texto y, como tal, no se pueden multiplicar.
Así que usamos SUSTITUIR para eliminar esos caracteres y agregar RECORTAR y LIMPIAR por si acaso:
=TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160),"")))*2.5
Respuesta2
Podrías encontrarte con un montón de "espacios que no se rompen" que no son manejados por CLEAN(). Esto es muy común cuando se toman datos de páginas web ya que, como los PDF, solo se preocupan por la presentación visual, no por que alguien extraiga datos para su uso posterior. Pero proviene en gran medida de los programas que generan datos, especialmente cuando no representan el uso general de los datos, sino más bien un conjunto limitado de personas que los utilizan. O lo hicieron hace 28 años, cuando se escribió la funcionalidad de salida CSV, que nunca se actualizó.
El problema es más fácil de manejar en su hoja de cálculo ya que literalmente solo desea la parte numérica sin nada que no sea un dígito numérico.
Si necesitara preservar los indicadores de que el valor es negativo, sería más difícil. O personajes que eran cualquier cosa menos estos espacios. O, como yo, obtenga cadenas que necesiten que la salida sean números discretos. En una cadena como "90.02 45.55 .062" pero conservadas para poder extraerlas.
Sin embargo, simplemente necesita los dígitos en una sola cadena y los números considerados por Excel. La siguiente fórmula hace eso:
=VALUE(TEXTJOIN(,TRUE,IFERROR(MID(C1,SEQUENCE(1,LEN(C1)),1)*1,"")))
Se utiliza SEQUENCE()para obtener una serie de valores que aumentan en 1 y son tan largos como la longitud de su objetivo. Luego los usa para especificar el punto de inicio de la MID()función que genera una matriz de los caracteres en el objetivo. Luego realiza una operación matemática ( *1) que funciona para los dígitos de la matriz, pero da errores para cualquier cosa que no sea un dígito (0-9). IFERROR()convierte esa cadena en dígitos con "" en cualquier lugar donde aparecieron los errores (en cualquier lugar donde el objetivo NO tuviera un dígito). TEXTJOIN()luego convierte esa matriz en una cadena de salida simple, ignorando esos elementos "" para que ahora tenga una cadena útil de solo los dígitos que existían en el destino. Se considera texto y así se mostrará.
En este momento, la fórmula le resulta útil si tiene una versión razonablemente reciente de Excel. Esto se debe a que Excel reconocerá una cadena de dígitos, incluso si están formateados como texto (después de todo, solo están formateados, no modificados de alguna manera fundamental), simplemente como eso, dígitos, si se presentan a una función que REQUIERE, o espera razonablemente , solo datos numéricos como entrada. Entonces, "C1+1" incluso considerará ese texto como un número y realizará la suma en lugar de dar un error o darle 0+1=1.SIN EMBARGO,no todas las funciones tratarán la salida de esta manera. VLOOKUP()lo tratará como lo que parece ser, texto en este caso, y al buscar en una tabla de números no encuentra ninguna coincidencia, lo que le genera un error. Así que envolví lo anterior VALUE()para terminar de modo que el resultado se considere un número y si usa el resultado como entrada para una función de búsqueda, como parece que alguien más podría hacerlo, funcionaría.
Entonces USTED podría eliminar esa función y aún así realizaría la multiplicación del marcado como desee.
Sin embargo, si no tiene las nuevas SPILLfunciones, o especialmente si tiene una versión bastante antigua de Excel, la siguiente fórmula funcionará básicamente en todas las versiones de Excel para Windows y siempre producirá una salida numérica:
{=ROUND(NPV(-0.9,0,IFERROR(MID(L1,LEN(C1)-ROW(INDIRECT("C1:I"&LEN(C1)))+1,1)/100,"")),3)}
Tenga en cuenta que es unCSEfórmula. Usado como una fórmula regular, elimina los dos últimos dígitos, a veces... y la salida se formatea como moneda. (Excel es útil... porque NPV()es una función financiera que DEBES querer formatear como moneda, ¿verdad?)
La fórmula anterior es de "en algún lugar de Internet hace bastante tiempo"... Desearía poder darle crédito a mi fuente, pero no recuerdo la fuente. Disculpas a algún gurú útil.
Me alegra que hayas preguntado esto porque lo tengo en muchos lugares y necesitaba actualizarlo para SEQUENCE()saber cómo puedo ayudar. ¡Sin mencionar el reemplazo del cambio de la matriz interna en la función a la cadena útil que se genera, TEXTJOIN()ya que ahora también está disponible! ¡Beneficio inmediato! ¡Es algo maravilloso!