Get column name (or headers or address as A,B,C, etc) as input inside cell

Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters

4-6-2015 9-25-15 AM

However, the COLUMN() is kind of tricky

Because of the fact that cell addresses (or references) are based on the old style of R1C1

=Column()

4-6-2015 9-27-34 AM

What usually human wants is the letter of the column (column name or column header, whatever you call it), so it should brings E instead of 5 in example above. But COLUMN() does not do that for you.

Yes, we can always convert the column into ASCII char code, like this maybe

=CHAR(Column()+64)

But this will stop after column Z

4-6-2015 9-32-57 AM

So I find in my experience, the best way to get that column header, is….

What???

Combination of more than 3 functions, Duh!!

=LEFT(ADDRESS(ROW(),COLUMN(),2),SEARCH("$",ADDRESS(ROW(),COLUMN(),2))-1)

4-6-2015 9-35-42 AM

 


 

If you already seen my iframe in Excel, you may already recognized this set of formulas there, I used it to mimic creating headers in that sheet

2012-02-19-031500

Thought this set, deserves to get a post on its own

Comments

comments