Excel Column Letter to Number Quick Reference

Comments

30 responses to “Excel Column Letter to Number Quick Reference”

1. 

   Eric Swartwout
   December 6, 2023

   call this function with letters to convert to number or numbers to convert to letters

   Function letnum(Ref As Variant) As Variant
   Dim up As String
   Dim chr1 As String ‘first character
   Dim chr2 As String ‘second character
   Dim trips As Long ‘ trips through alphabet
   Dim asc2 As Variant ‘second letter

   ‘check to see if parameter is letter or number
   If IsNumeric(Ref) = False Then
   trips = 0
   up = UCase(Ref)
   ‘look for one or 2 characters
   If Len(up) = 2 Then
   chr1 = Left(up, 1)
   chr2 = Right(up, 1)
   trips = Asc(chr1) – 64
   ElseIf Len(up) = 1 Then
   chr2 = up
   End If

   If Asc(chr2) >= 65 And Asc(chr2) <= 90 Then
   letnum = Asc(chr2) – 64 + (trips * 26)
   End If

   ElseIf IsNumeric(Ref) = True And Ref 26 Then
   If Ref Mod 26 > 0 Then
   asc2 = Chr((Ref Mod 26) + 64)
   letnum = Chr((Ref \ 26) + 64) & asc2
   Else
   asc2 = Chr(90)
   letnum = Chr((Ref \ 26) + 63) & asc2
   End If
   Else
   letnum = Chr(Ref + 64)
   End If

   Else
   letnum = “error”
   End If

   End Function

   Reply
2. 

   Yolanda Basson
   May 16, 2023

   How do I change the column names back to letters instead of numbers?

   1 2 3 4 5
   2
   3
   4
   5

   But want it to be

   A B C D E
   1
   2
   3
   4
   5

   Reply
   1. 

      Vishal Monpara
      May 16, 2023

      Hi Yolanda,

      You may have R1C1 Reference style enabled. That is the reason, it is showing all numbers. In order to change it, go to File menu > Options > Formulas tab > under “Working with Formulas” section, you will see “R1C1 Reference style” checked. Uncheck this box and you will get all your columns in alphabets.

      Regards
      Vishal Monpara

      Reply
3. 

   Thomas
   March 6, 2023

   Still the most useful reference, since manually computing it takes much longer. 🙂

   Reply
4. 

   Gilberto
   July 7, 2022

   Thank you!!

   Reply
5. 

   holibut
   November 9, 2021

   You can change column name to number by programming. The following is in Python:

   “`
   def getExcelColumnNumber(columnName):
   # columnName as A, AA, ABC
   columnLetters = list(columnName)
   print(columnLetters)

   columnValue = 0
   lettersCount = len(columnLetters)
   for i in range(lettersCount):
   columnValue = columnValue + 26**(lettersCount -i-1) * (ord(columnLetters[i]) – 64)
   # end for

   return columnValue
   # end def

   # test

   getExcelColumnNumber(‘AA’) # 27
   getExcelColumnNumber(‘YA’) # 651
   getExcelColumnNumber(‘ZZ’) # 702
   “`

   Reply
6. 

   Scott
   October 14, 2021

   Thanks from New Zealand. 🙂

   Reply
7. 

   Conrad Graybill
   October 4, 2021

   Thanks! What a timesaver. Just found this for the first after manually calculating the column numbers for VLOOKUP function.

   Reply
8. 

   Dee Roche
   September 13, 2021

   =column() in a column will render the column number

   Reply
9. 

   Mateja
   March 21, 2021

   or you can just make a simple script that takes an input (such as ABD, ABCDEFG, etc) and returns the #

   s, o, l = input(), 0, list(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’)

   for i in range(len(s)):
   o*=26
   o+=l.index(s[i])+1

   print(o)

   Reply
10. 

    Yoyo At The Beach
    February 4, 2021

    Thanks from the Cayman Islands for this!
    You saved me a lot of time working on my vlookups ✌✌✌

    Reply
11. 

    Jonathan
    December 9, 2020

    Thank you for this! I’ve visited this page many times when working on VLOOKUPs. Total life saver.

    Reply
12. 

    Jameel Siddiq
    July 3, 2020

    brilliant job, I often need the reverse but your table gives me that option as I can just look up the other way

    Reply
13. 

    Dirk
    May 14, 2020

    Thank you, such a simple table but very, very helpful.

    Reply
14. 

    Stephen Allen
    March 13, 2020

    I agree that this is really useful. I have take the opportunity to extend the process to Column XFD – the last in my version of Excel.

    A copy of the VB Code is available at https://www.linkedin.com/feed/update/urn:li:activity:6644160098514141186

    Reply
15. 

    Courtney
    February 27, 2020

    Thank you so much! This is so helpful 🙂

    Reply
16. 

    Oj Shrivastava
    February 18, 2020

    Or you can just make quick Mathematical formula based on the position of input.
    eg.
    ABC: 26*26*(A=1) + 26*(B=2) + (C=3) = 731.
    CH: 26*(C=3) + (H=8) = 86.

    Reply
    1. 

       VIPUL JHAVERI
       May 8, 2020

       very brilliant
       thanks to share
       vipul jhaveri

       Reply
17. 

    Martin
    November 28, 2019

    you should stop using vlookup and use xlookup. There you can just reference “CH” instead of 86

    Reply
18. 

    Tim
    November 25, 2019

    Thank you! I ran out of fingers trying to figure out how to reference Column CH in my VLOOKUP. 🙂
    So simple and yet so useful–bless you, Mr. Monpara!

    Reply
19. 

    Vipul
    October 29, 2019

    Thank you. what a time saver

    Reply
20. 

    Nick
    October 25, 2019

    Superb and very helpful reference

    Reply
21. 

    Aditi Bhatt
    August 31, 2019

    Thanks, it helped!

    Reply
22. 

    Ravinga
    April 18, 2019

    Thank you! Helpful

    Reply
23. 

    leonora
    January 25, 2019

    fabulous a real time saver thankyou!

    Reply
24. 

    Andrew Harichandran
    January 23, 2019

    Thank you, Mr. Vishal Monpara!

    Reply
25. 

    Natasha
    January 8, 2019

    Thank you so much for this. It’s such a simple thing, but it’s so very helpful.

    Reply
26. 

    Chris
    October 26, 2018

    Thank you that’s very useful. I use this list everyday at work.

    Reply
27. 

    Katie
    April 25, 2018

    Thank you! The formulas elsewhere online (eg “=COLUMN(INDIRECT(B5&”1″))”) work just fine, but imo it’s a bit more efficient to just use a list sometimes.

    Reply
28. 

    Abhilash Nagkat
    August 16, 2017

    Can you also provide the Column# for column till AAA or so?

    Reply