Reputation: 485
I am using
=CHAR(COLUMN(A1)+96)
To return the column letter, which is fine in the instance above.
However, once the column goes beyond Z
into AA
and so on, the column letter will not return properly.
I understand why, as it has reached the end of the alphabet set and beyond that are other characters.
Is there another way to accurately return the column letter?
Upvotes: 0
Views: 5217
Reputation: 1337
I'm not sure why you would want it but this will get it for you The cell being referenced here is D1):
=MID(CELL("address",ASD1),2,FIND("$",RIGHT(CELL("address",ASD1),LEN(CELL("address",ASD1))-2)))
Upvotes: 1
Reputation: 26670
Alternate method of getting the column letter:
=SUBSTITUTE(ADDRESS(1,COLUMN(A1)+26,4,1),1,"")
However, as Scott has already pointed out, depending on the need, there is probably a better way to accomplish your end goal.
Upvotes: 2
Reputation: 152660
To get the column letter use this:
=LEFT(ADDRESS(1,COLUMN(A1),4,1),IF(ISNUMBER(--MID(ADDRESS(1,COLUMN(A1),4,1),2,1)),1,2))
If you want it in lower case:
=LOWER(LEFT(ADDRESS(1,COLUMN(A1),4,1),IF(ISNUMBER(--MID(ADDRESS(1,COLUMN(A1),4,1),2,1)),1,2)))
But as I said in my comment if you want to use it in another formula there are quicker and better methods.
Upvotes: 1