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

	A​	B​
1​	Text​	Result​
2​	Training: 7, Fighting: 12, Speed:3​	22​
3​	Training: , Fighting: 12, Speed:​	12​
4​	Training: 0, Fighting: , Speed: 3​	3​

<tbody>
</tbody>

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