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 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.
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.
Leave a Reply