Numerical difference between 2 characters

[smoothman]

Hi all,

Was wondering if you could help me.
How do I write in a formula to get the numerical difference between two characters.

So difference between a and c is 2.
Difference between h and q is 9..

If the first character is after the second, for example X and B then I want to count till the end and back, so y z a b .. difference is 4

Thanks

 

[Joe4]

You are welcome.
Glad we were able to help!

Note that if you have the possibility of mixed cases (some upper case, some lower case), but don't care about the case and want to treat them all the same, then Fluff's solution will work better, as he uses the UPPER function to convert them all to UPPER case before comparing.

 

[FormR]

You could also handle the wrapping with the MOD() function like this if you wanted:

=MOD(CODE(UPPER(B1))-CODE(UPPER(A1)),26)

 

[Joe4]

You could also handle the wrapping with the MOD() function like this if you wanted:

=MOD(CODE(UPPER(B1))-CODE(UPPER(A1)),26)

Brilliantly clever and concise!
I wish I had thought of that!