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!