You "have tried" how?
Sure, VBA can do this.
Occurred to me to use ASCII values of characters to calculate a ranking but unfortunately Q has a higher ASCII code than K.
Could build a lookup table and use VLookup() function.