How to get letter in alphabet based on number in Excel and Google Spreadsheet

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.

  • Flemming Steffensen

    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.

      • Flemming Steffensen

        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.

  • Huppe

    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))

    • Alberto Eger

      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!

  • Joel Flanagan

    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.