Number to equivalent Letter

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
 
G

Gary''s Student

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
 
E

Elkar

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
 
S

Shane Devenshire

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top