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.

From "aha" to "oh shit", I'm sharing everything on my journey of making the easiest website to learn Excel.

I'm learning a lot, and so will you.

Comments

  1. Flemming Steffensen says:
    August 4, 2013 at 8:46 pm

    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)

    Reply
    • SpreadsheetPro.net says:
      August 4, 2013 at 9:29 pm

      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.

      Reply
      • Flemming Steffensen says:
        August 5, 2013 at 9:45 am

        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.

        Reply
  2. Huppe says:
    March 16, 2016 at 11:06 am

    For columns more than Z – ZZ use this:
    =(CHAR((COLUMN()/26)+64))&(CHAR((((COLUMN()/26)-ROUNDDOWN((COLUMN()/26),0))*26+64)))

    Reply
  3. Martim says:
    January 4, 2018 at 4:46 pm

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

    Reply
    • Alberto Eger says:
      January 31, 2018 at 11:00 am

      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!

      Reply
  4. Joel Flanagan says:
    August 8, 2018 at 2:49 pm

    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.

    Reply

Speak Your Mind

*