How to extract just the Column Name of the active cell

F

Fred

Hello,
Does anyone know how to extract only the Column Name of an active cell in
another cell. e.g. If active cell is D4 then I want the "D" to be extract and
displayed in another cell say A1. Let me know.

Regards,
Fred
 
B

Bob Phillips

Debug.Print ColumnLetter(activecell.Column)


'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

David Billigmeier

Not the best to look at, but it will work:

=LEFT(SUBSTITUTE(CELL("address"),"$",""),IF(ISNUMBER(--(MID(SUBSTITUTE(CELL("address"),"$",""),2,1))),1,2))
 
R

Ron de Bruin

Hi Fred

You must use a event for this in the sheet module

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 26 Then
Range("A1").Value = Left(Cells(Target.Row, Target.Column).Address(False, False), 2)
Else
Range("A1").Value = Left(Cells(Target.Row, Target.Column).Address(False, False), 1)
End If
End Sub
 
Top