There is a VBA function called EVALUATE that will, well, evaluate math strings like the examples you provided. Try this:
Open the VB Editor (ALT+F11)
Click Insert > Module
In the new module, paste the following code:
Public Function sum_text(r As Range)
If r.Cells.Count > 1 Then Exit Function
sum_text = Evaluate(r.Text)
End Function
Close the VB Editor window
In cell C3, put the formula:
Formula:
=sum_text(b3)
It should calculate the correct value. I tested on your other examples in your test workbook, and it worked each time.
Good luck!
Bookmarks