Como selecionar o primeiro número na célula no Excel?

Question

Eu tenho uma tabela que armazena um número e algum texto em uma célula, e eu quero fazer uma operação aritmética com base nos números. Então minha mesa parece com isso:

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. Eu acho que eu preciso de uma função que analisa a substring até o primeiro caractere não numérico em um número. Posso fazer isso com o Excel?

P.S .: Os números podem ter mais de 1 dígitos. É seguro assumir que eles são inteiros, mas seria bom ver uma solução que se aplica a pontos flutuantes também:)

Obrigado,

    

Answer 1

Para obter os números, se eles estiverem no começo da string, podemos usar isso:

=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, isto encontrará o fim do número e retornará como o fim 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},""))) Como esta parte itera os números, ela substitui a última instância de cada número por }}} .

O terceiro critério de SUBSTITUTO é 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},"")) . Itera os números e substitui cada um de cada vez por nada. Em seguida, ele encontra a diferença no tamanho da string original e da nova.

Portanto, no caso de A2, quando iterar para 2 , ele encontrará 2 e o substituto externo substituirá o último por }}} . Este é apenas um detentor de espaço temporário.

A função Aggregate é uma função multifuncional. O 14 diz às funções que estamos usando a função Large() . O 6 informa a função para ignorar erros. Isso é importante porque muitas das iterações não encontrarão nada e retornarão um erro.

Com o 1 no final, ele informa à função que queremos o maior retorno da função Pesquisar, que pesquisa os temporários }}} que são colocados por meio da iteração na última instância de cada número.

Assim, o Aggregate retorna o número máximo encontrado. Qual nós passamos ao critério do comprimento na função do meio.

Então, agora encontramos o número na frente da string.

Assim, podemos multiplicar duas dessas juntas 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))

UmaressalvaAfunçãoAggregatefoiintroduzidanoExcel2010.Podenãofuncionarcomversõesmaisantigas.

Sevocêtiverumaversãomaisantiga,precisaráusaressafórmulamaislonga:

=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},"")))))))

Faz praticamente o mesmo que o acima, aceita que ele deve testar os erros antes de encontrar o max.

    

Answer 2

Supondo que o número seja 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 se pareceria:

=A2*B2

Precisamos substituir as referências de célula 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, ele localizará o primeiro espaço e retornará os caracteres antes dele. 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 é necessário aninhar dentro de outra função para convertê-lo em um número). E note que se o número for ponto flutuante, isso ainda funcionará.

Ressalva: Isso depende de dados de origem "limpos", ou seja, uma entrada numérica ou uma entrada de texto em que começa com um número seguido por um espaço. Se houver uma célula que não esteja de acordo 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 esteja em uma célula separada.