Tenho uma tabela listando plantas, quantidades e preços. De alguma forma, a maioria dos preços tem alguns espaços iniciais que, presumo, estão causando o erro #VALUE quando tento aplicar um multiplicador de marcação.
Eu tentei Trim, Trim(Clean), copiar/colar especial, localizar/substituir, mas nada funciona - no final ainda tenho números com espaços à esquerda.
Até exportei para csv e depois importei de volta para excel com a ideia de que havia algum tipo de formatação causando problemas.
Que diabos? E há alguma maneira de postar uma seção da minha planilha para revisão?
Responder1
Existem diferentes tipos de espaços (espaço ininterrupto) em seus números. Estes geralmente são CHAR(160). Nem TRIM nem CLEAN os removerão, pois o Excel os vê como caracteres válidos. Mas isso torna a sequência de texto "números" e, como tal, não pode ser multiplicada.
Então usamos SUBSTITUTE para remover esses caracteres e adicionamos TRIM e CLEAN para garantir:
=TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160),"")))*2.5
Responder2
Você pode se deparar com uma tonelada de "espaços ininterruptos"que não são manipulados por CLEAN(). Isso é muito comum ao obter dados de páginas da Web, pois eles, como os PDFs, estão preocupados apenas com a apresentação visual, e não com alguém que extrai dados para uso posterior. Mas isso acontece muito com programas que geram dados, especialmente quando eles não retratam o uso geral dos dados, mas sim um conjunto limitado de pessoas que os utilizam. Ou o fizeram há 28 anos, quando a funcionalidade de saída CSV foi escrita, para nunca ser atualizada.
O problema é mais fácil de resolver em sua planilha, pois você literalmente deseja apenas a parte numérica, sem nada que não seja um dígito numérico.
Se você quisesse manter os indicadores de valor negativo, isso seria mais difícil. Ou personagens que eram tudo menos esses espaços. Ou, como eu, obtenha strings que precisam que a saída seja números discretos. Em uma string como "90.02 45.55 .062", mas preservada para que pudesse ser extraída.
No entanto, você simplesmente precisa dos dígitos em uma única string e dos números considerados pelo Excel. A fórmula a seguir faz isso:
=VALUE(TEXTJOIN(,TRUE,IFERROR(MID(C1,SEQUENCE(1,LEN(C1)),1)*1,"")))
Ele é usado SEQUENCE()para obter uma série de valores que aumenta em 1 e é tão longo quanto o comprimento do seu alvo. Em seguida, ele os usa para especificar o ponto inicial da MID()função que gera uma matriz dos caracteres no destino. Em seguida, ele executa uma operação matemática ( *1) naquilo que funciona para os dígitos da matriz, mas fornece erros para qualquer coisa que não seja um dígito (0-9). IFERROR()converte essa string em dígitos com "" em qualquer lugar onde os erros apareceram (em qualquer lugar onde o destino NÃO tinha um dígito). TEXTJOIN()em seguida, converte essa matriz em uma string de saída simples, ignorando os elementos "" para que agora você tenha uma string útil apenas com os dígitos que existiam no destino. É considerado como texto e será exibido assim.
Neste momento, a fórmula é útil para você se você tiver uma versão razoavelmente recente do Excel. Isso ocorre porque o Excel reconhecerá uma sequência de dígitos, mesmo que formatada como texto (eles são apenas formatados, não alterados de alguma forma fundamental, afinal), apenas como isso, dígitos, se apresentados a uma função que EXIGE, ou espera razoavelmente , apenas dados numéricos como entrada. Portanto, "C1+1" considerará esse texto como um número e fará a adição em vez de gerar um erro ou fornecer 0+1=1.NO ENTANTO,nem todas as funções tratarão a saída desta forma. VLOOKUP()irá tratá-lo como o que parece ser, texto neste caso, e procurar em uma tabela de números não encontrará nenhuma correspondência, gerando um erro. Então, envolvi o texto acima VALUE()para finalizar, para que o resultado seja considerado um número e se você usar o resultado como entrada para uma função de pesquisa, como parece que outra pessoa faria, funcionaria.
Então VOCÊ poderia retirar essa função dela e ela ainda faria a multiplicação da marcação conforme desejado.
Porém, se você não possui as novas SPILLfunções, ou especialmente se possui uma versão bastante antiga do Excel, a fórmula a seguir funcionará basicamente em todas as versões do Excel para Windows e sempre produzirá uma saída numérica:
{=ROUND(NPV(-0.9,0,IFERROR(MID(L1,LEN(C1)-ROW(INDIRECT("C1:I"&LEN(C1)))+1,1)/100,"")),3)}
Observe que é umCSEFórmula. Usado como uma fórmula regular, às vezes remove os dois últimos dígitos... e a saída é formatada como moeda. (O Excel é útil... porque NPV()é uma função financeira que você DEVE querer formatada como moeda, certo?)
A fórmula acima é de "algum lugar na Internet, há muito tempo atrás"... Gostaria de poder dar crédito à minha fonte, mas não me lembro da fonte. Desculpas a algum guru útil.
Que bom que você perguntou isso porque tenho isso em vários lugares e precisava atualizar para saber como SEQUENCE()posso ajudar alguém. Sem mencionar a substituição da mudança da matriz interna na função para a string útil que é gerada, TEXTJOIN()já que agora também está disponível! Benefício imediato! É uma coisa maravilhosa!