getting the sum of numbers in a string

[LFKim2018]

I have data in a cell of 1 to maximum of 10 numbers with + sign in between them, but they can not be added as yet for they are part of a string. How would be the formula or the function for its sum?
example:
Column A Column B
1+9.5 sum formula (10.5)
2+11+100 sum formula (113)
37+50+9+7.75 sum formula (103.75)
4+3+1000+77+1+10 sum formula (1095)
and so on....
up to a combination of ten numbers - always with a plus sign in between the numbers.
the numbers can be any number with or without decimal.
using of helper columns is ok.
many many thanks

 

[Rick Rothstein]

BTW, it looks like you are using these formula and a udf to eventually sum the numbers within strings in a range.
Instead of the various helper columns, defined Names, volatile functions etc, you could just have a single UDF to get the sum directly. Here is one way

Function SumR(r As Range) As Double
  Dim c As Variant, itm As Variant

  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d+(\.?\d*)"
    For Each c In r.Cells
      For Each itm In .Execute(c.Value)
        SumR = SumR + itm
      Next itm
    Next c
  End With
End Function

Then to get the result you have in cell D1 directly, just use =SumR(A1:A7)

For those who might be interested, here is a way to write Peter's SumR function without calling out to a Regular Expression evaluator...

Function SumR(R As Range) As Double
  Dim Cell As Range
  For Each Cell In R
    SumR = SumR + Evaluate(Cell.Value & "+0")
  Next
End Function

 

[Rick Rothstein]

For those who might be interested, here is a way to write Peter's SumR function without calling out to a Regular Expression evaluator...

Function SumR(R As Range) As Double
  Dim Cell As Range
  For Each Cell In R
    SumR = SumR + Evaluate(Cell.Value & "+0")
  Next
End Function

Actually, in order to get closer to matching the output from Peter's function, I need to make a small change to my function's code...

[table="width: 500"]
[tr]
	[td]Function SumR(r As Range) As Double
  Dim Cell As Range
  For Each Cell In r
    SumR = SumR + Evaluate("IFERROR(" & Cell.Value & ",0)")
  Next
End Function[/td]
[/tr]
[/table]

With the above modification, my code will now ignore cell with text in them like Peter's code does. However, the one place my code differs with Peter's is if a cell contained non-expression text mixed with the summation text. For example, if a cell contains this text...

Peter + 1 + Rick + 2

my code would ignore this cell entirely whereas Peter's code will add 3 to the final calculated summation.

 

[Peter_SSs]

Actually, in order to get closer to matching the output from Peter's function,..

Rick, your code does not replicate my function at all as far as I can see. Certainly does not do what the OP was trying to do overall (which was not as per post 1).

if a cell contains this text...

Peter + 1 + Rick + 2

my code would ignore this cell entirely whereas Peter's code will add 3 to the final calculated summation.

.. and the OP would want 3 returned from that cell for their particular exercise that I wrote function for.

 

[Rick Rothstein]

Rick, your code does not replicate my function at all as far as I can see. Certainly does not do what the OP was trying to do overall (which was not as per post 1).

In my tests, whether for a single cell or for a range of cells passed in, my function returned the same values as your function did (with the one exception that I noted). Can you post an example where the values are different?

.. and the OP would want 3 returned from that cell for their particular exercise that I wrote function for.

Did I miss it... I don't recall the OP giving such an example or a description for such an example.

 

[Peter_SSs]

Did I miss it... I don't recall the OP giving such an example or a description for such an example.

See post 17

 

[Rick Rothstein]

See post 17

Ah, now I see what you mean. Okay, then I believe the following non-RegExp function will duplicate the results of your RegExp function...

Function SumR(R As Range) As Double
  Dim Cell As Range, V As Variant
  For Each Cell In R
    For Each V In Split(Replace(Cell.Value, "+", "+ "))
      SumR = SumR + Val(V)
    Next
  Next
End Function

 

[Peter_SSs]

.. I believe the following non-RegExp function will duplicate the results of your RegExp function...

It does - for the OP's exact data format, but not in general.

(BTW, did you waste a character? Couldn't "+ " be replaced with just " "?)