Convert Number to Corresponding Excel Column

Question

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

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 3

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

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