Sum Numbers With Delimiter By Formula in Excel 2019-CodePudding

I have a cell with text as 0.4-2-10-0.5. How can I create formula or function using VBA to sum every element at cell is 0.4 2 10 0.5 =12.9 ? I tried formula from https://www.extendoffice.com/documents/excel/3959-excel-sum-numbers-with-commas.html. However That's wrong with my this case. Thank you

CodePudding user response：

Formula solution would be: =SUM(FILTERXML("<y><z>"&SUBSTITUTE(A1,"-","</z><z>")&"</z></y>","//z")) where A1 would contain the given text. Side note: this formula FILTERXML only works on Windows systems.

CodePudding user response：

This seems to work fine for me. Keep out, your locale might interpret the dot differently:

Sub test()
Dim s As String
s = "0.4-2-10-0.5"
a = Split(s, "-")
Dim sum As Double
sum = 0
For Each t In a
  sum = sum   CDbl(t)
Next t
End Sub