VBA – Convert Excel Column Number to Letter and Letter to Column Number

Converting Excel column number to letter and letter to column number is actually something that every VBA developer does at least once a day. The easiest way is probably to use the property .Column  or .Address of the range object that you are using and to play a bit with it.

Anyway, if you want to do it with Maths and without using the Excel range properties, this is the way to do it:

Function ColumnToLetter(columnNumber As Long) As String
   
    If columnNumber < 1 Then Exit Function
    ColumnToLetter = UCase(ColumnToLetter(Int((columnNumber - 1) / 26)) & Chr(((columnNumber - 1) Mod 26) + Asc("A")))

End Function

Function LetterToColumn(letters As String) As Long
    
    Dim i As Long
    letters = UCase(letters)
    
    For i = Len(letters) To 1 Step -1
        LetterToColumn = LetterToColumn + (Asc(Mid(letters, i, 1)) - 64) * 26 ^ (Len(letters) - i)
    Next
        
End Function

And if you want to see some tests, check here:

Sub Tests()

    Debug.Print LetterToColumn("a") = 1
    Debug.Print LetterToColumn("A") = 1
    Debug.Print LetterToColumn("Z") = 26
    Debug.Print LetterToColumn("AA") = 27
    Debug.Print LetterToColumn("AZ") = 52
    Debug.Print LetterToColumn("BA") = 53
    
    Debug.Print ColumnToLetter(1) = "A"
    Debug.Print ColumnToLetter(26) = "Z"
    Debug.Print ColumnToLetter(27) = "AA"
    Debug.Print ColumnToLetter(52) = "AZ"
    Debug.Print ColumnToLetter(53) = "BA"
    
End Sub

Enjoy! 🙂