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

  • Thread starter Thread starter Brotherharry
  • Start date Start date
B

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))
 
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,"")
 
Brotherharry said:
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))
Click to expand...


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)
 
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
 
Here is yet another way...

=IF(A1>26,CHAR(64+INT(A1/26)),"")&CHAR(65+MOD(A1-1,26))
 
Otherwise, this same approach gets more complicated (for columns above Z)
=IF(A1>26,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64)
Click to expand...

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.
 
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.
Click to expand...

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

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

Rick
 
Here is yet another way...

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

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
 
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))
 
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).
 
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)
 
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)
 
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))
 
2007:
=LEFT(ADDRESS(1,A1,2),1+(A1>26)+(A1>702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A1>26))
Click to expand...

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

Rick
 
Shane Devenshire said:
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))
Click to expand...
....

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.
 
Back
Top