Extracting the Column Letter in Excel

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)

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.

Leave a Reply to Neale Blackwood Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Extracting the Column Letter in Excel

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