Here's an odd little use of functions.
If you want to display the Row number on a spreadsheet, the formula
=Row()works just fine.
You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update.
Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B".
The following formula extracts the Column letter:
To break it down:
This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address.
The formula will produce a result such as "AA1".
This function looks at the address, i.e. "AA1".
It replaces the Row number character ("1") with a null or empty value ("").
The formula will produce a result such as "AA".
Also see Daily Dose of Excel by Dick Kusleika.
"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can't I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."
Of course Office 2007+ has taken it up to 16,284 columns.
See all Topics