Livio/ March 29, 2018/ Excel, Excel VBA/ 0 comments

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

Download the file

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 65

Function NUM_TO_LETTERS(ByVal iNum As Variant) As String
Dim iInt As Long

iInt = (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 65

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

me

Leave a Comment

Your email address will not be published.

*
*