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.

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 612 other followers

%d bloggers like this: