Adding numbers within the cell

[xoxo]

Hi all, I am xoxo and new to this forum.

Would like help on how to calculate numbers within a cell. Say, I have these numbers in A1: 01-02-03-04-05 and I want A2 to give me the sum of all the numbers in A1 without the dash. Is it possible?

Thank you very much!
xoxo

 

[Dave Patton]

try
=SUMPRODUCT(MID(SUBSTITUTE(A1,"-",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"-","")))),1)+0)

 

[xoxo]

Thank you for the help.

It works for 01-02-03-04-05.

When I apply the formula to a cell with 10-21-24-35-46, how do I go about in doing this? It gave me 28 as the sum, when it should be 136. Thanks.

 

[Dave Patton]

try text to columns with "-" as a separator

 

[xoxo]

Thank you for the quick reply. How do I do text to columns?

 

[Dave Patton]

Prior to Excel 2007, look at menu Data | Text to Column

Check for additional information in Excel help.

If your text string is consistent, you can build a formula such as

=SUM((MID(A5,{1,4,7,10,13},2)+0))

 

[xoxo]

Wow, that works beautifully. Thank you so much for your help!

 

[jeffreybrown]

As another approach you could use =EVAL(SUBSTITUTE(A5,"-","+")) which would require the numbers to be consistent.

 

[jeffreybrown]

Just a correction to my post. To use Eval it requires Morefunc's to be installed.

http://www.mrexcel.com/forum/showthread.php?t=375298&highlight=Eval

 

[pgc01]

You can use Jeff's approach without the add-in if you define a named formula. It will refer, however, to a cell with a fixed relative offset.

Ex.:

- Select Sheet1!B1

- Define the name:

Name: SumIn
Refers to: =EVALUATE(SUBSTITUTE(Sheet1!A1,"-","+"))

Now you can use this named formula to calculate the cell to the left.

Ex:

C10: 23-1-100-2000
D10: =SumIn

The result in D10 will be 2124.