Excel VBA – Number to Letters Series Progression
The other day I saw a forum question where somebody wanted to convert a number to a letter sequence going from A to Z (26 letters in total). For instance if he entered 3 he wanted to get C, if he entered 26 he wanted to get Z, but if he entered 27 he wanted to get AA (the progression has to restart at each 26). In some situations you may want to do something like that, for instance if you want to know the column name of column number 245. Excel VBA Number to Letters Series Progression
Number to letters solution
The solution I came up with is below:
Const NumberOfLettersFromAtoZ As Long = 26 ‘ number of alphabetic letters from A to Z
Const LettersAStartAtValue As Long = 65 ‘ letter A starta at character 65Function NUM_TO_LETTERS(ByVal iNum As Variant) As String
Dim iInt As LongiInt = (iNum – 1) \ NumberOfLettersFromAtoZ
If iInt > 0 Then
NUM_TO_LETTERS = NUM_TO_LETTERS(iInt) & Chr$(((iNum – 1) Mod NumberOfLettersFromAtoZ) + LettersAStartAtValue)
Else
NUM_TO_LETTERS = Chr$(iNum + LettersAStartAtValue – 1)
End If
End Function
Letters to Number solution
It is also possible to do the inverse, going from letters to number:
Const NumberOfLettersFromAtoZ As Long = 26 ‘ number of alphabetic letters from A to Z
Const LettersAStartAtValue As Long = 65 ‘ letter A starta at character 65Function LETTER_TO_NUM(ByVal inputStr As String) As Variant
If Len(inputStr) = 0 Then
LETTER_TO_NUM = 0
Else
LETTER_TO_NUM = (Asc(Left$(inputStr, 1)) – LettersAStartAtValue + 1) * (NumberOfLettersFromAtoZ ^ (Len(inputStr) – 1)) + LETTER_TO_NUM(Mid$(inputStr, 2))
End If
End Function
Post your comments if you have other solutions or if you find this useful!