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.
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Get answers right away at our AE Excel Support Forums!
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
Hi all,
Just wanted to point out one major flaw with the original post. If the Num_Column is equal to 26 then the remainder will be 0. The value falls in the else condition where the first part of Alpha_Column formula “Chr(Int(Num_Column / 26) + 64)” is 65 which is equal to the ASCII Chr of A. In second part of the Alpha_Column formula “Chr((Num_Column Mod 26) + 64)” is the remainder, 0, plus 64, which is 64. The ASCII value for 64 is “@”. Alpha_Column is then equal to “A@” which is definitely wrong. So to correct this simply modify the operator in the statement, “If Num_Column < 26 Then" to "If Num_Column <= 26 Then".
@Kiran,
Four characters shorter, but more importantly, two function calls less…
=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND(“$”,ADDRESS(ROW(),COLUMN(),2))-1)
Same as Andy, just 1 char even less and just 3 functions and may be the shortest;)
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),”1″,”")
This one works.
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
Dim firstColumnLetter As String
Dim secondColumnLetter As String
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
If Num_Column Mod 26 0 Then
firstColumnLetter = Chr(Int(Num_Column / 26) + 64)
secondColumnLetter = Chr((Num_Column Mod 26) + 64)
Else
firstColumnLetter = Chr(Int(Num_Column / 26) + 63)
secondColumnLetter = “Z”
End If
Alpha_Column = firstColumnLetter & secondColumnLetter
End If
End Function
A simple function for all cases:
——————————–
Function getColNameFromIndex(ByVal colIndex As Integer) As String
getColNameFromIndex = “”
If (colIndex = 16385) Then
Exit Function
End If
getColNameFromIndex = Left(Cells(1, colIndex).Address(1, 0), InStr(1, Cells(1, colIndex).Address(1, 0), “$”) – 1)
End Function
A simple function for all cases:
——————————–
Function getColNameFromIndex(ByVal colIndex As Integer) As String
getColNameFromIndex = “”
getColNameFromIndex = Left(Cells(1, colIndex).Address(1, 0), InStr(1, Cells(1, colIndex).Address(1, 0), “$”) – 1)
End Function