Adding all numbers in a cell

[mcannally]

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

 

[Special-K99]

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?

 

[Andrew Poulsom]

For your sample data:

=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.

 

[iyyi]

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

 

[iyyi]

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

 

[mcannally]

Perfect! Thanks for the feedback. I need to learn more about using array formulas.