Summing values in same cell separated by #

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
 
A

Arvi Laanemets

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.
 
B

BrianB

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
'=============================================
 
A

akkerfeld

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
 
J

Jerry W. Lewis

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
 
Top