Convert Number to Corresponding Excel Column

Question

Hi, I need some help in doing a logic that would convert a numeric value to corresponding MS Excel header value.

For example:

1 = "A" 2 = "B" 3 = "C" 4 = "D" 5 = "E" ......... 25 = "Y" 26 = "Z" 27 = "AA" 28 = "AB" 29 = "AC" 30 = "AD" .........

Would appreciate some .NET codes (C# or VB) for this. Thanks.

Answer 1

Here's some VBA (with test code) I strung together in Excel which does the trick. Unless VB.NET has changed drastically, it should work okay. Even if it has, you should be able to translate the idea into workable code.

' num2col - translate Excel column number (1-n) into column string ("A"-"ZZ"). '

Function num2col(num As Integer) As String
    ' Subtract one to make modulo/divide cleaner. '

    num = num - 1

    ' Select return value based on invalid/one-char/two-char input. '

    If num < 0 Or num >= 27 * 26 Then
        ' Return special sentinel value if out of range. '

        num2col = "-"
    Else
        ' Single char, just get the letter. '

        If num < 26 Then
            num2col = Chr(num + 65)
        Else
           ' Double char, get letters based on integer divide and modulus. '

           num2col = Chr(num \ 26 + 64) + Chr(num Mod 26 + 65)
        End If
    End If
End Function

 

' Test code in Excel VBA. '

Sub main()
    MsgBox ("-  should be " & num2col(0))
    MsgBox ("A  should be " & num2col(1))
    MsgBox ("B  should be " & num2col(2))
    MsgBox ("Z  should be " & num2col(26))
    MsgBox ("AA should be " & num2col(27))
    MsgBox ("AB should be " & num2col(28))
    MsgBox ("AY should be " & num2col(51))
    MsgBox ("AZ should be " & num2col(52))
    MsgBox ("BA should be " & num2col(53))
    MsgBox ("ZY should be " & num2col(27 * 26 - 1))
    MsgBox ("ZZ should be " & num2col(27 * 26))
    MsgBox ("-  should be " & num2col(27 * 26 + 1))
End Sub

Answer 2

Try the highest voted answer here (don't bother with the accepted answer...)

Answer 3

public string ColumnNumberToLetter(int ColumnNumber)
{
    if (ColumnNumber > 26)
    {
        return ((char) (Math.Floor(((double)ColumnNumber - 1) / 26) + 64)).ToString()
               + ((char) (((ColumnNumber - 1) % 26) + 65)).ToString();
    }
    return ((char)(ColumnNumber+64)).ToString();
}

Answer 4

Use a number base conversion routine. You want to convert from base 10 to base 26. Add each digit to 'A'

As in: http://www.vbforums.com/showthread.php?t=271359

Answer 5

Try this:

public static string ToExcelString(int number)
{
    if (number > 25)
    {
        int secondaryCounter = 0;
     while (number > 25)
     {
          secondaryCounter = secondaryCounter + 1;
       number = number - 25;
     }
     return ToExcelChar(number) + ToExcelChar(secondaryCounter);
    }
    else
    {
        return ToExcelChar(number)
    }
}
private const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
private static string ToExcelChar(int number)
{
    if (number > 25 || number < 0)
    {
     throw new InvalidArgumentException("the number passed in (" + number + ") must be between the range 0-25");
    }
    return alphabet[number];
}

Answer 6

Just Use the activecell.address then manuipulate the string based on where the $ is to what you need.

Answer 7

Why do we need to divide an integer by Mod 26 when we want to convert column numbers to column names in XL(VBA+macro) I want to know the logic behind this - ie division of integer by Mod 26 Please explain me the following function Function GetColumnName(colNum As Integer) As String Dim d As Integer Dim m As Integer Dim name As String d = colNum name = "" Do While (d > 0) m = (d - 1) Mod 26 name = Chr(65 + m) + name d = Int((d - m) / 26) Loop GetColumnName = name End Function