Adding all numbers in a cell

mcannally

New Member
Cells can contain a combination of letters and numbers, or just letters, or blank. If there are numbers in the string, then I'd like to sum them. If only text, then return "0". If empty, then return empty. For example

2CPT4 - desired result is 6
ABC - desired result is 0
7CPT - desired result is 7
8AB1C - desired result is 9
Att1BHG - desired result is 1
Empty cells to return an empty cell

Any formula suggestions? Thanks

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Question: 2CPT4 desired result is 6

Suppose you have
1ABC23

is the result 1+2+3 or 1+23?
Or will numbers present only ever be single digits?

=SUM(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0))

confirmed with Ctrl+Shift+Enter.

Also:
=SUM(IFERROR(VALUE(MID(A1,ROW(\$1:\$99),1)),))
Enter with Ctrl+Shift+Enter

Last edited:
Also:
=SUM(IFERROR(VALUE(MID(A1,ROW(\$1:\$99),1)),))
Enter with Ctrl+Shift+Enter
A bit shorter:
=SUM(IFERROR(1*MID(A1,ROW(\$1:\$99),1),))
Enter with Ctrl+Shift+Enter

Replies
12
Views
189
Replies
10
Views
303
Replies
7
Views
211
Replies
19
Views
469
Replies
3
Views
219

1,207,169
Messages
6,076,908
Members
446,239
Latest member
Home Nest

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.

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