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

### 13 Responses to “Excel Columns”

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

2. My Haskell solution (see http://bonsaicode.wordpress.com/2011/02/04/programming-praxis-excel-columns/ for a version with comments):

```import Data.Char

toExcel :: Int -> String
toExcel 0 = ""
toExcel n = toExcel d ++ [chr \$ m + 65] where (d,m) = divMod (n - 1) 26

fromExcel :: String -> Int
fromExcel = foldl (\a x -> 26 * a + ord x - 64) 0
```
3. Bogdan Popa said

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 r
```
4. Graham said

Bogdan Popa: your `to_column` may not be quite right. It returns `'AA'`
for both 27 and 53.

5. Graham said

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.

6. slabounty said

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)}"
```
7. Yuushi said

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)

8. Yuushi said

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)
```
9. Axio said

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

10. novatech said

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
end
```
11. Lautaro Pecile said
```import 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 n

```
12. Jebb said

Converting “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);
}
```
13. Rainer said

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)

```