Eu tenho uma planilha do Excel contendo quase 6.000 produtos. Na coluna Quantidade, existem alguns valores que terminam com "60pcs" e outros com "7pkts".
Eu preciso pegar a quantidade total no final e não consigo fazer isso devido a essas letras misturadas com números. Como faço para calcular a soma total se os valores contiverem texto?
Isso é menos geral do que a solução de @Andi Mohr; Ele pressupõe que você tenha apenas essas medidas unitárias, ambas começando com "p". A única vantagem é que você obtém o resultado final com uma fórmula.
=SUM(NUMBERVALUE(MID(A1:A6000,SEARCH("p",A1:A6000)-1)))
Ajuste as referências da coluna conforme necessário.
Tem de ser confirmado pressionando Ctrl + Deslocar + Introduzir .
Esta fórmula bastante brilhante fará o trabalho para você.
Se o seu mix de números e texto estiver na célula A1, digite-o na próxima célula, pressionando Ctrl + Deslocamento + Enter :
=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))
Você pode então somar essa coluna para calcular seu total.
Essa fórmula foi postada originalmente no Google Groups por alguém chamado Lori - li sobre isso em um Tópico no fórum do Chandoo de um post de Sajan. Como funciona:
The magic of NPV is the NPV calculation formula, where each term is multiplied by the inverse of (1+rate)^n, where n is the nth term in the series. e.g. (1+rate)^1, (1+rate)^2, etc. By using different values for rate, we can get different results. In this case, using -0.9 gives us 1+rate=1+-0.9=0.1. So we get values like {0.1;0.01;0.001;0.0001;0.00001}. Taking the inverse of this gives us {10;100;1000;10000;100000} etc. Combined with the fact that NPV skips text values, we get the desired results.
Editar: melhor velocidade de cálculo
Máté Juhász sugeriu um acréscimo para acelerar a fórmula se aplicada em um grande intervalo. Ele verifica se a string já é um número primeiro, para economizar tempo no Excel calculando algo que não é necessário.
=IF(ISNUMBER(A1),A1,NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,"")))