Summing values in same cell separated by #

  • Thread starter Thread starter akkerfeld
  • Start date Start date
A

akkerfeld

Hi!
I need to sum up the values in a cell that are separated by #.
A cell looks something like this:
3#3,4#5#6#9
I need to have the sum of this in another cell
26,4

What's the simplest way of solving this problem?

Thanks for your help
 
Hi

1. Be sure you have at lest as much empty adjacent columns as you have
different values in any of cells. When there isn't enough free columns, add
them;
2. Select cells (in column) with your data strings, and then
Data.TextToColumns from menu. Set delimiter to "#" and finish;
3. You have now p.e. numbers
3 3,4 5 6 9
in different columns, which you can to sum up as you wanted.
 
Use this like a normal worksheet function with :-
=MYSUM(A1)

'===========================================
Function MYSUM(st As String) as Double
mystr = ""
Mysum = 0
For c = 1 To Len(st)
myc = Mid(st, c, 1)
If myc = "#" Then
Mysum = Mysum + mystr
mystr = ""
Else
mystr = mystr & myc
End If
Next
Mysum = Mysum + mystr
End Function
'=============================================
 
Thank you both for your help.
Arvi's solution is working.
I would also like to try your's but can't figure out how to input thi
new function into excel
 
akkerfeld said:
Thank you both for your help.
Arvi's solution is working.
I would also like to try your's but can't figure out how to input this
new function into excel.
Click to expand...


1. Tools|Macro|Visual Basic Editor
2. Insert|Module
3. paste the code for the function

Return to the worksheet, and the function should then be active. A much
simpler VBA function that should also be faster is

Function MySum(st As String) As Double
MySum2 = Evaluate(Evaluate("Substitute(""" & st _
& """,""#"",""+"")"))
End Function

Jerry
 
Back
Top