Translate a column index into an Excel Column Name

Question

Given a columns' index, how can you get an Excel column name?

The problem is trickier than it sounds because it's not just base-26. The columns don't wrap over like normal digits would. Even the Microsoft Support Example doesn't scale beyond ZZZ.

_{Disclaimer: This is some code I had done a while back, and it came across my desktop again today. I thought it was worthy of posting here as a pre-answered question.}

Answer 1

The answer I came up with is to get a little recursive. This code is in VB.Net:

Function ColumnName(ByVal index As Integer) As String
        Static chars() As Char = {"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c}

        index -= 1 ' adjust so it matches 0-indexed array rather than 1-indexed column

        Dim quotient As Integer = index \ 26 ' normal / operator rounds. \ does integer division, which truncates
        If quotient > 0 Then
               ColumnName = ColumnName(quotient) & chars(index Mod 26)
        Else
               ColumnName = chars(index Mod 26)
        End If
End Function

And in C#:

string ColumnName(int index)
{
    index -= 1; //adjust so it matches 0-indexed array rather than 1-indexed column

    int quotient = index / 26;
    if (quotient > 0)
        return ColumnName(quotient) + chars[index % 26].ToString();
    else
        return chars[index % 26].ToString();
}
private char[] chars = new char[] {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

The only downside it that it uses 1-indexed columns rather than 0-indexed.

Answer 2

Here's Joel's awesome code modified to work with zero-based column indexes and without the char array.

 Public Shared Function GetExcelColumn(ByVal index As Integer) As String

        Dim quotient As Integer = index \ 26 ' Truncate 
        If quotient > 0 Then
            Return GetExcelColumn(quotient - 1) & Chr((index Mod 26) + 64).ToString

        Else
            Return Chr(index + 64).ToString

        End If

    End Function

Answer 3

It's for this very reason that I avoid column names in programmed interface to Excel. Using column numbers works very well in Cell(r,c) references and R1C1 addressing.

EDIT: The Range function also takes cell references, as in Range(Cell(r1,c1),Cell(r2,c2)). Also, you can use the Address function to get the A1-style address of a cell or range.

EDIT2: Here's a VBA function that uses the Address() function to retrieve the column name:

Function colname(colindex)
    x = Cells(1, colindex).Address(False, False) ' get the range name (e.g. AB1)
    colname = Mid(x, 1, Len(x) - 1)              ' return all but last character
End Function

Answer 4

public static String translateColumnIndexToName(int index) {
        //assert (index >= 0);

        int quotient = (index)/ 26;

        if (quotient > 0) {
            return translateColumnIndexToName(quotient-1) + (char) ((index % 26) + 65);
        } else {
            return "" + (char) ((index % 26) + 65);
        }


    }

and the test:

for (int i = 0; i < 100; i++) {
            System.out.println(i + ": " + translateColumnIndexToName(i));
}

here is the output:

0: A
1: B
2: C
3: D
4: E
5: F
6: G
7: H
8: I
9: J
10: K
11: L
12: M
13: N
14: O
15: P
16: Q
17: R
18: S
19: T
20: U
21: V
22: W
23: X
24: Y
25: Z
26: AA
27: AB
28: AC

I needed 0 based for POI

and translation from index to names:

public static int translateComunNameToIndex0(String columnName) {
        if (columnName == null) {
            return -1;
        }
        columnName = columnName.toUpperCase().trim();

        int colNo = -1;

        switch (columnName.length()) {
            case 1:
                colNo = (int) columnName.charAt(0) - 64;
                break;
            case 2:
                colNo = ((int) columnName.charAt(0) - 64) * 26 + ((int) columnName.charAt(1) - 64);
                break;
            default:
                //illegal argument exception
                throw new IllegalArgumentException(columnName);
        }

        return colNo;
    }

Answer 5

# Python 2.x, no recursive function calls

def colname_from_colx(colx):
    assert colx >= 0
    colname = ''
    r = colx
    while 1:
        r, d = divmod(r, 26)
        colname = chr(d + ord('A')) + colname
        if not r:
            return colname
        r -= 1

Answer 6

This is an old post, but after seeing some of the solutions I came up with my own C# variation. 0-Based, without recursion:

public static String GetExcelColumnName(int columnIndex)
{
    if (columnIndex < 0)
    {
        throw new ArgumentOutOfRangeException("columnIndex: " + columnIndex);
    }
    Stack<char> stack = new Stack<char>();
    while (columnIndex >= 0)
    {
        stack.Push((char)('A' + (columnIndex % 26)));
        columnIndex = (columnIndex / 26) - 1;
    }
    return new String(stack.ToArray());
}

Here are some test results at key transition points:

0: A
1: B
2: C
...
24: Y
25: Z
26: AA
27: AB
...
50: AY
51: AZ
52: BA
53: BB
...
700: ZY
701: ZZ
702: AAA
703: AAB

Answer 7

in python, with recursion. translated from Joey's answer. so far, it's tested to work up to GetExcelByColumn(35) = 'AI'

def GetExcelColumn(index):

    quotient = int(index / 26)

    if quotient > 0:
        return GetExcelColumn(quotient) + str(chr((index % 26) + 64))

    else:
        return str(chr(index + 64))

Answer 8

JavaScript Solution

/**
 * Calculate the column letter abbreviation from a 0 based index
 * @param {Number} value
 * @returns {string}
 */
getColumnFromIndex = function (value) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    value++;
    var remainder, result = "";
    do {
        remainder = value % 26;
        result = base[(remainder || 26) - 1] + result;
         value = Math.floor(value / 26);
    } while (value > 0);
    return result;
};

Answer 9

The php version, thank's to this post to help me figure it out ! ^^

/**
 * Get excel column name
 * @param index : a column index we want to get the value in excel column format
 * @return (string) : excel column format
 */
function getexcelcolumnname($index) {
    //Get the quotient : if the index superior to base 26 max ?
    $quotient = $index / 26;
    if ($quotient >= 1) {
        //If yes, get top level column + the current column code
        return getexcelcolumnname($quotient-1). chr(($index % 26)+65);
    } else {
        //If no just return the current column code
        return chr(65 + $index);
    }
}

Answer 10

Here is my solution in C#

// test
void Main()
{

    for( var i = 0; i< 1000; i++ )
    {   var byte_array = code( i );
        Console.WriteLine("{0} | {1} | {2}", i, byte_array, offset(byte_array));
    }
}

// Converts an offset to AAA code
public string code( int offset )
{
    List<byte> byte_array = new List<byte>();
    while( offset >= 0 )
    {
        byte_array.Add( Convert.ToByte(65 + offset % 26) );
        offset = offset / 26 - 1;
    }
    return ASCIIEncoding.ASCII.GetString( byte_array.ToArray().Reverse().ToArray());
}

// Converts AAA code to an offset
public int offset( string code)
{
    var offset = 0;
    var byte_array = Encoding.ASCII.GetBytes( code ).Reverse().ToArray();
    for( var i = 0; i < byte_array.Length; i++ )
    {
        offset += (byte_array[i] - 65 + 1) * Convert.ToInt32(Math.Pow(26.0, Convert.ToDouble(i)));
    }
    return offset - 1;
}

Answer 11

Here is my answer in C#, for translating both ways between column index and column name.

/// <summary>
/// Gets the name of a column given the index, as it would appear in Excel.
/// </summary>
/// <param name="columnIndex">The zero-based column index number.</param>
/// <returns>The name of the column.</returns>
/// <example>Column 0 = A, 26 = AA.</example>
public static string GetColumnName(int columnIndex)
{
    if (columnIndex < 0) throw new ArgumentOutOfRangeException("columnIndex", "Column index cannot be negative.");

    var dividend = columnIndex + 1;
    var columnName = string.Empty;

    while (dividend > 0)
    {
        var modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo) + columnName;
        dividend = (dividend - modulo) / 26;
    }

    return columnName;
}

/// <summary>
/// Gets the zero-based column index given a column name.
/// </summary>
/// <param name="columnName">The column name.</param>
/// <returns>The index of the column.</returns>
public static int GetColumnIndex(string columnName)
{
    var index = 0;
    var total = 0;
    for (var i = columnName.Length - 1; i >= 0; i--)
        total += (columnName.ToUpperInvariant()[i] - 64) * (int)Math.Pow(26, index++);

    return total - 1;
}

Answer 12

In Ruby:

class Fixnum
  def col_name
    quot = self/26
    (quot>0 ? (quot-1).col_name : "") + (self%26+65).chr
  end
end

puts 0.col_name # => "A"
puts 51.col_name # => "AZ"

Answer 13

This JavaScript version shows that at its core it's a conversion to base 26:

function colName(x)
{
    x = (parseInt("ooooooop0", 26) + x).toString(26);
    return x.slice(x.indexOf('p') + 1).replace(/./g, function(c)
    {
        c = c.charCodeAt(0);
        return String.fromCharCode(c < 64 ? c + 17 : c - 22);
    });
}

The .toString(26) bit shows that Joel Coehoorn is wrong: it is a simple base conversion.

(Note: I have a more straight-forward implementation based on Dana's answer in production. It's less heavy, works for larger numbers although that won't affect me, but also doesn't show the mathematical principle as clearly.)

P.S. Here's the function evaluated at important points:

0 A
1 B
9 J
10 K
24 Y
25 Z
26 AA
27 AB
700 ZY
701 ZZ
702 AAA
703 AAB
18276 ZZY
18277 ZZZ
18278 AAAA
18279 AAAB
475252 ZZZY
475253 ZZZZ
475254 AAAAA
475255 AAAAB
12356628 ZZZZY
12356629 ZZZZZ
12356630 AAAAAA
12356631 AAAAAB
321272404 ZZZZZY
321272405 ZZZZZZ
321272406 AAAAAAA
321272407 AAAAAAB
8353082580 ZZZZZZY
8353082581 ZZZZZZZ
8353082582 AAAAAAAA
8353082583 AAAAAAAB

Answer 14

this is with Swift 4 :

@IBAction func printlaction(_ sender: Any) {
    let textN : Int = Int (number_textfield.text!)!
    reslut.text = String (printEXCL_Letter(index: textN))
}


func printEXCL_Letter(index : Int) -> String {

    let letters = ["a", "b", "c","d", "e", "f","g", "h", "i","j", "k", "l","m", "n", "o","p", "q", "r","s", "t", "u","v","w" ,"x", "y","z"]

    var index = index;
    index -= 1
    let index_div = index / 26

    if (index_div > 0){
        return printEXCL_Letter(index: index_div) + letters[index % 26];
    }
    else {
        return letters[index % 26]
    }
}

Answer 15

I enjoy writing recursive functions, but I don't think it's necessary here. This is my solution in VB. It works up to column ZZ. If someone can tell me if it works for AAA to ZZZ that would be nice to know.

Public Function TranslateColumnIndexToName(index As Integer) As String
'
Dim remainder As Integer
Dim remainder2 As Integer
Dim quotient As Integer
Dim quotient2 As Integer
'
quotient2 = ((index) / (26 * 26)) - 2
remainder2 = (index Mod (26 * 26)) - 1
quotient = ((remainder2) / 26) - 2
remainder = (index Mod 26) - 1
'
If quotient2 > 0 Then
    TranslateColumnIndexToName = ChrW(quotient2 + 65) & ChrW(quotient + 65) & ChrW(remainder + 65)
ElseIf quotient > 0 Then
    TranslateColumnIndexToName = ChrW(quotient + 65) & ChrW(remainder + 65)
Else
    TranslateColumnIndexToName = ChrW(remainder + 65)
End If 

End Function