How to Convert a Column Number (E.G. 127) into an Excel Column (E.G. Aa)

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

Here's how I do it:

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";

    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    } 

    return columnName;
}

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

Here's how I do it:

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";

    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    } 

    return columnName;
}

How to convert Excel column name to number and vice versa?

For AB from 28 try,

dim cola as string

cola = split(cells(1, 28).address, "$")(1)

debug.print cola

For 28 from AB try,

dim coli as long

coli = cells(1, "AB").column

debug.print coli

On the worksheet type AB in A1 and use this formula in B1 to get 28,

=column(indirect(a1&":"&a1))

On the worksheet type 28 in A1 and use this formula in B1 to get AB,

=substitute(address(1, a1, 4, 1), 1, "")

---

Related Topics