Summing values in a cell with comma separated text!!!

[freag34]

I have a single cell that contains the text entry:

"1,4,6,7"

I want to know a formula (I'd prefer to not use a macro, although obviously a macro could solve this fairly easily) that will SUM the numbers in a cell to give a result in another cell - for example the result of the above is 1+4+6+7=18.

Can anyone think of a neat trick to accomplish this?

Much appreciated

 

[Aladin Akyurek]

Also:

=EVAL(B1&"({"&A1&"})")

where A1 is a multiple-value cell with comma as separator and B1 houses a function name like SUM, COUNT, or AVERAGE.

EVAL is part of the morefunc add-in, downloadable from:

http://longre.free.fr/english/index.html

 

[arif_duet]

Thanks Andrew, this is very helpful.

I hope i will be help many person........

 

[sanPetersburgJP]

I can do it with a custom function:

Function Test(Rng As Range) As Variant
    Test = Evaluate(Application.Substitute(Rng, ",", "+"))
End Function

Then type:

=Test(A1)

Dear Andrew,
Brilliant... howerver when i try it it gives 0 as a result.. any ideas why?