 # Sum the numbers in a cell?

 %92 COTTON %8 ELASTANE &

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

samples.

It needs 100% verification. how can i do?

thanks

6 Replies

# Re: Sum the numbers in a cell?

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

# Re: Sum the numbers in a cell?

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

# Re: Sum the numbers in a cell?

That would be a sensible modification.

# Re: Sum the numbers in a cell?

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.

# Re: Sum the numbers in a cell?

Wonderful! it worked. thanks.

# Re: Sum the numbers in a cell?

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``````