asked on

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

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zorvek (Kevin Jones)

In case the row the formula is in is not row 1:

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

Kevin

Alan

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.

Alan

Sorry, that last narrative should have read AC5.

Just change the reference in the formula as required.

Alan.

Alan

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.

Steve_Brady

ASKER

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.

Alan

Okay - no problem.

Alan.

Steve_Brady

ASKER

Reference file added with a couple of methods.
EE-26883770-Auto-input-column-le.xlsx

Auto input column letter headings for columns &gt; &quot;Z&quot; in Excel

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