convert numbers to alphabet

CPiper

unread,

Nov 5, 2003, 12:15:47 PM11/5/03

to

Is it possible to convert numbers to the corresponding
alaphabetic equivelant? 1=A, 2=B,....27=AA

Peo Sjoblom

unread,

Nov 5, 2003, 12:39:17 PM11/5/03

to

One possible way using a formula and a lookup table, create the table
put this formula in a cell and copy down 256 rows

=LEFT(ADDRESS(1,ROW(1:1),4),IF(ROW(1:1)>26,2,1))

assume you put the formula in I1 going down to I256, in J1 put
1 and in J2 2, select both cells and double click on the lower right corner
of J2.

Now you can use A1 as the cell where to put the letter

=IF(A1="","",=VLOOKUP(A1,$I$1:$J$256,2,0))

you might even want something like this in case someone is using lower caps

=IF(A1="","",VLOOKUP(UPPER(A1),$I$1:$J$256,2,0))

--

Regards,

Peo Sjoblom

"CPiper" <anon...@discussions.microsoft.com> wrote in message
news:014801c3a3c0$74133510$a301...@phx.gbl...

Ken Wright

unread,

Nov 5, 2003, 12:46:22 PM11/5/03

to

=IF(A1<27,CHAR(A1+64),CHAR(INT((A27-1)/26)+63)&CHAR(A1-(INT((A1-1)/26)*26)+64))

takes you out to ZZ

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

"CPiper" <anon...@discussions.microsoft.com> wrote in message
news:014801c3a3c0$74133510$a301...@phx.gbl...

> Is it possible to convert numbers to the corresponding
> alaphabetic equivelant? 1=A, 2=B,....27=AA

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003

Harlan Grove

unread,

Nov 5, 2003, 1:30:39 PM11/5/03

to

"Peo Sjoblom" wrote...

>One possible way using a formula and a lookup table, create the table
>put this formula in a cell and copy down 256 rows
>
>=LEFT(ADDRESS(1,ROW(1:1),4),IF(ROW(1:1)>26,2,1))

..

Argh! Why? You're so close. If OP only wants to support numbers up to 256, then
no lookup table needed.

=SUBSTITUTE(ADDRESS(1,X,2),"$1","")

If OP wants to support numbers up to 26^4, use

=IF(X>26^3,CHAR(64+INT(X/26^3)),"")
&IF(X>26^2,CHAR(64+INT(MOD(X,26^3)/26^2)),"")
&IF(X>26,CHAR(64+INT(MOD(X,26^2)/26)),"")
&CHAR(64+MOD(X,26))

which is easy (if tedious) to scale to higher powers of 26.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

Harlan Grove

unread,

Nov 5, 2003, 2:17:47 PM11/5/03

to

"Harlan Grove" wrote...
..

>=IF(X>26^3,CHAR(64+INT(X/26^3)),"")
>&IF(X>26^2,CHAR(64+INT(MOD(X,26^3)/26^2)),"")
>&IF(X>26,CHAR(64+INT(MOD(X,26^2)/26)),"")
>&CHAR(64+MOD(X,26))

..

Might be nice if I got the formula right.

=IF(X>26*27^2,CHAR(65+MOD(INT((X-1)/(26*27^2)-1),26)),"")
&IF(X>26*27,CHAR(65+MOD(INT((X-1)/(26*27)-1),26)),"")
&IF(X>26,CHAR(65+MOD(INT((X-1)/26-1),26)),"")
&CHAR(65+MOD(X-1,26))