inverse of the column function? i.e. input a number, output thecorresponding column text label

[Brotherharry]

in A1

=column(D2)

would return the number 5. I want the reverse of that.

e.g.a function that would when pointed at A1, returns the text "D"

This beast does the job, but is there are more elegant way?

=LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2))))-1))

 

[Rick Rothstein]

I doubt if anyone would call this more elegant... hell, it probably isn't
even as efficient... just different.

=REPLACE(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2)),9,"")

 

[Glenn]

in A1

=column(D2)

would return the number 5. I want the reverse of that.

e.g.a function that would when pointed at A1, returns the text "D"

This beast does the job, but is there are more elegant way?

=LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2))))-1))

More elegant? In this context, elegant is defined as "gracefully concise and
simple; admirably succinct." So, subjective.

How about this?

=MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1),2)-2)

 

[Bernie Deitrick]

If your numbers are always less than 27, then a simple solution is

=CHAR(A1+64)

Otherwise, this same approach gets more complicated (for columns above Z)

=IF(A1>26,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64)

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein]

Here is yet another way...

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

 

[Rick Rothstein]

Otherwise, this same approach gets more complicated (for columns above Z)

=IF(A1>26,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64)

This one fails for a value of 26 (it returns "@" instead of "Z"). I ran into
the same problem while developing my 2nd posted solution.

 

[Rick Rothstein]

Otherwise, this same approach gets more complicated (for columns above Z)

This one fails for a value of 26 (it returns "@" instead of "Z"). I ran
into the same problem while developing my 2nd posted solution.

I should have mentioned here, the fix is simple... change the part after the
ampersand to this...

CHAR(MOD(A1-1,26)+65)

Rick

 

[Shane Devenshire]

Hi,

How do you get 5 from COLUMN(D)?

 

[Lars-Åke Aspelin]

Here is yet another way...

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

For A1=52 this gives BZ instead of AZ
For A1=78 this gives CZ instead of BZ
and so on

And if you have Excel 2007, the formula does not work for A1>702

Lars-Åke

 

[Rick Rothstein]

Actually, as Lars-Åke points out in my sub thread, this fix does not really
work.

 

[Rick Rothstein]

Good catch! Thanks for point it out.

 

[Rick Rothstein]

Okay, this should fix the problem for XL2003 and earlier...

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

 

[Rick Rothstein]

Well, the fix I gave you is correct for the part of your formula it applies
to. What Lars-Åke pointed out was a problem with the IF part of our code.
Here is the corrected formula I posted in my sub thread...

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

I'm pretty sure this is correct (for XL2003 and earlier as Lars-Åke also
pointed out).

 

[Shane Devenshire]

Hi,

One way

=MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1,2))-1)

or a modification, but slightly longer version, of a previous suggestion

=LEFT(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2))-1)

 

[Shane Devenshire]

Hi,

and here is an even shorter method:

=LEFT(ADDRESS(1,A1,2),LEN(ADDRESS(1,A1))-3)

down from

=MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1,2))-1)

 

[Shane Devenshire]

Hi,

And to take the last simplification and go further:

from

=LEFT(ADDRESS(1,A1,2),LEN(ADDRESS(1,A1))-3)

to

2007:
=LEFT(ADDRESS(1,A1,2),1+(A1>26)+(A1>702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A1>26))

 

[Rick Rothstein]

2007:

=LEFT(ADDRESS(1,A1,2),1+(A1>26)+(A1>702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A1>26))

Nice going Shane... I would definitely call these formulas the "elegant"
ones the OP was looking for.

Rick

 

[Glenn]

Nice going Shane... I would definitely call these formulas the "elegant"
ones the OP was looking for.

Rick

Agreed!

 

[Shane Devenshire]

Thanks Guys!!!


Cheers,
Shane Devenshire

Agreed!

 

[Harlan Grove]

And to take the last simplification and go further:

from

=LEFT(ADDRESS(1,A1,2),LEN(ADDRESS(1,A1))-3)

to

2007:
=LEFT(ADDRESS(1,A1,2),1+(A1>26)+(A1>702))

2003:
=LEFT(ADDRESS(1,A1,2),1+(A1>26))

....

You could use the 2007 formula in 2003. As long as A1 contains a valid
column number (between 1 and 256), the (A1>702) test will be FALSE. No
good reason to use different formulas in different Excel versions when
it isn't necessary.

Then again, if some future Excel versions goes beyond columns ZZZ, as
long as the ADDRESS function remains unchanged, it'd be more robust to
use

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

This ain't rocket science.