Reverse Column function

**Peterdkn** · 12-03-2009, 07:21 AM

The Column function returns a number corresponding to the letter header of the column. Is there a function to provide the opposite function, that is, given a number, it returns a letter that can then be used in a formula as a cell address?

**rwgrietveld** · 12-03-2009, 07:25 AM

I would use CODE and CHAR

=CODE("A")
=CHAR(65)

or starting from column A.

=CHAR(COLUMN(A1)+64) can be dragged to the right

**martindwilson** · 12-03-2009, 07:29 AM

or use indirect with address
=INDIRECT(ADDRESS(1,1)) which =a1
=INDIRECT(ADDRESS(2,6)) which =f2

**DonkeyOte** · 12-03-2009, 08:09 AM

CHAR has obvious limitations - a safer method is ADDRESS as outlined already, the below

=SUBSTITUTE(ADDRESS(1,A1,4),"1","")

would return the column letter where column number is specified in A1

However I would make the point that RARELY do you need the letter to create range references given you can use INDEX which will utilise the number.

Consider:

A1: 10

Both

=INDIRECT(SUBSTITUTE(ADDRESS(1,A1,4),"1","")&"5")

and

=INDEX($5:$5,A1)

will return contents of J5 only the latter is non volatile and is far simpler.