sum numbers mixed with text

[Stubby]

Hi fellow Mr Excel'ers

I have scoured the message board for an answer to my problem and seen lots of answers but nothing seems to match what I need!

I have a cell with the following information

2S22F1R

the letters in the cell could be any letter not just the ones i have used above. What I need to do is sum up the numbers, so in the above example i would have an answer of 25.

I guess it can be done with VBA but I need a NON vba answer as the person who will be using this is not a programmer!

any help will be much appreciated.

 

[Steve=True]

Are the cells always: 1 digit, 1 letter, 2 digits, 1 Letter, 1 number, 1 letter?

=SUM(LEFT(G8,1),MID(G8,3,2),MID(G8,6,1))

or

you can always split the data with the Text to columns feature in excel and then

a little trickier if the data is random length of numbers/letters.

 

[Stubby]

Hi Steve=True

Looks Good. I won't be able to try it until Monday but thanks in advance!

 

[doublej_jj]

It is a little clumsy and assumes there are always 7 characters:

=if(isnumber(Left(A1,1)*1),Left(A1,1),0)+if(isnumber(Mid(A1,2,1)*1),Mid(A1,2,1),0)+if(isnumber(Mid(A1,3,1)*1),Mid(A1,3,1),0)+if(isnumber(Mid(A1,4,1)*1),Mid(A1,4,1),0)+if(isnumber(Mid(A1,5,1)*1),Mid(A1,5,1),0)+if(isnumber(Mid(A1,6,1)*1),Mid(A1,6,1),0)+if(isnumber(Right(A1,1)*1),Right(A1,1),0)