Let's say cell A2 has following string.
12,2, 2023,309,4, 557,62
Hence, there are following distinct numbers
12
2
2023
309
4
557
62
The challenge is to find the sum of these numbers. Hence answer should be 2969 which is 12+2+2023+309+4+557+62.
The solution to this problem will be published on 28-Feb-22.
If we assume that the maximum length of the text is 300 characters and that there is a maximum of 24 dots between the numbers, then this normally-entered formula will work…
=SUMPRODUCT(0+TRIM(MID(SUBSTITUTE("."&A1&REPT(".0",25),".",REPT(" ",100)),ROW(1:25)*100,100)))
If you allow UDF (user defined functions), then this one-liner would also work…
Function SumDottedNumbers(S As String) As Long
SumDottedNumbers = Evaluate(Replace(S, ".", "+"))
End Function
Great, the numbers are separated by comma. So, I presume you are using decimal by oversight in your solution.
Whoops! I am going to be having cataract surgery in the spring (hopefully when Covid has died down) and currently I am plagued by ghosted double vision in both eyes (one with vertical ghosts and the other with horizontal ones) which makes reading "challenging", but in particular, small things are really hard to distinguish. So yes, assume those dots are commas.
I pray that you get well soon. You are an inspiration to all of us.
Sorry for the late reply but I missed this when you first posted it. Thank you for the good wishes. There is not anything serious with my eyes, it is just an huge annoyance. The cataract surgery should fix everything, and I'm told that surgery is nothing to worry about,
With Excel 365
=SUM(TEXTSPLIT(A2,",")*1)