Suppose you have a number between 1 and 26 in cell A1, you can get the corresponding letter in the alphabet using the following trick:
=mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ";A1;1)
Of course, you can extend and modify this with your own letters to suit your particular needs.
The CHAR() command is much better suited for this. It indexes the ASCII table directly
For big letters use this formula:
=char(A1+64)
… and for small letters use this:
=char(A2+96)
Hi Flemming, thanks for your valuable addition! I was under the impression that using CHAR would be dependent on the spreadsheet’s locale settings which is why I opted for this work-aroundy, expressive option. But seeing it takes the ASCII table that shouldn’t be a problem, right?
The advantage of my proposed expressive workaround would be when you want to easily modify your ‘alphabet’. I’ll update my post later to include your option as well.
Using CHAR() may in some cases be dependent on the local settings, but not for the English alphabet and numbers etc.
Since CHAR() uses a potential local variant of the ASCII table, entries after 128 may differ, but the first 127 entries are standardized – refer to http://www.asciitable.com/ for the list.
For columns more than Z – ZZ use this:
=(CHAR((COLUMN()/26)+64))&(CHAR((((COLUMN()/26)-ROUNDDOWN((COLUMN()/26),0))*26+64)))
For all letters from A to ZZ use:
=IF(A1>26,MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,FLOOR(A1/26),1)&MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,MOD(A1-1,26)+1,1),MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ”,A1,1))
Hi! I love your formula, but for some reason the AZ, BZ, CZ (and on) columns are display wrongly. For example: AZ is shown as BZ, BZ is shown as CZ and so on.
Thank you in any case!
Good catch! It was an issue with my use of the FLOOR formula, I’ve fixed the original comment!
I used this in google sheets to get the same result.
=CODE( LOWER(A1) ) – 96
CODE(“a”) returns 97 (the ASCII code of the lowercase letter “a”).
Each letter is sequentially numbered in ASCII so I subtracted 96 and used LOWER() to force lowercase.