Sum of Alphanumeric values

[mrc44]

I have a set of values all of which are comprised of one letter and one digit number like (a1,a2,a5,b2,b4,c3,c1,d4,e5,etc) in A1:F5, and I would like to sum these values (the last numeric values) in G1. I need to sum the numbers disregarding the letters. Every value has one digit number at the end (after the letter). Is there a way to do this with a formula (or UDF)? please. Thanks.

For example: if the values are a1,a5,b2,b4,c3,d1,e4, then the sum of these would be = 20

Note: I need the sum function to work without separating the values into different cells.

 

[Rick Rothstein]

Here is one way...

=SUMPRODUCT(0+(0&RIGHT(A1:F5)))

 

[mrc44]

Thanks a lot, Rick! This is a simple and perfect solution.