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

 

[freag34]

I should add that the number of entries as well as number of digits of each number is unknown, so a straight combination of VALUE(LEFT,xxx)) is not going to work, eg I need the formula to work for "12,234,12,1,12" just as well as it does for the simple example above.

Thanks

 

[sen_edp]

hello

hope this helps

BOOK1
	A	B	C	D
1	1,4,6,7	18
2
3
4
5
Sheet1

 

[freag34]

Just thinking out loud here: if I was to do a search and replace of "," with "+" using the SUBSTITUTE function, then add an "=" with CONCATENATE, I could get a text string of :

"=1+4+6+9"

Now I want excel to execute this text string - is that possible? Another problem is it's not a REAL text string, it's a result of a few formulas which might make it even more complicated.

 

[freag34]

I appreciate the reply SEN, but I don't think that will work with an unknown number of entries or digits!!

Thanks though, maybe this problem is one of those unsolvable ones?

 

[Andrew Poulsom]

Here is an array formula that works:

=SUM(IF(ISERR(VALUE(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1))),0,VALUE(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1))))

Press Ctrl+Shift+Enter not just Enter.

Note that it assumes your data is in cell A1. Change the relative references to suit but keep the absolute references $A$1.

 

[freag34]

This seems to work great on single digit numbers, but for example when my input is:

"1,23"

it outputs 6 instead of 24. I'm looking at the details of this very complicated formula to decipher it now to figure out if I can tweak it a bit.

Really appreciate the response

 

[Andrew Poulsom]

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)

 

[sen_edp]

On 2002-09-06 01:44, Andrew Poulsom wrote:
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)

nice work Andrew - congratulations !!
_________________
Best Regards,<font color="black"><font size=+1><font color="blue">A<font color="red">ndrea<font color="blue">S</font color="red"><font size=+1><font size=1><font color="red">using xl2000
This message was edited by sen_edp on 2002-09-06 02:21

 

[freag34]

thanks Andrew, very very cool. Very elegant.

Thanks all for your help, this board rocks, I hope to help others out too