Opposite of COLUMN command?

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
 
R

Roger Govier

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,"")
 
B

Bernard Liengme

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
 
G

Gord Dibben

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
 
S

Scott Parsley

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?
 
R

Rick Rothstein

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
 
S

Shane Devenshire

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.
 
Top