# Calculating column number based on Excel alpha column reference [closed]

Here's a super fun brain teaser!!!

Can you give me a mathematical equation to calculate the column number of a Microsoft Excel column, given the alphabetic column reference? Let X, Y, & Z represent the alphabetic sequence number, relative to the alphabet (A = 1, B = 2, etc.)

• Isn't this trivial? It's just base 26 arithmetic. May 31, 2016 at 21:59
• Can you please clarify your question ? May 31, 2016 at 22:45

This is trivial:

the text input is simply a $1\text{-based}$ radix $26$ representation of the number to output.

Formally

Let alphabet $\Delta=\{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z\}$ the input be a word $\Gamma=\{\gamma_1,\gamma_2,\cdots,\gamma_{\ell(\Gamma)}\}|\gamma \in \Delta$
and $F:\Delta\mapsto[1,26]$
Now a function to produce our required output is:
\begin{align}column(\Gamma)=\sum_{p=1}^{\ell(\Gamma)}26^{\ell(\Gamma)-p}F(\gamma_p)\end{align}

I do not have Excel, so here is Python code that does the same thing:

def ColumnNumber(text):
return sum(26**p*(ord(c)-64) for p, c in enumerate(text[-1::-1]))

VBA can (relevant Python code in brackets):

reverse the text (text[-1::-1]),
loop through something (for .. in),
take powers (26**p),
find the ordinal of an ASCII character (ord(c)), and
sum numbers (sum()) - probably by keeping a variable and using +.

The enumerate would probably need to be done manually by keeping a variable counting the number of loops, starting at $0$, so it should be possible.

Of course we don't need to implement this anyway as we can just use the function COLUMN() with no arguments!

• Not quite. AA comes after Z: that means A is 0, but 00 is the same thing as 0. (Really, it's a base-26 system with the exception that Z is a zero that adds one to the digit to the left.)
– Deusovi
May 31, 2016 at 23:13
• @Deusovi As I fist implemented and then realised that A is 1 not 0, since this is Excel we are talking about (AA is 27, AAA is 703) - see the code the numbers in the base are $[1,26]$ rather than $[0,25]$ due to the offset of $64$ May 31, 2016 at 23:17
• @Deusovi I realised what you meant and clarified in the textual description Jun 1, 2016 at 0:01
• I think the mathematics is now formal Jun 1, 2016 at 0:48