Here is a quick reference for Excel column letter to number mapping. Many times I needed to find the column number associated with a column letter in order to use it in Excel Macro. For a lazy developer like me, It is very time consuming 😉 to use my Math skill to get the answer so I created this quick reference lookup for myself.
Jump to specific column list
If you are looking for a specific column, click on the following links to jump to a specific list of columns
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
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.
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!
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.
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
“`
Thanks from New Zealand. 🙂
Thanks! What a timesaver. Just found this for the first after manually calculating the column numbers for VLOOKUP function.
=column() in a column will render the column number
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)
Thanks from the Cayman Islands for this!
You saved me a lot of time working on my vlookups ✌✌✌
Thank you for this! I’ve visited this page many times when working on VLOOKUPs. Total life saver.
brilliant job, I often need the reverse but your table gives me that option as I can just look up the other way
Thank you, such a simple table but very, very helpful.
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
Thank you so much! This is so helpful 🙂
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.
very brilliant
thanks to share
vipul jhaveri
you should stop using vlookup and use xlookup. There you can just reference “CH” instead of 86
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!
Thank you. what a time saver
Superb and very helpful reference
Thanks, it helped!
Thank you! Helpful
fabulous a real time saver thankyou!
Thank you, Mr. Vishal Monpara!
Thank you so much for this. It’s such a simple thing, but it’s so very helpful.
Thank you that’s very useful. I use this list everyday at work.
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.
Can you also provide the Column# for column till AAA or so?