MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Getting the letter name of a column


Posted by Jimmy Rizos on July 26, 2001 4:33 AM

Is there any way to get the letter name of a column if
I have the column number? I know I can write a little
algorithm to figure it out, however I am wondering
if there is a builtin way to do this.

thanks
jr


Posted by Aladin Akyurek on July 26, 2001 4:59 AM

=ADDRESS(1,n),

where n is the column number or, if this is what you want,

=MID(ADDRESS(1,n),2,IF(LEN(n)>2,2,1)),

where again n is the column number.

Aladin

Posted by IML on July 26, 2001 9:57 AM

I think the second formula needs a slight change to
=MID(ADDRESS(1,n),2,IF(LEN(address(1,n)>2,2,1)).

This is much better than what I was thinking. I was thinking of combining if column is greater than 26, using the Left function on the address.

Posted by Aladin Akyurek on July 26, 2001 10:07 AM

Ian -- Try e.g., n=3,25,256 with both unmodified and modified versions. I think you probably overlooked the fact that LEN also accepts numbers, so there shouldn't a need for computing the address twice.
BTW, I reckon you also keep an eye on the sumproduct thing.

Aladin

Posted by Aladin Akyurek on July 26, 2001 10:19 AM

Thanks to you, Ian, the second formula should be:

=MID(ADDRESS(1,n),2,IF(n>26,2,1)),

where n is or the cell of the desired column number or the column number itself.

Aladin

Posted by IML on July 26, 2001 10:27 AM

Yes, both do return the same with those number. When I modify my basterdize version or your slightly more to

=MID(ADDRESS(1,n),2,IF(LEN(ADDRESS(1,n,4))>2,2,1))

I get a result of AA with 27, and A with the original though?

I'll have to look at the sumproduct some more. I tend to learn through repetition. Just seems like a few months ago a asked you to explain how an array formula works!

Posted by IML on July 26, 2001 10:28 AM

sorry, just saw your post.

Posted by Mark W. on July 26, 2001 12:48 PM

Or...

Posted by Mark W. on July 26, 2001 1:07 PM

Oops! Make that...