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