Custom HEX or BASE 33 Sequence

[Mitch Matheny]

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
0003
0004
0005
0006
0007
0008
0009
000A
000B

all the way to

ZZZZ

of course minus the letters I, O, & U.

If this is possible, I would appreciate any help I can get. Thanks, - Mitch

 

[Harlan Grove]

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.

 

[Rick Rothstein]

Here is a UserDefinedFunction (UDF) that will increase a Base33 number by
one...

Public Function AddOneBase33(Base33Value As Variant) As String
Dim X As Long
Const Digits = "0123456789ABCDEFGHJKLMNPQRSTVWXYZ0"
AddOneBase33 = "0" & Base33Value
For X = Len(AddOneBase33) To 1 Step -1
Mid(AddOneBase33, X) = Mid(Digits, InStr(Digits, Mid( _
AddOneBase33, X, 1)) + 1, 1)
If Mid("0" & Base33Value, X, 1) <> "Z" Then Exit For
Next
AddOneBase33 = Mid(AddOneBase33, 2)
End Function

To use it, simple enter your starting Base33 number, as a **text** value, in
a cell, say '0000 in A1; then use the UDF with that cell as a reference. So,
this...

=AddOneBase33(A1)

will result in 0001. You can copy this formula down to generate your
sequence of numbers. Of course, read Harlan's post about the limitation
problem you will have in trying to get to ZZZZ.

Oh, in case you don't know how to install an UDF... press Alt+F11 to get
into the VBA editor, click Insert/Module from the menu bar there, copy/paste
the code above into the code window that appears. That's it... the UDF can
now be used on your worksheet.

 

[Mitch Matheny]

Rick, I can't seem to get the code to work properly. If I type in 0009 I get
000A which is ok, then I type in 0010 and I get 0011 which is not correct.
Not sure what I am doing wrong. I think I got off course by saying I wanted
my list to start at 0001. That really doesn't matter.

Let me restate my objective. I want a function that is identical to DEC2HEX
but uses my custom base33 sequence.

So if I type the decimal number 0 (in cell A1 for example) I get 0000 (in
cell A2 for example). Other random examples:

Input = Output
0 = 0000
9 = 0009
10 = 000A
33 = 0010
18 = 000J
43 = 001A
???????? = ZZZZ (not sure what it would take to get ZZZZ, I will probably
never need to go this high, but would like to have the capability to
calculate the complete sequence if needed.)

My original question asked for a column of my sequence because I was going
to use a VLOOKUP to calculate decimal to base33. After seeing the last two
postings I think it may be simpler to go another route.

Additional suggestions would be appreciated. Thanks - Mitch

 

[Rick Rothstein]

The output is correct... it just appears to not be what you are now looking
for. Your original post asked for a method of generating consecutive Base33
numbers... the formula I posted will allow you to do that... it returns the
next Base33 number after the one that was passed into it... it is not a
decimal number to Base33 number converter (which your new object of "I want
a function that is identical to DEC2HEX but uses my custom base33 sequence"
indicates you now want)... it is simply an incrementer of Base33 numbers.
The next Base33 number after 0010 is 0011 (decimal 34 follows decimal 33) in
the same way that the next Hex number after 10 is 11 (because decimal 17
follows decimal 16).

Okay, so you want a Decimal to Base33 converter. Try this UDF...

Public Function Dec2Base33(Value As Long, Optional _
NumLen As Long = 0) As String
Dim Modulo As Long
Dim Result As Long
Const Digits = "0123456789ABCDEFGHJKLMNPQRSTVWXYZ"
Do Until Value = 0
Modulo = Value Mod 33
Dec2Base33 = Mid(Digits, Modulo + 1, 1) & Dec2Base33
Value = Value \ 33
Loop
If NumLen > 0 Then Dec2Base33 = Right(String(NumLen, "0") & _
Dec2Base33, NumLen)
If Dec2Base33 = "" Then Dec2Base33 = "0"
End Function

 

[Mitch Matheny]

Perfect Rick. Thanks for the help. I should have stated my question more
clearly from the beginning. - Mitch

 

[Rick Rothstein]

Not a problem... I'm just glad we got it all straightened out for you.

 

[Harlan Grove]

Okay, so you want a Decimal to Base33 converter. Try this UDF...

Public Function Dec2Base33(Value As Long, Optional _
                     NumLen As Long = 0) As String

No so picky: if you're going to use line continuation, then keep the
Optional token with the argument to which it applies.

  Dim Modulo As Long
  Dim Result As Long
  Const Digits = "0123456789ABCDEFGHJKLMNPQRSTVWXYZ"
  Do Until Value = 0
    Modulo = Value Mod 33
    Dec2Base33 = Mid(Digits, Modulo + 1, 1) & Dec2Base33
    Value = Value \ 33
  Loop
  If NumLen > 0 Then Dec2Base33 = Right(String(NumLen, "0") & _
                                        Dec2Base33, NumLen)
  If Dec2Base33 = "" Then Dec2Base33 = "0"
End Function

....

Note that when Value > 33 ^ NumLen, your udf returns only the LEAST
significant NumLen digits. Maybe that's what the OP wants, but maybe
the OP wants return values similar to TEXT(x,"0000") which returns
999999 rather than 9999 for x = 10^6-1.

Then there's the standard tweak to this sort of routine: prefill the
return value with zeros, then modify only the nonzero digits.

Finally, pass the numeric value as a Double to allow for Excel's full
range of whole numbers.


Function foo(ByVal n As Double, Optional d As Long = 0) As String
Const BASE As Long = 33
Const NUMERALS As String = "123456789ABCDEFGHJKLMNPQRSTVWXYZ"

Dim k As Long, j As Long

If n < 0 Or d < 0 Then
foo = "invalid arguments"
Exit Function
End If

n = Int(n)
If n > 0 Then k = Int(Log(n) / Log(BASE)) + 1 Else k = 1
If k < d Then k = d

foo = String(k, "0")

Do While n > 0
j = n Mod BASE
n = n \ BASE
If j > 0 Then Mid$(foo, k, 1) = Mid$(NUMERALS, j, 1)
k = k - 1
Loop

End Function