Convert a decimal into an alphabetic column id?

Question

Working with some spreadsheet stuff, internally the columns are referred to by a simple decimal number (0 indexed), but for the output, I need it to be in the standard human readable format:

A, B, C, .. Z, AA, AB, AC, .. AZ, BA ..

I first thought "easy! I'll just switch to base 26 and use [A-Z] instead of [0-9A-P]", except that using this technique makes A => 0. B => 1. Therefore the sequence actually goes like this:

A, B, .. Y, Z, BA, BB

How do you convert decimals to an excel-style column name?

Answer 1

This probably isn't the most efficient way, but it's fairly terse and it works. Probably worth putting a range check in that it won't be above ZZ in production code as I suspect bad things would happen then. And of course you could expand the conditional operator into an if/else if you don't like them.

static string ColumnPrefix(int column)
{
    Debug.Assert(column >= 0, "Column would be below 'A'.");
    Debug.Assert(column <= 26 * 27, "Column would be above 'ZZ'.");

    Func<int, string> itoa = i => new string((char)('A' + i), 1);
    return column < 26 ? 
               itoa(column) : 
               itoa((column - 26) / 26) + itoa(column % 26);
}

Answer 2

Check this question:

• How to convert a column number (eg. 127) into an excel column (eg. AA)

Answer 3

Here is a VB function. By exploiting modulo and the logarithm, it is trivial and no recursion necessary:

Function ColumnLetter(ByVal colNum As Long) As String
    Dim i As Long, x As Long
    For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
        x = (26 ^ (i + 1) - 1) / 25 - 1
        If colNum > x Then
           ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ i) Mod 26 + 65)
        End If
    Next i
End Function

Soure

Answer 4

In VB .NET code and using recursion for expressiveness

  Function Decimal2ExcelAZ(ByVal number As Integer) As String
    If number < 0 Then
      Throw New Exception("Number cannot be negative")
    ElseIf number < 26 Then
      Rem 65 is the ASCII of "A"
      Return New String(Chr(65 + number) + "")        
    Else
      Return Decimal2ExcelAZ(number \ 26 - 1) + Decimal2ExcelAZ(number Mod 26)
    End If
  End Function

Answer 5

Here's the solution in PHP, taken pretty much directly from this answer by Graham:

function rowCol2Cell($row, $col) {
    $dividend = $col + 1;
    $columnName = '';

    while ($dividend > 0) {
        $modulo = ($dividend - 1) % 26;
        $columnName = chr(65 + $modulo) . $columnName;
        $dividend = (int)(($dividend - $modulo) / 26);
    }

    return $columnName . ($row + 1);
}

// rowCol2Cell(0, 0) = "A1"
// rowCol2Cell(0, 1) = "B1"
// rowCol2Cell(0,26) = "AA1"

Edit:

If you're using the PHP PEAR module Spreadsheet Excel Writer, then it has this function built-in:

Spreadsheet_Excel_Writer::rowcolToCell

I probably shoulda just RTFM, hey...

Answer 6

Wouldn't be complete without a Java version...

protected static String columnLetter( int columnNumber ) {
 if( columnNumber < 0 ) return "Err";
 if( columnNumber < 26 )
  return Character.toString((char) (columnNumber+ 65));
 return columnLetter( (columnNumber / 26) - 1) + 
  columnLetter ( columnNumber % 26 );
}

(Based on the recursive VB .NET example)