Extracting the Column Letter in Excel

There can be times when identifying the current or a specific column letter reference is useful.

  • 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)

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

col_letter_02

 

Please note: I reserve the right to delete comments that are offensive or off-topic.