How to return the column letter of cell reference

**arekkusu03** · 09-05-2012, 01:12 AM

Hi guys,

I was just wondering what formula to use to return the column letter of cell reference eg.

A1 = A
B1 = B
C1 = C

just like how the Row or Column formula shows the number of the row or column cell.

Thank you

**JieJenn** · 09-05-2012, 01:16 AM

I might be wrong, but I don't think there is a formula for that. You can probably use a formula like =CHAR(COLUMN()+96) to get convert column number to letter.

**arekkusu03** · 09-05-2012, 01:26 AM

Hi JieJenn, thanks it works

**JieJenn** · 09-05-2012, 01:42 AM

But why would you put cell reference inside column function though? The other way to do is using VBA.

**vlady** · 09-05-2012, 01:53 AM

maybe also this one if your extending to AA, AB ......---> to end

=LOWER(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")) - small letters

or

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

**wills79** · 06-29-2016, 10:20 AM

I realize this thread is four years old, but wanted to post in case someone else came along after I me with the same question. vlady's suggestion worked best for me since JieJenn's formula only works up to column Z.

**Nat55** · 04-06-2017, 11:28 AM

Thank you both, Vlady and Wills79, great solution, saved me hours!

**gothic chicken** · 11-05-2019, 11:54 PM

Originally Posted by JieJenn

I might be wrong, but I don't think there is a formula for that. You can probably use a formula like =CHAR(COLUMN()+96) to get convert column number to letter.

A perfect, simple answer to what I needed! [="Last TtC column: "&CHAR(MAX(A2:A10000)+65)]

However, the OP mentioned getting the letter of a cell reference, so reversing that as =CHAR(CODE(a1)) will give that result (up to "Z"

)

Like this:
...A...B
1 M1 M
2 A1 A
3 C7 C

**SpreckR** · 05-19-2020, 05:19 PM

Vladimir, thanks very much. This was helpful for me. I needed to column letters for documenting a very large spreadsheet

**carrollm** · 06-01-2023, 01:42 PM

the formula =CHAR(COLUMN()+96) only works thru z
is there a formula than works 2+ column letters
Thanks Carroll

**Glenn Kennedy** · 06-01-2023, 01:53 PM

carollm. It is a forum rule that you start your OWN thread... and do not piggy back on other/ancient threads. Please start your OWN and supplement it with a sample sheet showing what you have and what you want.

This thread is now closed.