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