How do I autofill an alphabetic sequence? (AAA, AAB, etc.)
I want Excel to autofill an alphabetic sequence. This is for equipment mgmt.
purposes. From AAA to ZZZ. |
You can add custom fill lists through ToolsOptionsCustom Lists.
hth Rowan "dogisnuts" wrote: I want Excel to autofill an alphabetic sequence. This is for equipment mgmt. purposes. From AAA to ZZZ. |
"dogisnuts" wrote
I want Excel to autofill an alphabetic sequence. This is for equipment mgmt purposes. From AAA to ZZZ 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 ! -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- .. |
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]) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
All times are GMT +1. The time now is 03:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com