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

#### freag34

##### New Member
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

hello

hope this helps
BOOK1
ABCD
11,4,6,718
2
3
4
5
Sheet1

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.

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?

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.

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

I can do it with a custom function:

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

Then type:

=Test(A1)

On 2002-09-06 01:44, Andrew Poulsom wrote:
I can do it with a custom function:

Code:
``````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

thanks Andrew, very very cool. Very elegant.

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

Replies
7
Views
852
Replies
4
Views
311
Replies
1
Views
185
Replies
8
Views
540
Replies
3
Views
898
Legacy 143009
L

1,218,891
Messages
6,145,023
Members
450,586
Latest member
hehehihi2007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back