No need for a macro. Paste the formula below into cell B2 and it will extract the numbers only from the cells.

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

By the way, if you have an entry in a cell like "swes3454gfr56" it will return 345456.