Column() convert to letter (A1 notation)

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
Is there a way to get the column letter (in A1 notation) of the active cell, as opposed to its number (from R1C1 notation).

=ADDRESS(5,10,4 ) returns row and col in A1 notation, but getting just col is a parse job

My sources of column number (R1C1)
Formula: =Column()
Code: MsgBOx("active column is " & activecell.column())
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
Scott:
Thanks for quick response.

The question is how to get only the column address in A1 notation.
I'm comfortable getting the entire address with either:
Activecell.address
or
Address(...)

Thanks, John
 

Ver101

Board Regular
Joined
Sep 2, 2011
Messages
190
Is there a way to get the column letter (in A1 notation) of the active cell, as opposed to its number (from R1C1 notation).

=ADDRESS(5,10,4 ) returns row and col in A1 notation, but getting just col is a parse job

My sources of column number (R1C1)
Formula: =Column()
Code: MsgBOx("active column is " & activecell.column())

You can use the Cell function ("address") changing all (find and replace function) except the A1 notation
 

Ver101

Board Regular
Joined
Sep 2, 2011
Messages
190

ADVERTISEMENT

You can use the Cell function ("address") changing all (find and replace function) except the A1 notation

You can also used Address function removing excess through find and replace
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,352
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this:
Code:
Sub GetColAdr()
Dim Adr As String, cAdr As String
With ActiveCell
    Adr = .Address
    For i = 1 To Len(Adr)
        If Not IsNumeric(Mid(Adr, i, 1)) Then cAdr = cAdr & Mid(Adr, i, 1)
    Next i
End With
MsgBox "Activecell is in column " & Mid(cAdr, 2, Len(cAdr) - 2)
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,873

ADVERTISEMENT

Hi John

You can get the column in A1 notation, but why do you want to do it?

Code:
MsgBox Split(ActiveCell.Address, "$")(1)
 

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
Thanks to both.
MoJoe - I was headed down your route; thanks for working code to which I can compare my parsing / conversion.
Pgc - brilliant & you introduced me to a whole new function

Will be used to report to user the col that has changed.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,105
Messages
5,570,228
Members
412,311
Latest member
ncavana
Top