Como obter a soma dos valores numéricos em uma string alfanumérica em uma fórmula do Microsoft Excel?

Question

Eu tenho dados em uma célula como:

Good response -6,RNR -2,No Voice -1

Preciso obter a soma apenas dos números na célula. Como posso fazer isso em uma única fórmula?

    

Answer 1

Eu uso Excel add-in RegEx Find / Replace , ele adiciona funcionalidade de expressão regular ao Excel.
(Eu não sou afiliado com esse add-in de qualquer forma, apenas um usuário entusiasta)

Aqui você pode usar uma fórmula como essa para encontrar e somar todos os números:
=SUM(RegExFind(A1,"\d+"))

Ou para considerar valores negativos também:
=SUM(RegExFind(A1,"[-\d]+"))

    

Answer 2

Com base nas informações fornecidas & assumindo que sempre haverá apenas 3 valores para extrair e cada 1 dígito de comprimento, O abaixo irá funcionar para uma string em A2 :

=LEFT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(A2,1)

Claro que existem algumas suposições que fiz aqui,

Se isso não funcionar, explique-me mais detalhadamente como seus dados estão estruturados etc. e tentarei corrigir.

    

Answer 3

Se os seus dados forem exatamente como você mostra, com o número sendo precedido por <space> e seguido por <comma> ou o final da string, você poderá usar o seguinte.

Defina uma fórmula e nomeie-a como seq_99 em que

seq_99 refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

Então você pode usar essa fórmula matriz :

=SUM(IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))),--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))))

Para array-enter uma fórmula, mantenha pressionado ctrl + shift enquanto pressiona enter . Se você fizer isso corretamente, o Excel colocará as chaves {...} ao redor da fórmula que você pode ver na barra de fórmulas.

Eu assumi que - indicava um número negativo. Não é esse o caso, é trivial alterar a fórmula para excluí-lo.

    

Answer 4

Eu gostaria de sugerir uma pequena função VBA. Note que o delimitador pode ser ajustado como mostrado abaixo.

Function SumNumbers(rngS As Range, Optional strDelim As String = "-") As Double

    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)

    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum

End Function

Notas:

• Useessafunçãodamaneirausual:=SumNumbers(endereçodacélula).
• SeodelimitadorforSPACE,usestrDelimAsString="" na lista de argumentos.
• Se o Delimitador for COMMA, use strDelim As String = "," na lista de argumentos.

Espero que isso ajude você.