VBA Routine to return Column Letter of Cell
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.
I always use this:
Function Alpha_Column(Cell_Add as Range)
Alpha_Column = Replace(Replace(Cell_Add,”$”,”"),Row(Cell_Add),”")
End Function
Stan
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)
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
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
=LEFT(ADDRESS(ROW(),COLUMN(),4),LEN(ADDRESS(ROW(),COLUMN(),4))-LEN(ROW()))
works fine for me.
Thank you Andy Pope! You saved me a whole lotta time and effort! This is exactly what I needed since I can’t write VBA (yet!).
Here is the UDF that I use…
Function ColumnLetter(Cell_Add As Range) As String
ColumnLetter = Split(Cell_Add.Address, “$”)(1)
End Function
Came up with this not too long ago:
Sub GetColumn(ByVal row As Integer, ByVal col As Integer, ByRef letter As String)
addr = Sheet1.Cells(row, col).Address(False, False)
letter = Left(addr, 1)
End Sub
Called like this:
Call GetColumn(r, c, colLetter)
Doing that stores the column letter in the variable colLetter. Integers can be input for r and c to indicate which cell you are talking about.
@Andyman
Two points… First, you don’t really need the row to be specified as you can use hard code any row number into the Cells property call (I would suggest using 1) since the column letter is not dependent on the row number. Second, you code will return the wrong answer if the column number is greater than 26.
Why not just use the following:
[range object].column
where [range object] can be Selection, ActiveCell, or any variable declared as Range. Then use Cells(x, y) for whatever you need.
It’s easier for loops to use numbers than letters. (That’s meant to invoke a smile or two from those who get it.)
Kev, your solution is perfect
=LEFT(ADDRESS(ROW(),COLUMN(),4),LEN(ADDRESS(ROW(),COLUMN(),4))-LEN(ROW()))
returns column name correctly