How to calculate MOD 10 in Excel but ignore first letter

[AngieL]

I need to calculate a check digit using MOD 10 calculation. I am using the formula:

=MOD(10-(SUM(CEILING(MOD(MID(AG2,{1,2,3,4,5,6,7,8,9},1)*{2,1,2,1,2,1,2,1,2},9.5),1))),10)

with the data in AG2 being R208223361. However, my formula doesn't ignore the R in front of the 9 digit number. Can anyone help?

Thanks!

 

[adam087]

If they're always 9 digit numbers then use...

...VALUE(RIGHT(AG2, 9))...

Hope that helps.


/AJ

 

[AngieL]

Thanks! Where do I incorporate that into my original formula?

 

[adam087]

In place of the AG2 cell reference.

/AJ

 

[AngieL]

Thank you! It works perfectly!

 

[adam087]

Awesome, thanks for the feedback

/AJ