Tenho uma tabela que armazena um número e algum texto em uma célula e quero fazer uma operação aritmética com base nos números. Então minha tabela fica assim:
Number1 | Number2 | Product
2 | 3 | 6
3 (some text) | 40 | 120
4 | 5 (here too) | 20
Portanto, na presença desses textos feios, ainda quero poder produzir a coluna 3 automaticamente. Acho que preciso de uma função que analise a substring até o primeiro caractere não numérico em um número. Posso fazer isso com Excel?
PS:Os números podem ter mais de 1 dígito. É seguro assumir que são números inteiros, mas seria bom ver uma solução que também se aplicasse a pontos flutuantes :)
Obrigado,
Responder1
Para obter os números se eles estiverem no início da string, podemos usar isto:
=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 nossa fórmula base, isso encontrará o final do número e o retornará como o final da função MID().
Há muita coisa acontecendo aqui:
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},"")))À medida que esta parte itera pelos números, ela substitui a última instância de cada número por }}}.
O terceiro critério de SUBSTITUTE é a instância. Encontramos o número de instâncias com o LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")). Ele itera pelos números e substitui cada um de cada vez por nada. Em seguida, ele encontra a diferença no comprimento da string original e da nova.
Portanto, no caso de A2, quando ele itera, 2encontra 2 e o Substituto externo substitui o último por }}}. Este é apenas um espaço reservado temporário.
A função agregada é uma função multifuncional. O 14 informa as funções que estamos usando na Large()função. Diz 6à função para ignorar erros. Isso é importante porque muitas das iterações não encontrarão nada e retornarão um erro.
Com o 1no final informa à função que queremos o maior retorno da função Search que busca aqueles temporários }}}que são colocados por meio de iteração na última instância de cada número.
Portanto, o agregado retorna o número máximo encontrado. Que passamos para o critério de comprimento na função Mid.
Então agora encontramos o número na frente da string.
Portanto, podemos multiplicar dois deles para obter a saída desejada (qualquer função matemática transformará a string retornada em um 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))
Uma advertênciaA função Agregar foi introduzida no Excel 2010. Pode não funcionar com versões mais antigas.
Se você tiver uma versão mais antiga, precisará usar esta fórmula mais longa:
=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},"")))))))
Ele faz aproximadamente o mesmo que o acima, aceitando que deve testar os erros primeiro antes de encontrar o máximo.
Responder2
Supondo que o número esteja sempre separado de qualquer texto por um espaço, você poderia usar algo assim. Digamos que seu exemplo tenha dados começando em A2, então seu primeiro resultado em C2 normalmente seria assim:
=A2*B2
Precisamos substituir as referências de células simples por uma fórmula que trate a célula de origem como um número se for apenas um número, mas extraia o número se também houver texto. Então C2 se tornaria:
=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))
Se a célula de origem não for um número, ela encontra o primeiro espaço e retorna os caracteres anteriores. Tecnicamente, o que ele retorna é um valor de texto contendo dígitos, mas o Excel é inteligente o suficiente para tratá-lo como um número no cálculo do produto (não há necessidade de aninhá-lo dentro de outra função para convertê-lo em um número). E observe que se o número estiver em ponto flutuante, isso ainda funcionará.
Advertência: Isso depende de dados de origem "limpos", ou seja, uma entrada numérica ou uma entrada de texto que começa com um número seguido por um espaço. Se houver uma célula que não esteja em conformidade com isso, você precisará limpar os dados ou incluir alguma forma de verificação de erros. A melhor solução seria dividir as duas colunas de dados para que qualquer texto ficasse em uma célula separada.