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]

If "a" is in cell A1, and "c" is in cell B1, use this formula:

=CODE(B1)-CODE(A1)

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[smoothman]

Many thanks. I'm using the latest version of Excel.

@Joe4

The =code() formula works if the first letter is smaller than the second. So if it's A and D then it correctly comes back as 3.

However it doesn't work the other way. So if it is the difference between D and B then I don't want it to say -2. I want it to go forward and loop back. So E F G H I J K L M N O P Q R S T U V W X Y Z A B = 24

Any ideas here?

 

[JvdV]

Deleted, this was not what OP needs

 

[Fluff]

How about

=IF(A2<B2,CODE(UPPER(B2))-CODE(UPPER(A2)),CODE(UPPER(B2))+26-CODE(UPPER(A2)))

 

[Joe4]

Try this:

=IF(CODE(B1)-CODE(A1)>0,CODE(B1)-CODE(A1),CODE(B1)-CODE(A1)+26)

If you have Excel 365, you can shorten it to:

=LET(x,CODE(B1)-CODE(A1),IF(x>0,x,x+26))

 

[Joe4]

Note: Might you have a mixture of comparing upper case value to lower case values, i.e. compare "a" to "M"?
How exactly do you want those to work?

 

[smoothman]

Hi @Joe4 all good it's working perfectly . Thank you so much

Thanks aswell @Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.

Please don't forget to update your profile, as requested.