aa ab ac....ax ay az....ba bb bc....zx zy zz ?

[JethroUK©]

i need to generate these letters

aa ab ac....ax ay az....ba bb bc....zx zy zz ? down the A column

What's best/easiest way?

 

[CLR]

In cells B1:B26 put A, in B27:B52 put B, etc etc through Z
In cells C1:C26 put the alphabet, a,b,c,d,etc etc..........then copy and
paste it down to C27:C52, etc etc till it fills by the Z's in column B
Then in A1 put =B1&C1 and copy down.........do Copy > PasteSpecial > Values
on column A and delete columns B and C.........

Vaya con Dios,
Chuck, CABGx3

 

[Ron Coderre]

Try this:

A1: =CHAR(FLOOR((ROW()-1)/26,1)+97)&CHAR(MOD(ROW()-1,26)+97)
Copy down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Dave Peterson]

On more, put this in A1 and drag down:
=CHAR(INT((ROW(A1)-1)/26)+97)&CHAR(MOD(ROW(A1)-1,26)+97)

If you really mean upper case, change the 97's to 65's:
=CHAR(INT((ROW(A1)-1)/26)+65)&CHAR(MOD(ROW(A1)-1,26)+65)

 

[Ron Rosenfeld]

i need to generate these letters

aa ab ac....ax ay az....ba bb bc....zx zy zz ? down the A column

What's best/easiest way?

A1: =CHAR(97+(ROW()-1)/26)&CHAR(97+MOD(ROW()-1,26))

Fill down to A676


--ron

 

[Ragdyer]

If you're going to do this on many different occasions, it might pay to
create a "Custom List", so that each time you need to make this list, you
could simply enter "AA" and *autofill* down a column or across a row.

Start with a new sheet, and enter this formula in A1:

=IF(ROW(A1) > 26, CHAR(INT((ROW(A1) - 1)/ 26) + 64) & CHAR(MOD(ROW(A1) -
1,26) + 65), CHAR(ROW(A1) + 64))

You can copy it down to A650 to end up with "XZ".

Now, while the column is *still* selected from the copy, let's remove the
formulas.
Right click in the selection and choose "Copy".
Right click again and choose "Paste Special",
And click on "Values", then <OK>.

And NOW, while the column is *still* selected from the formula removal,
Click on:
<Tools> <Options> <CustomLists>
The "ImportList" box should already have the column range entered, since it
was selected.
Click on "Import", then <OK>.

Strangely enough, whatever case you start your list with (AA, BB - aa, bb),
XL will continue to autofill with *that* case.

Another advantage of custom lists is, you can start anywhere in the middle,
and XL will take it up from wherever you started.

 

[JethroUK©]

Peachy - thanks very much - i forgot about CHAR() func - i used to be able
to figure stuff out myself once upon a time )