The standard Excel “Column” Function returns the number rather than the letter of the column e.g:
Column(E4) – returns the number 5 rather than the letter E
Column(AD12) returns the number 30 rather than AD.
The following function returns the letter rather than the number of the column. So in the above two examples we have the letters E and AD respectively . The routine will return blank if more than a single cell is referenced:
Function Alpha_Column(Cell_Add As Range) As String
Dim No_of_Rows As Integer
Dim No_of_Cols As Integer
Dim Num_Column As Integer
No_of_Rows = Cell_Add.Rows.Count
No_of_Cols = Cell_Add.Columns.Count
If ((No_of_Rows <> 1) Or (No_of_Cols <> 1)) Then
Alpha_Column = ""
Exit Function
End If
Num_Column = Cell_Add.Column
If Num_Column < 26 Then
Alpha_Column = Chr(64 + Num_Column)
Else
Alpha_Column = Chr(Int(Num_Column / 26) + 64) & Chr((Num_Column Mod 26) + 64)
End If
End Function
To download the .XLSM file from this article, click here.
November 4th, 2008 at 5:58 am
I always use this:
Function Alpha_Column(Cell_Add as Range)
Alpha_Column = Replace(Replace(Cell_Add,”$”,”"),Row(Cell_Add),”")
End Function
Stan
November 4th, 2008 at 6:37 am
Couldn’t all that for determining the column letter be reduced to this:
Alpha_Column = Mid(Cell_Add.Address, 2, InStr(2, Cell_Add.Address, “$”) – 2)
November 4th, 2008 at 1:55 pm
I did this UDF before I saw Nick’s simple worksheet formula. Both are simpler than the original.
Public Function ColumnLetters(rng As Range)
Dim sAddr As String
Dim sTrim As String
sAddr = rng.Address(True, True, xlA1)
sTrim = Mid(sAddr, 2)
sTrim = Left(sTrim, InStr(sTrim, “$”) – 1)
ColumnLetters = sTrim
End Function
November 5th, 2008 at 4:24 pm
I would use worksheet formula
=SUBSTITUTE(ADDRESS(1,COLUMN(AD12),2),”$1″,”")
Or UDF
Function ColumnLetter(Cell_Add) As String
ColumnLetter = Replace(Cell_Add.EntireColumn.Cells(1).Address(, False), “$1″, “”)
End Function
November 20th, 2008 at 12:08 pm
=LEFT(ADDRESS(ROW(),COLUMN(),4),LEN(ADDRESS(ROW(),COLUMN(),4))-LEN(ROW()))
works fine for me.