How can I change column numbers back to column letters?

[Space Elf]

I'm using Office 2000. The columns are normally letters. When using the
command COLUMN(CB7), naturally I get the answer "80". How can I convert it
back to "CB"? Someting like COMMAND(80) will equal "CB". I can't use
CHAR(64+80) because that would not result in multiple letter column. Any help?

[Richard Buttrey]

On Thu, 2 Mar 2006 08:40:06 -0800, Space Elf
<SpaceElf@discussions.microsoft.com> wrote:

>I'm using Office 2000. The columns are normally letters. When using the
>command COLUMN(CB7), naturally I get the answer "80". How can I convert it
>back to "CB"? Someting like COMMAND(80) will equal "CB". I can't use
>CHAR(64+80) because that would not result in multiple letter column. Any help?

The following entered in any cell will return the column reference of
that cells column.

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)

A slight modification to include the specific column reference, e.g
CB7 will produce "CB" wherever the formula is placed.

=LEFT(ADDRESS(1,COLUMN(CB7),4),LEN(ADDRESS(1,COLUMN(CB7),4))-1)

Hope this helps

Richard Buttrey
__

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Space Elf" <SpaceElf@discussions.microsoft.com> wrote in message
news:3D8689F1-7F60-45C6-BF89-01DA363E042F@microsoft.com...
> I'm using Office 2000. The columns are normally letters. When using the
> command COLUMN(CB7), naturally I get the answer "80". How can I convert it
> back to "CB"? Someting like COMMAND(80) will equal "CB". I can't use
> CHAR(64+80) because that would not result in multiple letter column. Any
help?

[ΓΙΑΝΝΗΣ Χ.Β.]

Another simple solution:

=LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2))

=LEFT(ADDRESS(1,COLUMN(CB7),4),IF(COLUMN(CB7)<27,1,2))

Ioannis, Athens

"Space Elf" wrote:

> I'm using Office 2000. The columns are normally letters. When using the
> command COLUMN(CB7), naturally I get the answer "80". How can I convert it
> back to "CB"? Someting like COMMAND(80) will equal "CB". I can't use
> CHAR(64+80) because that would not result in multiple letter column. Any help?