View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default Custom HEX or BASE 33 Sequence

Mitch Matheny wrote...
I want to create a custom numbering sequence in excel....

0123456789ABCDEFGHJKLMNPQRSTVWXYZ
(note that the letters I, O, & U are left out).

My objective is to create a column of the custom sequence that looks like
the following...

0001
0002

....
0009
000A
000B

....

Not starting with 0000?

Define a name like Numerals referring to the string constant
"0123456789ABCDEFGHJKLMNPQRSTVWXYZ". Then enter the following formula
in cell A1.

A1:
=MID(Numerals,INT(ROWS(A$1:A1)/LEN(Numerals)^3)+1,1)
&MID(Numerals,INT(MOD(ROWS(A$1:A1),LEN(Numerals)^3 )/
LEN(Numerals)^2)+1,1)
&MID(Numerals,INT(MOD(ROWS(A$1:A1),LEN(Numerals)^2 )/LEN(Numerals))
+1,1)
&MID(Numerals,MOD(ROWS(A$1:A1),LEN(Numerals))+1 ,1)

Fill A1 down as far as needed. Note: to get to ZZZZ you need 1,185,920
cells, which means you need 19 columns. However, this isn't something
Excel does efficiently. Indeed, filling just column A will nearly
crash Excel.

This sort of thing is far better handled using real programming
languages. What are you trying to do with these base-33 numbers? There
may be alternative ways to do it that are better suited to Excel.