Opposite of COLUMN command?

  • Thread starter Thread starter Neil Perry
  • Start date Start date
N

Neil Perry

Is there a way to identify a column based on a number?

Using the COLUMN command, I can calculate that column(Z1) = 26,
column(AA1)=27.

However, is there a reverse command for this? In otherwords, what column is
at 26-10=16 = column P.

Thanks in advance,
Neil
 
Hi Neil

If you are always dealing with columns less than Z, then a simple
=CHAR(64+16) will return P

=ADDRESS(1,16) will return $P$1

If you want just the letter P then
=SUBSTITUTE(SUBSTITUTE(ADDRESS(1,16),"$",""),1,"")
 
The ASCII value of A is 65, so CHAR(65) returns "A"
IF G12 hold a numbers (say 12), then =CHAR(G12+64) will return the column
letter (in this case P)
But this fails after Z

However, =ADDRESS(1,G12,4) will return P1 when G12 hold value 16
and =LEFT(ADDRESS(1,G12,4),FIND("1",G14)-1) returns KN when G12 hold value
300

best wishes
 
In Excel 2003 this UDF will return the column letter for any number up to
256.................maybe 16,384 if using Excel 2007?

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

=getcollet(26-10) returns P


Gord Dibben MS Excel MVP
 
Can you not use some form of...

=ADDRESS(1,27,4) - returns AA1
=ADDRESS(1,26-10,4) - returns P1

....and parse the result of that somehow?
 
If you are looking for a VB solution, here is a somewhat shorter function
for you to consider...

Function GetColLet(ColNumber As Integer) As String
GetColLet = Split(Columns(ColNumber).Address(0, 0), ":")(0)
End Function
 
Hi,

And a single spreadsheet formula that will do it is

=MID(ADDRESS(1,A2),2,FIND("$",ADDRESS(1,A2),2)-2)
where you can replace the A2 reference with any calculation or cell
reference you want. A2 contains the column number that you want the letter
for.
 
Back
Top