Auto input column letter headings for columns > "Z" in Excel

Hello,

In Excel (2007), I have been using the following formula to generate letters (rather than numbers) to represent particular columns:

    =UPPER(CHAR(COLUMN(A1)+96))

However, that method is only useful for columns A-Z.  Beginning with column AA, the formula returns non-alphabetic symbols.  

Is there a different formula which generates column letters but does so for all columns rather than just the first 26?

Thanks
Steve_BradyAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The formula below returns the column letter or letters given a column number.

   =SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
In case the row the formula is in is not row 1:

   =SUBSTITUTE(ADDRESS(1,COLUMN(),4),ROW(),)

Kevin
0
 
AlanConsultantCommented:

Try this:

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

for the column it is entered in,

OR

=SUBSTITUTE(ADDRESS(1,COLUMN(ac5),4),"1","")

for the column of E5.


HTH,

Alan.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
AlanConsultantCommented:
Sorry, that last narrative should have read AC5.

Just change the reference in the formula as required.


Alan.
0
 
AlanConsultantCommented:
However, can I ask *why* you want this?

I suspect there is a much easier way to achieve your actual objective, that going down this route.

Alan.
0
 
Steve_BradyAuthor Commented:
Thank you for the helpful responses.

Alan3285:
>>I suspect there is a much easier way to achieve your actual objective, that going down this route.


Alan, I appreciate that follow-up question and I would like to see if there is an easier way to approach it. However, since the question in this thread has already been answered, I will begin a new thread in a few minutes with the follow-up question there.

Thanks again.
0
 
AlanConsultantCommented:
Okay - no problem.

Alan.
0
 
Steve_BradyAuthor Commented:
Reference file added with a couple of methods.
EE-26883770-Auto-input-column-le.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.