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

 

[Peter_SSs]

I also have the sample workbook.

Can you upload the sample workbook to a public file-share site & provide a link here?

 

[LFKim2018]

Mr. Peter_SSs
Actually I am helping a certain Mr. Chen in his work. With your formula I think it worked but I am thinking of VBA which is a more dynamic approach to the problem.
I know the format for a subroutine but I do not know the proper syntax.
it could go like:

Sub AddDigitsInAString()
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row    
    For i = 2 to Lastrow
        Digits(Range("A" & i)  > B    'pull out numbers from the string in order together with the "+" signs and place it at Column B
        SumDigits  > C                   'sum the string digits in Column B and place result in column C
   Next
   Range("D2")=sum(C:C)      'place sum of column C to Cell D2
End Sub


[I]the function by Mr. Rick Rothstein (don't know if it will work in the above sub)
[/I]
Function Digits(ByVal S As String) As String 'Courtesy of Rick Rothstein
    Dim X As Long
    For X = 1 To Len(S)
        If Mid(S, X, 1) Like "[!0-9,.,+]" Then Mid(S, X) = Chr(1)
    Next
    Digits = Trim(Replace(S, Chr(1), ""))
End Function

many many thanks

 

[LFKim2018]

so sorry, I do not know how to use public-share site...
can I send them to your email address?

 

[Peter_SSs]

so sorry, I do not know how to use public-share site...

You can sign up for a free account here for example, though there are other sites too if you Google.

 

[LFKim2018]

You can sign up for a free account here for example, though there are other sites too if you Google.

pls confirm if you get the file ..

https://www.dropbox.com/home/for sharing

 

[Peter_SSs]

pls confirm if you get the file ..

https://www.dropbox.com/home/for sharing

No, you mustn't have the sharing quite right yet. That link requires me to log in (which I can do but shouldn't have to) and even if I log in the link is not taking me to your file. Have a bit more of a look about how to share with anybody. When you hover over the file in your dropbox there should be a 'Share' button appear at the right (or use the 3 dots at the right) then click 'Copy link' to the right of 'Anyone with the link can view this file'

 

[LFKim2018]

No, you mustn't have the sharing quite right yet. That link requires me to log in (which I can do but shouldn't have to) and even if I log in the link is not taking me to your file. Have a bit more of a look about how to share with anybody. When you hover over the file in your dropbox there should be a 'Share' button appear at the right (or use the 3 dots at the right) then click 'Copy link' to the right of 'Anyone with the link can view this file'

https://www.dropbox.com/s/rx9dy9jhtkakc7u/sumdigits.PNG?dl=0
https://www.dropbox.com/s/peqpi2rypnjns5e/Test for Mr. Chen.xlsm?dl=0

could the above be the one?

 

[Peter_SSs]

could the above be the one?

Yes, I can get the file now thanks. As I suspected, the formula you were claiming does more than 9 numbers does not.

Your posts 8 & 10 say that this formula worked for 10 or 15 numbers:
=SUMPRODUCT(MID(SUBSTITUTE(A1&REPT("+0",9),"+",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100-99,100)+0)

However, the file you have provided is not using that formula at all, but the following formula (from post 5)
=SUMPRODUCT(MID(SUBSTITUTE(A1,"+",REPT(" ",100)),ROW(INDIRECT("1:"&((LEN(A1)-LEN(SUBSTITUTE(A1,"+","")))+1)))*100-99,100)+0)

This formula does work for more numbers - I never said it only worked for 9
The reason I offered the the different formula in post 6 was for if you wanted to avoid using a volatile function (INDIRECT) that could slow your sheet down if you have a lot of those formulas in it.

Hope it is clear now.


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)

 

[LFKim2018]

Yes, I can get the file now thanks. As I suspected, the formula you were claiming does more than 9 numbers does not.

Your posts 8 & 10 say that this formula worked for 10 or 15 numbers:
=SUMPRODUCT(MID(SUBSTITUTE(A1&REPT("+0",9),"+",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100-99,100)+0)

However, the file you have provided is not using that formula at all, but the following formula (from post 5)
=SUMPRODUCT(MID(SUBSTITUTE(A1,"+",REPT(" ",100)),ROW(INDIRECT("1:"&((LEN(A1)-LEN(SUBSTITUTE(A1,"+","")))+1)))*100-99,100)+0)

This formula does work for more numbers - I never said it only worked for 9
The reason I offered the the different formula in post 6 was for if you wanted to avoid using a volatile function (INDIRECT) that could slow your sheet down if you have a lot of those formulas in it.

Hope it is clear now.


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, named ranges, 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)

Mr. Peter_SSs
WOW! so much simplicity!!
many many thanks

 

[Peter_SSs]

Mr. Peter_SSs
WOW! so much simplicity!!
many many thanks

You are very welcome.