Excel Columns
February 4, 2011
In case you didn’t get it, the trick here is that there is no “zero digit” in the representation; thus, you can’t just use the letters A to Z as digits in a base-26 numbering system. Instead, you have to add or subtract one at each step. Note in the column function that -64 is actually -65 + 1, subtracting the ascii representation of #\A and adding 1 because there is no zero digit:
(define (column excel) ; A=>1, AA=>27, IV=>256
(let loop ((cs (string->list excel)) (result 0))
(if (null? cs) result
(loop (cdr cs) (+ (* result 26) (char->integer (car cs)) -64)))))
(define (excel column) ; 1=>A, 27=>AA, 256=>IV
(let loop ((q column) (result '()))
(if (= q 0) (list->string result)
(let* ((q (- q 1))
(r (modulo q 26))
(q (quotient q 26)))
(loop q (cons (integer->char (+ r 65)) result))))))
Here are some examples:
> (excel 1)
"A"
> (excel 26)
"Z"
> (excel 27)
"AA"
> (excel 256)
"IV"
> (column "A")
1
> (column "Z")
26
> (column "AA")
27
> (column "IV")
256
It is easy to test exhaustively:
(define (test-excel)
(do ((i 1 (+ i 1))) ((< (expt 2 16) i))
(assert (column (excel i)) i)))
We used assert from the Standard Prelude. You can run the program at http://programmingpraxis.codepad.org/DXx4AX1q.
Programming Praxis 
[…] Praxis – Excel Columns By Remco Niemeijer In today’s Programming Praxis exercise our task is to write two functions to convert numbers to and from […]
My Haskell solution (see http://bonsaicode.wordpress.com/2011/02/04/programming-praxis-excel-columns/ for a version with comments):
A Python 3 solution:
#!/usr/bin/env python3 def to_column(n): """Turns a number into an Excel column ID. >>> to_column(0) '' >>> to_column(1) 'A' >>> to_column(26) 'Z' >>> to_column(27) 'AA' >>> to_column(256) 'IV' """ s = "" while n != 0: s += chr((n - 1) % 26 + 65) n //= 27 return s[::-1] def from_column(s): """Turns an Excel column ID into a number. >>> from_column("A") 1 >>> from_column("AA") 27 >>> from_column("iv") 256 """ p, r, s = 0, 0, s[::-1].upper() for c in s: r += (26 ** p) * (ord(c) - 64) p += 1 return rBogdan Popa: your
to_columnmay not be quite right. It returns'AA'for both 27 and 53.
My Python solution.
I made two versions of each, trying to practice a more functional style. I’m
indebted to Remco’s work for noticing that finding the correct digits is
divmodin disguise.A ruby version (mostly stolen from Graham’s Python version)
def column(excel) # A=> 1, AA=>27, IV=>256 c = 0 excel.each_byte do |byte| c = 26 * c + ((byte.to_i)-64) end c end def excel(col) # 1=>A 27=>AA 256=>IV e = "" while col > 0 col -= 1 r = col % 26 col /= 26 e << (r+65).chr end e end puts "A = #{column("A")}" puts "AA = #{column("AA")}" puts "IV = #{column("IV")}" puts "1 = #{excel(1)}" puts "27 = #{excel(27)}" puts "256 = #{excel(256)}"Python Solution:
def decimalValueR(column):
if len(column) == 1:
return ord(column.upper()) – ord(‘A’) + 1
return (ord(column[0].upper()) – ord(‘A’) + 1)*26 + decimalValueR(column[1:])
def columnValueR(decimal):
if decimal <= 26:
return chr(decimal+64)
elif decimal%26 == 0:
return chr(decimal/26 + 63) + 'Z'
return chr((decimal/26)+64) + columnValueR(decimal%26)
2nd Python Solution, since I forgot the code tags…oops.
def decimalValueR(column): if len(column) == 1: return ord(column.upper()) - ord('A') + 1 return (ord(column[0].upper()) - ord('A') + 1)*26 + decimalValueR(column[1:]) def columnValueR(decimal): if decimal <= 26: return chr(decimal+64) elif decimal%26 == 0: return chr(decimal/26 + 63) + 'Z' return chr((decimal/26)+64) + columnValueR(decimal%26);; Should be OK.
(define (unit:excel->int e)
(- (char->integer e) 64))
(define (unit:int->excel e)
(integer->char (+ (floor e) 64)))
(define (excel->int e)
(if (and (string->number e)
(< (string->number e) 27))
(string->number e)
(let ((high (string-ref e 0))
(low (string-ref e 1)))
(+ (* 26 (unit:excel->int high))
(unit:excel->int low)))))
(define (int->excel i)
(if (<= i 26)
(number->string i)
(let ((s (make-string 2)))
(string-set! s 0 (unit:int->excel (/ i 26)))
(string-set! s 1 (unit:int->excel (modulo i 26)))
s)))
(define (test)
(let loop ((i 1))
(if (>= i 256)
#t
(and
(= i (excel->int (int->excel i)))
(loop (+ i 1))))))
ruby 1.9.2 :)
def to_column(excel) eval excel.reverse.each_byte.with_index.map { |i,x| (i-64)*26**x }.join('+') end def to_excel(num) e="" while (num > 0) e << (num-(num=num/26)*26+64).chr end e.reverse endimport string nletters = len(string.uppercase) def to_excel(n): s = '' while True: n, i = divmod(n, nletters) s += string.uppercase[i] if not n: break return s index = dict(zip(string.uppercase, [x + 1 for x in xrange(nletters)])) def from_excel(s): s = string.upper(s) n = reduce(lambda n, c: nletters * n + index[c], s, 0) return nConverting “AA” style indices to integers:
int XLnumber(char *colIndex) { return XLnumber_helper(colIndex, 0); } int XLnumber_helper(char *colIndex, int sum) { if (*colIndex == '\0') return sum; else return XLnumber_helper(colIndex + 1, 26 * sum + *colIndex - 'A' + 1); }…and the other way around, using a bit of glib magic instead of implementing my own LIFO data structure:
#include <glib.h> void XLletters(int numIndex, GString *letters) /* letters initialised as a null-terminated empty string prior to passing to this function * like this for example: * letters = g_string_new("") * Remember to free both the GString and GString->str, or call * g_string_free(GString *, TRUE) * from the calling function after returning */ { int d, m; gchar c; d = numIndex / 26; m = numIndex % 26; c = 'A' + m - 1; letters = g_string_prepend_c(letters, c); if (d > 0) XLletters(d, letters); }A possible solution in REXX
b = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' parse arg in select when datatype(in, 'N') then say in '=' num2excel(in) otherwise say in '=' excel2num(in) end exit num2excel: parse arg num d = num % 26 r = num // 26 if r == 0 then do r = 26 d = d-1 end if d == 0 then return substr(b, r, 1) else return substr(b, d, 1)||substr(b, r, 1) excel2num: parse arg ex if length(ex) == 2 then return (pos(substr(ex, 1, 1), b) * 26) + pos(substr(ex, 2, 1), b) else return pos(ex, b)