Solved

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

Posted on 2011-03-13
8
456 Views
Last Modified: 2012-05-11
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
Comment
Question by:Steve_Brady
  • 4
  • 2
  • 2
8 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
The formula below returns the column letter or letters given a column number.

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

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
In case the row the formula is in is not row 1:

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

Kevin
0
 
LVL 12

Expert Comment

by:Alan3285
Comment Utility

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
 
LVL 12

Expert Comment

by:Alan3285
Comment Utility
Sorry, that last narrative should have read AC5.

Just change the reference in the formula as required.


Alan.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:Alan3285
Comment Utility
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
 

Author Comment

by:Steve_Brady
Comment Utility
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
 
LVL 12

Expert Comment

by:Alan3285
Comment Utility
Okay - no problem.

Alan.
0
 

Author Comment

by:Steve_Brady
Comment Utility
Reference file added with a couple of methods.
EE-26883770-Auto-input-column-le.xlsx
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now