Return the column letter rather than number with c

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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)))