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:

Boa resposta -6,RNR -2,Sem voz -1

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

Answer 1

Eu uso ExcelSuplemento Localizar/Substituir RegEx, ele adiciona funcionalidade de expressão regular ao Excel.
(Não sou afiliado a esse suplemento de forma alguma, apenas um usuário entusiasta)

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

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

Answer 2

Com base nas informações fornecidas e assumindo que haverá apenas 3 valores para extrair e cada 1 dígito de comprimento, o seguinte funcionará para uma string emA2:

=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 com mais detalhes como seus dados estão estruturados, etc., e tentarei alterar.

Answer 3

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

Defina uma fórmula e nomeie-a seq_99onde

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 issoinserido na matrizFórmula:

=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))))

Paraentrada de matrizuma fórmula, mantenha pressionado ctrl+ shiftenquanto pressiona enter. Se você fizer isso corretamente, o Excel colocará colchetes {...}ao redor da fórmula que você pode ver na barra de fórmulas.

Presumi que -denotava um número negativo. Se não for esse o caso, é trivial alterar a fórmula para excluí-lo.

Answer 4

Gostaria de sugerir uma pequena função VBA. Observe que o delimitador pode ser ajustado conforme 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:

• Use esta função da maneira usual:=SomaNúmeros(Endereço da Célula).
• Se o delimitador for ESPAÇO, usestrDelim As String = " "na lista de argumentos.
• Se o delimitador for COMMA, usestrDelim As String = ","na lista de argumentos.

Espero que isso ajude você.