###
User Function

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:

**=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")**

To break it down:

**=ADDRESS(row_num,column_num,abs_num)**

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*".

**SUBSTITUTE(text,old_text,new_text)**

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

**Dick **mused:'

"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**