Borbin the 🐱

To use a fast Excel automation, you assign the data matrix to an Excel Range, but ranges in Excel use letters for the column.

Columns start with A to Z, then continues with AA until ZZ and AAA from there all the way to the system limit.

So how to convert the column index to the Excel Column naming?

It is not a base 26 numbering schema: The ones-digit has a value range from 0..25 and all higher order digits have a range from 1..26.

Bijective base 26 on http://en.wikipedia.org/wiki/Hexavigesimal talks about this, but has a broken algorithm. AAA is not 677 but 703 (corrected for one-based numbering). The implementation skips one letter in the higher order digits.

The correct numbering is:

 1=A
  :
 26=Z
 27=AA
  :
 702=ZZ
 703=AAA

  : 

This text file contain all columns up to 20000:ACOF (and this ZIP from 1..AAAAA).

Combined with the fact that Excel indices start from 1 instead of 0, I developed the following solution (implemented in Windows Powershell):

Function ToExcelColumn([int]$col)
{
    # Author: Jürgen Eidt, Dec 2012
    # to Excel column name conversion
    [string]$base26 = ""

    # Convert to modified base26
    while ($col -gt 0)
    {
        [int]$r = ($col - 1) % 26
        $base26 = [char]([int][char]'A' + $r) + $base26
        $col = ($col - $r) / 26
    }

    $base26
}

For completeness: The reverse operation (from column representation to the actual int value):

Function ExcelColumnToInt([string]$A1)
{
    # Author: Jürgen Eidt, Dec 2012
    # Excel column name conversion

    [int]$col = 0
    [char[]]$A1.ToUpperInvariant() | % { $col = 26 * $col + [int]$_ + 1 - [int][char]'A' }

    $col
}