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

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