extract all numbers (double digit) in excel and sum

[sosolid4u09]

Hi everyone,

I have a bit of a complex problem.

I have a column like the following;

Training: 7, Fighting: 12, Speed: 3

I need a formula that sums together 7+12+3 automatically.

Now I got a solution where i extract all the digits in that cell and sum. But that ends up giving me 7+1+2+3 which is obviously not correct.

Does anyone have an idea?

Thanks

 

[sosolid4u09]

I think an User Defined Function (UDF) should be the proper solution.

Anyway, this array formula worked for your examples


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Text​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Training: 7, Fighting: 12, Speed:3​

[/TD]
[TD]

22​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Training: , Fighting: 12, Speed:​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Training: 0, Fighting: , Speed: 3​

[/TD]
[TD]

3​

[/TD]
[/TR]
</tbody>[/TABLE]


Array Formula in B2 copied down
=SUM(IFERROR(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(":"&A2,":",REPT(" ",500)),",",REPT(" ",500)),ROW(A$1:A$100)*500,500)),0))

confirmed with Ctrl+Shift+Enter, not just Enter

M.

Just to confirm - this also works!

thanks a lot

 

[István Hirsch]

For any number of numbers, set to 100 as max value (confirm with Ctrl + Shift + Enter):

=SUM(IF(ISNUMBER(FIND(" "&ROW($1:$100)&" "," "&SUBSTITUTE(A1,",","")&" ")),ROW($1:$100),0))

 

[Marcelo Branco]

For any number of numbers, set to 100 as max value (confirm with Ctrl + Shift + Enter):

=SUM(IF(ISNUMBER(FIND(" "&ROW($1:$100)&" "," "&SUBSTITUTE(A1,",","")&" ")),ROW($1:$100),0))

Nicely done!

M.

 

[István Hirsch]

Thank you very much!

 

[István Hirsch]

Oops, the conception should be further developped as now it adds only once numbers which appear more than once!

 

[Marcelo Branco]

Oops, the conception should be further developped as now it adds only once numbers which appear more than once!

Yes, it fails for
Training: 7, Fighting: 12, Speed: 12

Hadn't noticed...

M.

 

[Rick Rothstein]

This array-entered** formula appears to work correctly...

=SUM(ROW($1:$99)*(LEN(A1&",")-LEN(SUBSTITUTE(A1&",",RIGHT(" "&ROW($1:$99),2)&",","")))/3)

**Confirm this formula using CTRL+SHIFT+ENTER and not just Enter by itself