Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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
0
Steve_Brady
Asked:
Steve_Brady
  • 4
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now