C
CPiper
Is it possible to convert numbers to the corresponding
alaphabetic equivelant? 1=A, 2=B,....27=AA
alaphabetic equivelant? 1=A, 2=B,....27=AA
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
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))
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))
K
Ken Wright
=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
takes you out to ZZ
H
Harlan Grove
...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.
H
Harlan Grove
...
...
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))
...
...=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))