CODEWITHSUNDEEP
excelexcel-formula
Aurelius
Aurelius

Reputation: 485

Return the column letter rather than number with column "AA" onwards

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

Answers (3)

Jeremy
Jeremy

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

tigeravatar
tigeravatar

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

Scott Craner
Scott Craner

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

Related Questions