Solved

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

Posted on 2011-03-13
8
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35123540
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)
ID: 35123558
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
ID: 35123559

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!

 
LVL 12

Expert Comment

by:Alan3285
ID: 35123561
Sorry, that last narrative should have read AC5.

Just change the reference in the formula as required.


Alan.
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 35123568
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
ID: 35123814
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
ID: 35123897
Okay - no problem.

Alan.
0
 

Author Comment

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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

726 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