How do I autofill an alphabetic sequence? (AAA, AAB, etc.)

D

dogisnuts

I want Excel to autofill an alphabetic sequence. This is for equipment mgmt.
purposes. From AAA to ZZZ.
 
M

Max

dogisnuts said:
I want Excel to autofill an alphabetic sequence.
This is for equipment mgmt purposes. From AAA to ZZZ
Click to expand...

Try this slight adaptation of a previous post by Glenn Schwandt in
..worksheet.functions (Mar 2001)

Put in any starting cell, say, B2:

=UPPER(IF(ROW(A1)>18278,CHAR(96+INT(MOD(ROW(A1)-18279,456976)/17576)+1),"")&
IF(ROW(A1)>702,CHAR(96+INT(MOD(ROW(A1)-703,17576)/676)+1),"")&IF(ROW(A1)>26,
CHAR(96+INT(MOD(ROW(A1)-27,676)/26)+1),"")&CHAR(97+MOD(ROW(A1)-1,26)))

Copy B2 down to B18279 to fill: A, B, C ... ZZZ

And .. you can actually fill in the alpha series beyond ZZZ (whew!) to the
extent of Excel's 65536 rows, viz. filling B2 to B65536 will return the
alpha series: A, B, C ... CRXO !
 
M

Max

From AAA to ZZZ

Oops, misread your start-point (AAA, not A) ! <g>

Put in any starting cell, say, B2:

=UPPER(IF(ROW(A703)>18278,CHAR(96+INT(MOD(ROW(A703)-18279,456976)/17576)+1),
"")&IF(ROW(A703)>702,CHAR(96+INT(MOD(ROW(A703)-703,17576)/676)+1),"")&IF(ROW
(A703)>26,CHAR(96+INT(MOD(ROW(A703)-27,676)/26)+1),"")&CHAR(97+MOD(ROW(A703)
-1,26)))

Copy B2 down to B17577
(i.e. down by 17576 rows [26^3=17576])
 
Top