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

Pages: 1 2

[…] 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