Sum the numbers in a cell?

[ErhanEngin]

%92 COTTON %8 ELASTANE & 

COTTON %73,POLYESTER %14,POLYAMIDE %11,ELASTANE %2

samples.

It needs 100% verification. how can i do?

thanks

[#2]

@ErhanEngin 

Here is a custom VBA function you can use:

Function Verify(ByVal txt As String) As Boolean
    Dim arr() As String
    Dim i As Long
    Dim s As Long
    txt = Replace(txt, ",", " ")
    txt = Replace(txt, "%", " ")
    arr = Split(txt)
    For i = 0 To UBound(arr)
        s = s + Val(arr(i))
    Next i
    Verify = (s = 100)
End Function

You can use this in a cell formula:

=Verify(A2)

will return TRUE if the numbers in A2 add up to 100, FALSE if not. The formula can be filled down.

Or you can use it in a conditional formatting rule of type 'Use a formula to determine which cells to format', to highlight cells that meet the condition (or not).

[#3]

Though not common, I think I have seen blends where the percent is a decimal. Perhaps dim s as a single?

[#4]

@JMB17 

That would be a sensible modification.

[#5]

@ErhanEngin 

I think is is time to give in!

I used Charles Williams's regex add-in 

= MAP(target, LAMBDA(t, SUM(--Rgx.MID(t,"[\d\.]+",0))))

and adapted the formula OK for decimal points.

Using basic functionality with MID and SCAN, I achieved a solution of sorts for integers

= MAP(target,
    LAMBDA(t,
      LET(
        seq,     SEQUENCE(1,LEN(t)),
        chr,     MID(t,seq,1),
        digit,   IF(ISNUMBER(--chr), --chr),
        build,   SCAN(,digit, LAMBDA(p,d,IF(d,IF(p,10*p+d,d)))),
        reverse, INDEX(build, 1+LEN(t)-seq),
        num,     SCAN(,reverse, LAMBDA(p,n,IF(n,IF(NOT(p),n)))),
        SUM(num)
      )
    )
  )

but I don't think I fancy further adjustment.

[#6]

Wonderful! it worked. thanks.

[#7]

@Peter Bartholomew 

If the data reliably included the leading "%" signs, you maybe could also filter out those elements and then combine them back into an expression.

Function PctTotal(ByVal txt As String) As Single
     Dim arr As Variant
     
     arr = Split(Replace(txt, ",", " "))
     arr = Join(Filter(arr, "%"), "+")
     PctTotal = CSng(Evaluate(Replace(arr, "%", "")))
     
End Function