There can be times when identifying the current or a specific column letter reference is useful. Check out the comments for a shorter formula – thanks Batuque.
- to document the file – you can refer to the column letter directly
- for instruction purposes – “View column X” is better than “View column 24”
- to create dynamic references using the INDIRECT function
The formula that extracts the column letter for cell A1 is
=MID(CELL("address",A1),2,SEARCH("$",CELL("address",A1),2)-2)
Simply replace the two references to A1 to find any other column letter.
The MID function extracts characters from the middle of a cell. In this case it extracts from the second character as the first character is always the $ sign.
The CELL function “address” returns the fixed (absolute) address reference of a cell eg $A$1. The column letter always starts at the second character position.
The SEARCH function finds the character position of a text string. In this case it finds the second $ sign. The ,2 at the end of the SEARCH function instructs Excel to find the second $ sign within the CELL function address result.
2 is subtracted from the SEARCH function result to determine how many characters make up the column letter(s). It will be either 1,2 or 3 characters for the column reference.
Hi Neil
Nice article! I was looking at your formula and at first I thought it could be improved with something like this:
=CHAR(COLUMN()+64)
Then I realised this is only good for cells from A-Z. So I tried to alter your formula and come up with a slight modification;
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)
It is the 4 in the formula that is key. It provides for the relative reference for the third part of the address formula. Then we are just replacing 1 as in (A1 or AAA1) with nothing to be left with the column number.
Anyways worth a look perhaps.
Take care
Smallman
Cool – thanks Smallman – that works well too.
Lots of different ways to achieve things in Excel.
Regards
Neale