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

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

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)

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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!

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!

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.