Convert an Excel column number to a column name or letter: C# and VB.NET examples

Excel worksheet size has increased dramatically from Excel 2000 to Excel 2007. In fact, the number of rows an Excel 2013 worksheet can support has increased 16 times and the number of columns 64 times more than the number Excel 2000 could handle. Below is a table displaying the number of columns and rows the different versions of Excel can contain:

• Getting the column letter by column number
• Getting the column index by column name

Getting the column letter by column index

There are a lot of examples floating around on the internet on how to convert Excel column numbers to alphabetical characters. There are a few ways to get the column letter, using either vanilla C# or VB.NET, Excel formulas or the Excel object model. Let’s take a look at some of the solutions.

Using C# or VB.NET

C# example:

static string ColumnIndexToColumnLetter(int colIndex)
{
    int div = colIndex;
    string colLetter = String.Empty;
    int mod = 0;
 
    while (div > 0)
    {
        mod = (div - 1) % 26;
        colLetter = (char)(65 + mod) + colLetter;
        div = (int)((div - mod) / 26);
    }
    return colLetter;
}

C# usage:

string columnLetter = ColumnIndexToColumnLetter(100); // returns CV

VB.NET example:

Private Function ColumnIndexToColumnLetter(colIndex As Integer) As String
    Dim div As Integer = colIndex
    Dim colLetter As String = String.Empty
    Dim modnum As Integer = 0
 
    While div > 0
        modnum = (div - 1) Mod 26
        colLetter = Chr(65 + modnum) & colLetter
        div = CInt((div - modnum) \ 26)
    End While
 
    Return colLetter
End Function

VB.NET usage:

Dim columnLetter As String = ColumnIndexToColumnLetter(85) ' returns CG

Using an Excel formula to convert a column number to column letter

Of course, if you’re in Excel and need to get the column letter based on a number, you can always use the ADDRESS function. The function is pretty straight forward; it requires a row and column number, and in our case, we need to specify the abs_num parameter, which can be one of four possible options:

Consider the following formula:
=ADDRESS(1,5,1)

By changing the abs_num parameter, you’ll see the following result:

Notice that in all the four cases, we get the column letter, in this case E, back as well as the row number. By setting the abs_num parameter to 4 and replacing the row number, we can effectively return the column letter using the following formula:

=SUBSTITUTE(ADDRESS(1,5,4),”1″,””)

Using the Excel Object Model to convert column numbers to alphabetic format

Of course, you could always use the Excel object model to get the column letter. Much like the ADDRESS function, you use the Address property of the Range object to determine the address of the column and then simply replace the row number as illustrated below:

C# example:

private void columnLetterRibbonButton_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    Worksheet sheet = null;
    Range range = null;
    string colLetter = string.Empty;
 
    try
    {
        sheet = (Worksheet)ExcelApp.ActiveSheet;
        range = sheet.Cells[1, 50] as Excel.Range;
        colLetter = range.Address[false, false, XlReferenceStyle.xlA1];
        colLetter = colLetter.Replace("1", "");
        MessageBox.Show(String.Format(
            "Column letter for column number 50 is : {0}", colLetter));
    }
    finally
    {
        if (sheet != null) Marshal.ReleaseComObject(sheet);
        if (range != null) Marshal.ReleaseComObject(range);
    }

VB.NET example:

Private Sub GetColumnLetterRibbonButton_OnClick(sender As Object, _
    control As IRibbonControl, pressed As Boolean) Handles _
    GetColumnLetterRibbonButton.OnClick
 
    Dim sheet As Excel.Worksheet = Nothing
    Dim range As Excel.Range = Nothing
    Dim colLetter As String = String.Empty
 
    Try
        sheet = DirectCast(ExcelApp.ActiveSheet, Excel.Worksheet)
        range = TryCast(sheet.Cells(1, 50), Excel.Range)
        colLetter = range.Address(False, False, Excel.XlReferenceStyle.xlA1)
        colLetter = colLetter.Replace("1", "")
        MessageBox.Show(String.Format( _
            "Column letter for column number 50 is : {0}", colLetter))
    Finally
        If sheet IsNot Nothing Then
            Marshal.ReleaseComObject(sheet)
        End If
        If range IsNot Nothing Then
            Marshal.ReleaseComObject(range)
        End If
    End Try
End Sub

Getting the column index by column letter

What should you do in order to reverse the scenario? How do you get the Excel column letter when all you have is the column index? Let’s take a look at how you can accomplish this using C#, VB.NET, an Excel formula and the Excel object model.

Using C# or VB.NET

C# example:

public static int ColumnLetterToColumnIndex(string columnLetter)
{
    columnLetter = columnLetter.ToUpper();
    int sum = 0;
 
    for (int i = 0; i < columnLetter.Length; i++)
    {
        sum *= 26;    
        sum += (columnLetter[i] - 'A' + 1);
    }
    return sum;
}

C# usage:

int columnIndex = ColumnLetterToColumnIndex("XFD"); // returns 16384

VB.NET example:

Public Function ColumnLetterToColumnIndex(columnLetter As String) As Integer
    columnLetter = columnLetter.ToUpper()
    Dim sum As Integer = 0
 
    For i As Integer = 0 To columnLetter.Length - 1
        sum *= 26
        Dim charA As Integer = Char.GetNumericValue("A")
        Dim charColLetter As Integer = Char.GetNumericValue(columnLetter(i))
        sum += (charColLetter - charA) + 1
    Next
    Return sum
End Function

VB.NET usage:

Dim columnIndex As Integer = ColumnLetterToColumnIndex("ADX") ' returns 703

Using an Excel formula to get the column number

Getting the column index directly in Excel is very easy. Excel has a built-in COLUMN() function. This function accepts a column reference, which is the column address. One catch though, is you have to combine the row number with the column address in order for it to work, e.g.:
=COLUMN(ADX1)

Using the Excel Object Model to convert a column letter to column number

Getting the column index is easily accomplished using the Excel object model. You first need to get a reference to a range object and then get the column number via the Column property of the Range object.

C# example:

private void getColumnIndexRibbonButton_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    Worksheet sheet = null;
    Range range = null;
    int colIndex = 0;
 
    try
    {
        sheet = (Worksheet)ExcelApp.ActiveSheet;
        range = sheet.Range["ADX1"] as Excel.Range;
        colIndex = range.Column;
        MessageBox.Show(String.Format(
            "Column index for column letter ADX is : {0}", colIndex));
    }
    finally
    {
        if (sheet != null) Marshal.ReleaseComObject(sheet);
        if (range != null) Marshal.ReleaseComObject(range);
    }
}

VB.NET example:

Private Sub GetColumnIndexRibbonButton_OnClick(sender As Object, _
    control As IRibbonControl, pressed As Boolean) Handles _
    GetColumnIndexRibbonButton.OnClick
 
    Dim sheet As Excel.Worksheet = Nothing
    Dim range As Excel.Range = Nothing
    Dim colIndex As Integer = 0
 
    Try
        sheet = DirectCast(ExcelApp.ActiveSheet, Excel.Worksheet)
        range = TryCast(sheet.Range("ADX1"), Excel.Range)
        colIndex = range.Column
        MessageBox.Show([String].Format( _
            "Column index for column letter ADX is : {0}", colIndex))
    Finally
        If sheet IsNot Nothing Then
            Marshal.ReleaseComObject(sheet)
        End If
        If range IsNot Nothing Then
            Marshal.ReleaseComObject(range)
        End If
    End Try
End Sub

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Sample add-ins and applications (C# and VB.NET)

You may also be interested in:

• Creating COM add-ins for Excel 2013 – 2007. Customizing Excel Ribbon
• Excel add-in development in Visual Studio