Number to equivalent Letter

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

I need a spreadsheet with the following data: (in either format)

Format 1:

col 1 col 2 col3
a a a
a a b
a a c
a a d ...
z z z

format 2:

col1
aaa
aab
aac
aad ...
zzz


This spreadsheet will go on to list ALL the 17,000+ permutations.
Is there a formula or something that can either:
1. create the list automatically using the letters?
2. if i used numbers in format 1, is there a formula to convert the number
to it's equivalent letter ? 1=a, 2=b, c=3 etc.

Thanks for looking.

Ann
 
In A703, enter:

=IF(ROW()>18277,CHAR(64+MOD(INT((ROW()-18279)/17576),26)+1),"")&
IF(ROW()>702,CHAR(64+MOD(INT((ROW()-703)/676),26)+1),"")&
IF(ROW()>26,CHAR(64+MOD(INT((ROW()-27)/26),26)+1),"")&
CHAR(64+MOD(ROW()-1,26)+1)

and copy down
 
Here's another way, entered in Row 1:

=CHAR((INT((ROW()-1)/676)+1)+96)&CHAR((MOD(INT((ROW()-1)/26),26)+1)+96)&CHAR((MOD(ROW()-1,26)+1)+96)

Copy down...

HTH
Elkar
 
Hi,

Enter the following formula anywhere on row 1 and copy it down.

=CHAR(ROW()/676+64.999)&CHAR(ROW()/26+64.97)&CHAR(MOD(ROW()-1,26)+65)
 
Back
Top