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
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.