# 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)`

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.

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

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;

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