Sum multiple numbers in a cell-CodePudding

I have a sheet that I am working on but some of the cells contain "|" in between that is why I can't sum the values. I tried many ways but could not come up with a solution. Basically, my output should be like column F below.

E:             F:
25.00|0.00     25
0              0
10.00          0
3.00|13.00     16
3.00           3

CodePudding user response：

You can use FILTERXML with SUM, depending on the version of Excel you have:

=SUM(FILTERXML("<t><n>" & SUBSTITUTE(E1, "|", "</n><n>") & "</n></t>", "//n"))

You can also create a UDF with VBA, using EVALUATE like this:

Function sumIt(raw)
    
    sumIt = Application.Evaluate(Replace(raw, "|", " "))
    
End Function

or SPLIT, like this:

Function sumIt(raw)
    
    arr = Split(raw, "|")
    For Each a In arr
        sumIt = sumIt   CDec(a)
        Next
    
End Function

CodePudding user response：

Use FilterXML() then SUM(). Try-

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

CodePudding user response：

Try solutions given [here], these are for comma but should be working for | as well (https://www.extendoffice.com/documents/excel/3959-excel-sum-numbers-with-commas.html)