Function to convert column number to letter?

Question

This function returns the column letter for a given column number.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

testing code for column 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

If you'd rather not use a range object:

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

Something that works for me is:

Cells(Row,Column).Address 

This will return the $AE$1 format reference for you.

• For example: MsgBox Columns( 9347 ).Address returns .

---

To return ONLY the column letter(s): Split((Columns(Column Index).Address(,0)),":")(0)

• For example: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) returns .

---

  

---

And a solution using recursion:

Function ColumnNumberToLetter(iCol As Long) As String

    Dim lAlpha As Long
    Dim lRemainder As Long

    If iCol <= 26 Then
        ColumnNumberToLetter = Chr(iCol + 64)
    Else
        lRemainder = iCol Mod 26
        lAlpha = Int(iCol / 26)
        If lRemainder = 0 Then
            lRemainder = 26
            lAlpha = lAlpha - 1
        End If
        ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + 64)
    End If

End Function