1
$\begingroup$

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

I look forward to seeing your answers... ALGEBRAIC!

| improve this question | |
$\endgroup$
  • 3
    $\begingroup$ Isn't this trivial? It's just base 26 arithmetic. $\endgroup$ – LeppyR64 May 31 '16 at 21:59
  • $\begingroup$ Can you please clarify your question ? $\endgroup$ – Fabich May 31 '16 at 22:45
3
$\begingroup$

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!

| improve this answer | |
$\endgroup$
  • $\begingroup$ 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.) $\endgroup$ – Deusovi♦ May 31 '16 at 23:13
  • $\begingroup$ @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$ $\endgroup$ – Jonathan Allan May 31 '16 at 23:17
  • $\begingroup$ @Deusovi I realised what you meant and clarified in the textual description $\endgroup$ – Jonathan Allan Jun 1 '16 at 0:01
  • $\begingroup$ I think the mathematics is now formal $\endgroup$ – Jonathan Allan Jun 1 '16 at 0:48
1
$\begingroup$

How about:

=column(indirect(a1 & "1"))

where a1 is the cell containing the column letter.

| improve this answer | |
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.