You need to convert the Letter into a number. The CODE formula will do this.
Place this formula somewhere next to your data and fill it down (You can hide this column after)
Code:
=SUM(0+LEFT(B2,1)&CODE(RIGHT(B2,1)))
This basically takes the first character and then converts the 2nd character to a number then it put it in a sum + 0 to convert it all to a number that you can then use your RANK formula with.
Let me know if you have any other questions.
By the way looking at your formula before you can make it easier by typing it like so and then autofilling or copying/pasting it down
The $ sign locks that cell reference and it will not change when filling down.
Here is an attachment if the above didnt make sense.