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

Posted by programmingpraxis
Filed in Exercises
13 Comments »

13 Responses to “Excel Columns”

  1. Programming Praxis – Excel Columns « Bonsai Code said
    February 4, 2011 at 9:59 AM

    […] 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. Remco Niemeijer said
    February 4, 2011 at 9:59 AM

    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
    February 4, 2011 at 1:21 PM

    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
    February 4, 2011 at 3:56 PM

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

  5. Graham said
    February 4, 2011 at 4:27 PM

    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
    February 4, 2011 at 10:15 PM

    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
    February 7, 2011 at 12:28 AM

    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
    February 7, 2011 at 12:31 AM

    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
    February 7, 2011 at 3:13 AM

    ;; 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
    February 7, 2011 at 2:16 PM

    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
    February 8, 2011 at 11:50 AM 
    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
    February 9, 2011 at 9:36 PM

    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
    February 19, 2011 at 11:36 AM

    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)

Leave a comment