How to extract just the Column Name of the active cell
 

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

How to extract just the Column Name of the active cell
 

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)


"Fred" wrote in message
...
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

How to extract just the Column Name of the active cell
 

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

=LEFT(SUBSTITUTE(CELL("address"),"$",""),IF(ISNUMB ER(--(MID(SUBSTITUTE(CELL("address"),"$",""),2,1))),1,2 ))


--
Regards,
Dave


"Fred" wrote:

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

How to extract just the Column Name of the active cell
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Fred" wrote in message ...
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

How to extract just the Column Name of the active cell
 

It doesn't update very well, but...

=CHAR(CELL("col")+64)

"Fred" wrote:

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