Excel Columns
February 4, 2011
The columns in an Excel spreadsheet are “numbered” A through IV, where A corresponds to 1, Z corresponds to 26, AA corresponds to 27, and IV corresponds to 256.
Your task is to write functions that translate back and forth between the two representations. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.
[…] 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:
Bogdan Popa: your
to_column
may 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
divmod
in disguise.A ruby version (mostly stolen from Graham’s Python version)
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.
;; 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 :)
Converting “AA” style indices to integers:
…and the other way around, using a bit of glib magic instead of implementing my own LIFO data structure:
A possible solution in REXX