Solution – Challenge 73 – Sum the Numbers in a String

Below are the possible solutions to the Challenge 73 – Sum the Numbers in a String

Formula using traditional Excel functions

=SUM(–(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&
(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*LEN(A2)+1,LEN(A2))))

Using FILTERXML (for office 2013 and greater and 365) 

=SUM(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s"))

If you are not on Office 365, then you will need to enter above formula as array i.e. by pressing CTRL+SHIFT+Enter. In Office 365, all formulas are treated as array formula automatically.

Using Evaluate through Name Manager

Formulas tab – Name Manager – New – Put any name in Name: box, say you put GetSum

Put following in Refer To: box

=EVALUATE(SUBSTITUTE($A$2,",","+"))

Now when you put =GetSum in a cell, it will calculate the sum.

Note – If you use this, then you will need to save your file as .xlsm not as .xlsx.

Using Lambda function (only for Office 365 Insiders)

The corresponding lambda function which needs to be put in Refer To: box of Name manager

=LAMBDA(s,IF(s="",0,LEFT(s,FIND(",",s&",")-1)+GenSum(REPLACE(s,1,FIND(",",s&","),""))))

And in Name: box, you need to put GenSum.

Then to get the sum, use following formula

=GenSum(A2)

Power Query approach

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Column1"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type number}}),
#"Calculated Sum" = List.Sum(#"Changed Type"[Column1])
in
#"Calculated Sum"

Above formulas and Power Query can be downloaded from Solution – Challenge 73 – Sum the Numbers in a String

Note – There is an excellent VBA solution posted by Rick in Comments section of the problem. If you are looking for a VBA solution, you should go to Challenge 73 – Sum the Numbers in a String