[ACCEPTED]-Translate a column index into an Excel Column Name-excel

The answer I came up with is to get a little 3 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 2 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 1 columns rather than 0-indexed.

Here's Joel's awesome code modified to work 2 with zero-based column indexes and without 1 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

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

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

EDIT2: Here's 2 a VBA function that uses the Address() function 1 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

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 2 0 based for POI

and translation from index 1 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;
    }

# 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

0

This is an old post, but after seeing some 3 of the solutions I came up with my own C# variation. 0-Based, without 2 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 1 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

The php version, thank's to this post to 1 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);
    }
}

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;
};

0

in python, with recursion. translated from 1 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))

I enjoy writing recursive functions, but 5 I don't think it's necessary here. This 4 is my solution in VB. It works up to column 3 ZZ. If someone can tell me if it works for 2 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 1 Function

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;
}

0

Here is my answer in C#, for translating 2 both ways between column index and column 1 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;
}

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"

0

This JavaScript version shows that at its 9 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 8 shows that Joel Coehoorn is wrong: it is 7 a simple base conversion.

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

P.S. Here's 1 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

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]
    }
}

0