Custom HEX or BASE 33 Sequence

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.

--
Rick (MVP - Excel)


"Mitch Matheny" <MitchMatheny.RemoveThis@discussions.microsoft.com> wrote in message
news:068AEE31-C13A-4862-8F5E-6C1371432413@microsoft.com...
>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

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" wrote:

> 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.
>
> --
> Rick (MVP - Excel)
>
>
> "Mitch Matheny" <MitchMatheny.DeleteThis@discussions.microsoft.com> wrote in message
> news:068AEE31-C13A-4862-8F5E-6C1371432413@microsoft.com...
> >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
>
>

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

--
Rick (MVP - Excel)


"Mitch Matheny" <MitchMatheny DeleteThis @discussions.microsoft.com> wrote in message
news:DDEFC20F-4987-4EBB-8FF1-BC1A6131D82A@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Mitch Matheny" <MitchMatheny DeleteThis @discussions.microsoft.com> wrote in message
>> news:068AEE31-C13A-4862-8F5E-6C1371432413@microsoft.com...
>> >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
>>
>>

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

"Rick Rothstein" wrote:

> 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
>
> --
> Rick (MVP - Excel)
>
>
> "Mitch Matheny" <MitchMatheny.RemoveThis@discussions.microsoft.com> wrote in message
> news:DDEFC20F-4987-4EBB-8FF1-BC1A6131D82A@microsoft.com...
> > 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" wrote:
> >
> >> 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.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Mitch Matheny" <MitchMatheny.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:068AEE31-C13A-4862-8F5E-6C1371432413@microsoft.com...
> >> >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
> >>
> >>
>
>

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

--
Rick (MVP - Excel)


"Mitch Matheny" <MitchMatheny DeleteThis @discussions.microsoft.com> wrote in message
news:34E5147B-114D-4E19-B7AB-C138454BE8B9@microsoft.com...
> Perfect Rick. Thanks for the help. I should have stated my question more
> clearly from the beginning. - Mitch
>
> "Rick Rothstein" wrote:
>
>> 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
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Mitch Matheny" <MitchMatheny DeleteThis @discussions.microsoft.com> wrote in message
>> news:DDEFC20F-4987-4EBB-8FF1-BC1A6131D82A@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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.
>> >>
>> >> --
>> >> Rick (MVP - Excel)
>> >>
>> >>
>> >> "Mitch Matheny" <MitchMatheny DeleteThis @discussions.microsoft.com> wrote in
>> >> message
>> >> news:068AEE31-C13A-4862-8F5E-6C1371432413@microsoft.com...
>> >> >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
>> >>
>> >>
>>
>>

"Rick Rothstein" <rick.newsNO.S....TakeThisOut@NO.SPAMverizon.net> wrote...
....
>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