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

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

```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.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.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)

```