convert Excel column numbers into alphabetical characters

[CT Witter]

I am using the following macro from the ms site...How to convert Excel column numbers into alphabetical characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;833402&Product=xl2002

However, it is exhibiting strange behavior, works fine until column EA (#131) I get D[ instead of EA. This goes on until 135 (EE) then works fine again until 156 FA

Is there something wrong? What do I need to do differently?

CT

 

[Davexx]

Re: convert Excel column numbers into alphabetical character

ok, here's another, thougt a split might work. (i imagine these 2 methods can be turned into functions as well)

colchar = split(activecell.address, "$")(1)
MsgBox colchar & Space(10), vbQuestion

 

[Scott Huish]

Re: convert Excel column numbers into alphabetical character

Function ConvertToLetter(r As Range) As String
ConvertToLetter = Split(r.Address, "$")(1)
End Function

 

[Scott Huish]

Here is a formula based solution that does the same thing:

=SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,"")

Change the A1 to whatever you want or you can take out the A1 to use the column the formula is in.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

Excel 2010
	D	E	F	G	H	I	J
2	A	B	C	D	E	F	G
Sheet1

Cell Formulas
Range	Formula
D2	=SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,"")
E2	=SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),1,"")
F2	=SUBSTITUTE(ADDRESS(1,COLUMN(C1),4),1,"")
G2	=SUBSTITUTE(ADDRESS(1,COLUMN(D1),4),1,"")
H2	=SUBSTITUTE(ADDRESS(1,COLUMN(E1),4),1,"")
I2	=SUBSTITUTE(ADDRESS(1,COLUMN(F1),4),1,"")
J2	=SUBSTITUTE(ADDRESS(1,COLUMN(G1),4),1,"")

Excel 2010
	D	E	F	G	H	I	J
2	D	E	F	G	H	I	J
Sheet1

Cell Formulas
Range	Formula
D2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
E2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
F2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
G2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
H2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
I2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
J2	=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")