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

freag34

New Member
Joined
Jun 11, 2002
Messages
23
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

freag34

New Member
Joined
Jun 11, 2002
Messages
23
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

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
hello

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

freag34

New Member
Joined
Jun 11, 2002
Messages
23
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

New Member
Joined
Jun 11, 2002
Messages
23

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 11, 2002
Messages
23

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
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
 

freag34

New Member
Joined
Jun 11, 2002
Messages
23
thanks Andrew, very very cool. Very elegant.

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

Forum statistics

Threads
1,143,641
Messages
5,719,988
Members
422,257
Latest member
Calion

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top