aftermath

we dream, we create, we change, we love

Classic Columns

leave a comment »

Excel can easily count which column a cell is in via the simple built-in formula –

=COLUMN()


But that gives me a number. For example, if I use that in column C it gives me 3; while for column AC it gives me 29. That can be useful, yes. But frequently, if I’m pulling something from column AC, I want to know those letters – “AC” – not the number 29. I know, it seems obvious: “Just look at the column header at the top of the spreadsheet, dummy,” I can almost hear. Except as I’ll illustrate in future posts in this aftermath category, there are too many times when I need to have the letters feeding directly into a formula that doesn’t have the luxury of my eyes looking at column headers for the information.

And if Excel has an easy formula for that, then I’m not the only one to miss it, since I’m not the only one who’s developed a formula for doing so. Like other formulas I’ve seen, my own relies on the result of the COLUMN() number, using the fact that there are 26 letters in the alphabet and essentially reciting one’s way to the alpha result –

=IF(COLUMN()>26,CHAR(INT(COLUMN()/26)+64),””)
     &CHAR(MOD(COLUMN()-1,26)+65)

Quite frequently, since I do find use for this so commonly, I will actually reserve take the very first row of any of my spreadsheets for this formula. It looks a little silly (unless I hide that first row, which I also quite commonly do), since that first row merely repeats Excel’s own column header. And then since I really have no further need of the column-letter formula itself, I copy the top row onto itself, pasting just the alpha values; so the formula has actually saved me from simply typing out the column letters manual (which I’ll sometimes do, instead of using this formula, for a smaller spreadsheet). And from then on any time I need the column letter, I know to simply pull the top cell of that column into my formulas. In fact, that is so common a practice of mine that any x$1 cell reference in any of my formulas within this aftermath category should be presumed to return the letter of the column being used.

bumper sticker [www.internetbumperstickers.com] - excel hero

Written by macheide

14 March 2008 at 7:35 am

Posted in excel hero

Comment?

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: