Adding numbers in between slashes

[asad]

Hi Guys,

Is there a formula that can add numbers separated by slashes in a cell? So for example I have 0/2/1/0/3, the desired result is 6.

Thanks
Asad

 

[kbrummert]

If you are ok using a custom function...

Function Eval(Ref As String)

Dim sbs As String

sbs = WorksheetFunction.Substitute(Ref, "/", "+")
Eval = Evaluate(sbs)

End Function

 

[asad]

That's wonderful. That will be one option. But I prefer to do it with a formula. So I will wait for another guru to give another answer before I use this one.

 

[asad]

I am thinking of changing that VB script to formula. Something like:

=SUM(SUBSTITUTE(A1,"/",","))

with ctrl+shift+enter
It gives an error.
But the solution must along the similar lines.

 

[Tetra201]

Try

=SUMPRODUCT(--MID(SUBSTITUTE(A1,"/",""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A1,"/","")))),1))

 

[asad]

=SUMPRODUCT(--MID(SUBSTITUTE(A1,"/",""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A1,"/","")))),1))

Beautiful.
Works perfect.
Thanks a lot.

 

[asad]

=SUMPRODUCT(--MID(SUBSTITUTE(A1,"/",""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A1,"/","")))),1))

This wouldn't work if there was a double digit number, will it? Like 0/11/2/0/3, the answer should be 16 but the formula gives 7.

 

[Tetra201]

In this case, an UDF would be, probably, the easiest approach:

Function SumNum(s As String) As Double
    SumNum = Evaluate(Replace(s, "/", "+"))
End Function

 

[Dave Patton]

Excel 2010
	A	B	C
1	0/2/1/0/3	6	6
2	0/21/0/3	24	24
3
3d

Cell Formulas
Range	Formula
B1	=Calc
C1	=Eval(A1)

Named Ranges
Name	Refers To	Cells
Calc	=EVALUATE(SUBSTITUTE(!B3,"/","+"))

Use a Named range
Select cell B1
With Name Manager
New I used the name "Calc".
Refers to: =EVALUATE(SUBSTITUTE(!A1,"/","+"))

This obviously is equivalent to the UDF Eval.

 

[asad]

Thanks Tetra201