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.

About these ads

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)
    
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 598 other followers

%d bloggers like this: