Formula help to sum numbers following text

Is it always only 1 letter?

Hi Ex-cel, welcome to the forum. If your cells always contain one letter followed only by numbers, you could use the array function:

=SUM(MID(A1:A5,2,LEN(A1:A5))+0)

This must be confirmed using CTRL+SHIFT+ENTER, not just ENTER. When done properly, Excel will automatically add braces, {}, around your formula.

Edit: Deleted

Actually, Soren, using the +0 in my formula converts any number stored as a string (which the MID function creates anyway) to a true number and drops any additional spaces. So "P7__" would become 7 (underscores representing spaces).

Originally Posted by Paul

Actually, Soren, using the +0 in my formula converts any number stored as a string (which the MID function creates anyway) to a true number and drops any additional spaces. So "P7__" would become 7 (underscores representing spaces).

Ok, but for some reason that doesn't work for me. Is that because the space is at the end?

EDIT1: I guess not after rereading your reply.

EDIT2: Alright, I was a little to fast there; I didn't have spaces after the strings, but rather some other formatting resembling a " ". Thanks Paul!

This would also do it for single letters

=SUMPRODUCT(REPLACE(A1:A5,1,1,"")+0)

Thanks, DDL. Now of course I tested both with a blank cell in the range. Both error out, but I was able to slightly adjust DDL's to handle blanks in the range by changing the "" to 0.

=SUMPRODUCT(REPLACE(A1:A5,1,1,0)+0)

Ah, yes, I didn't think of doing it that way, nice one