Convert Number to Corresponding Excel Column
13,184
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
Author by
T.lu
Updated on June 27, 2022Comments
-
T.lu almost 2 years
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.
-
Lance Roberts almost 15 yearsIt isn't Base26, you'll be off a little.
-
Ilia Hadzhiev almost 15 yearsFeeling confused now. What base is it? A-Z for each digit. Isn't that 26 possibilities?
-
Admin over 9 years@TheJacobTaylor Because you're missing
0
in that scheme. One doesn't count8, 9, 11, 12, ... 19, 21, 22 ...
Remember thatA
is for1
. Incrementing Z by 1 should give you A0 (which isn't in this system), not AA.