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

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))

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

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,"")

--
Rick (MVP - Excel)


"Brotherharry" wrote in message
...
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))

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

Brotherharry wrote:
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)

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

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(A126,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64)

HTH,
Bernie
MS Excel MVP


"Brotherharry" wrote in message
...
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))

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

Here is yet another way...

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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,"")

--
Rick (MVP - Excel)


"Brotherharry" wrote in message
...
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))

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

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

=IF(A126,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 (MVP - Excel)

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

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

=IF(A126,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.

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

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

Rick

inverse of the column function? i.e. input a number, output the co
 

Hi,

How do you get 5 from COLUMN(D)?


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brotherharry" wrote:

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))

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

On Fri, 13 Feb 2009 12:54:29 -0500, "Rick Rothstein"
wrote:

Here is yet another way...

=IF(A126,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 A1702

Lars-Åke

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Otherwise, this same approach gets more complicated (for columns above
Z)

=IF(A126,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.

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

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

Rick

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

Good catch! Thanks for point it out.

--
Rick (MVP - Excel)


"Lars-Åke Aspelin" wrote in message
...
On Fri, 13 Feb 2009 12:54:29 -0500, "Rick Rothstein"
wrote:

Here is yet another way...

=IF(A126,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 A1702

Lars-Åke

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

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Good catch! Thanks for point it out.

--
Rick (MVP - Excel)


"Lars-Åke Aspelin" wrote in message
...
On Fri, 13 Feb 2009 12:54:29 -0500, "Rick Rothstein"
wrote:

Here is yet another way...

=IF(A126,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 A1702

Lars-Åke

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

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(A126,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).

--
Rick (MVP - Excel)


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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Otherwise, this same approach gets more complicated (for columns above
Z)

=IF(A126,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.

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

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

Rick

inverse of the column function? i.e. input a number, output the co
 

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brotherharry" wrote:

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))

inverse of the column function? i.e. input a number, output the co
 

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brotherharry" wrote:

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))

inverse of the column function? i.e. input a number, output the co
 

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+(A126)+(A1702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A126))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Brotherharry" wrote:

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))

inverse of the column function? i.e. input a number, output the co
 

2007:
=LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A126))

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

Rick

inverse of the column function? i.e. input a number, output theco
 

Rick Rothstein wrote:
2007:
=LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A126))

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

Rick


Agreed!

inverse of the column function? i.e. input a number, output th
 

Thanks Guys!!!


Cheers,
Shane Devenshire


"Glenn" wrote:

Rick Rothstein wrote:
2007:
=LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702))

2003:

=LEFT(ADDRESS(1,A1,2),1+(A126))

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

Rick


Agreed!

inverse of the column function? i.e. input a number, output theco
 

Shane Devenshire wrote...
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+(A126)+(A1702))

2003:
=LEFT(ADDRESS(1,A1,2),1+(A126))
....

You could use the 2007 formula in 2003. As long as A1 contains a valid
column number (between 1 and 256), the (A1702) 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.