# Adding numbers within the cell

#### xoxo

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Dave Patton

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

#### xoxo

##### Board Regular
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

##### Well-known Member
try text to columns with "-" as a separator

#### xoxo

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

#### Dave Patton

##### Well-known Member
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

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

#### jeffreybrown

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

#### pgc01

##### MrExcel MVP
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.

Replies
9
Views
205
Replies
3
Views
121
Replies
3
Views
378
Replies
13
Views
345
Replies
3
Views
273

1,190,913
Messages
5,983,539
Members
439,848
Latest member
timmyo

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