Sum the numbers in a cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-3226334%22%20slang%3D%22en-US%22%3ESum%20the%20numbers%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3226334%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22504%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22504%22%3E%2592%20COTTON%20%258%20ELASTANE%20%26amp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22504%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22504%22%3ECOTTON%20%2573%2CPOLYESTER%20%2514%2CPOLYAMIDE%20%2511%2CELASTANE%20%252%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Esamples.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20needs%20100%25%20verification.%26nbsp%3Bhow%20can%20i%20do%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3226334%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3230733%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20the%20numbers%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3230733%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1320742%22%20target%3D%22_blank%22%3E%40ErhanEngin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%20you%20can%20use%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20Verify(ByVal%20txt%20As%20String)%20As%20Boolean%0A%20%20%20%20Dim%20arr()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20s%20As%20Long%0A%20%20%20%20txt%20%3D%20Replace(txt%2C%20%22%2C%22%2C%20%22%20%22)%0A%20%20%20%20txt%20%3D%20Replace(txt%2C%20%22%25%22%2C%20%22%20%22)%0A%20%20%20%20arr%20%3D%20Split(txt)%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(arr)%0A%20%20%20%20%20%20%20%20s%20%3D%20s%20%2B%20Val(arr(i))%0A%20%20%20%20Next%20i%0A%20%20%20%20Verify%20%3D%20(s%20%3D%20100)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20can%20use%20this%20in%20a%20cell%20formula%3A%3C%2FP%3E%0A%3CP%3E%3DVerify(A2)%3C%2FP%3E%0A%3CP%3Ewill%20return%20TRUE%20if%20the%20numbers%20in%20A2%20add%20up%20to%20100%2C%20FALSE%20if%20not.%20The%20formula%20can%20be%20filled%20down.%3C%2FP%3E%0A%3CP%3EOr%20you%20can%20use%20it%20in%20a%20conditional%20formatting%20rule%20of%20type%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'%2C%20to%20highlight%20cells%20that%20meet%20the%20condition%20(or%20not).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3238846%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20the%20numbers%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3238846%22%20slang%3D%22en-US%22%3EThough%20not%20common%2C%20I%20think%20I%20have%20seen%20blends%20where%20the%20percent%20is%20a%20decimal.%20Perhaps%20dim%20s%20as%20a%20single%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3239091%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20the%20numbers%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3239091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1320742%22%20target%3D%22_blank%22%3E%40ErhanEngin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20is%20is%20time%20to%20give%20in!%3C%2FP%3E%3CP%3EI%20used%20Charles%20Williams's%20regex%20add-in%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAP(target%2C%20LAMBDA(t%2C%20SUM(--Rgx.MID(t%2C%22%5B%5Cd%5C.%5D%2B%22%2C0))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20adapted%20the%20formula%20OK%20for%20decimal%20points.%3C%2FP%3E%3CP%3EUsing%20basic%20functionality%20with%20MID%20and%20SCAN%2C%20I%20achieved%20a%20solution%20of%20sorts%20for%20integers%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAP(target%2C%0A%20%20%20%20LAMBDA(t%2C%0A%20%20%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20seq%2C%20%20%20%20%20SEQUENCE(1%2CLEN(t))%2C%0A%20%20%20%20%20%20%20%20chr%2C%20%20%20%20%20MID(t%2Cseq%2C1)%2C%0A%20%20%20%20%20%20%20%20digit%2C%20%20%20IF(ISNUMBER(--chr)%2C%20--chr)%2C%0A%20%20%20%20%20%20%20%20build%2C%20%20%20SCAN(%2Cdigit%2C%20LAMBDA(p%2Cd%2CIF(d%2CIF(p%2C10*p%2Bd%2Cd))))%2C%0A%20%20%20%20%20%20%20%20reverse%2C%20INDEX(build%2C%201%2BLEN(t)-seq)%2C%0A%20%20%20%20%20%20%20%20num%2C%20%20%20%20%20SCAN(%2Creverse%2C%20LAMBDA(p%2Cn%2CIF(n%2CIF(NOT(p)%2Cn))))%2C%0A%20%20%20%20%20%20%20%20SUM(num)%0A%20%20%20%20%20%20)%0A%20%20%20%20)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20I%20don't%20think%20I%20fancy%20further%20adjustment.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
%92 COTTON %8 ELASTANE & 

 

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

samples.

 

It needs 100% verification. how can i do?

 

thanks

6 Replies

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

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

@JMB17 

That would be a sensible modification.

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

Wonderful! it worked. thanks.

@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