Results 1 to 5 of 5

20070104, 20:26 #1
 Join Date
 Sep 2006
 Location
 Illinois, USA
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Serialized alphanumeric formula (Excell 2003)
Hello,
Does anyone know of a formula that can be used to calculate beginning and ending numbers in a Base 33 alphanumeric sequence ? The letters left out of the sequence are I, O, and S. I have seen calculators that will do Base 33 but with the X,Y,Z missing. We leave out the former so they don't get confused with 1,0,5.
Thanks for any help you all can provide !
 BOB 

20070104, 21:16 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Serialized alphanumeric formula (Excell 2003)
Hi Bob
Just to clarify, are you wanting to insert I, S or 0 into the sequence and if so how is excel going to know what the original number was?
I see this:
<pre>Base 33 Base 10
i 18
i0 594
i00 19602
</pre>
etc
Is there another way it can recognise the number from the string?Jerry

20070104, 21:40 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Serialized alphanumeric formula (Excell 2003)
Can you provide some examples of what you want to accomplish?

20070104, 22:04 #4
 Join Date
 Sep 2006
 Location
 Illinois, USA
 Posts
 22
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Serialized alphanumeric formula (Excell 2003)
Hi Jerry,
Thanks for taking the time to look at my issue. I need to clarify as I may have misused the term Base33. We print serialized barcode labels and usually a sequence used 09 so 0001, 0002, 0003..etc. Sometimes we use an alphanumberic sequence 0001, 0002...0009, 000A, 000B, etc...
Then there are customers that need us to leave out the I,O,& S. So when the sequence gets to 000G, 000H, I is skipped and next one is 000J etc.. So the Base 33 I referred to just means using the 10 digits and 23 alpha characters.
So using this numbering scheme, I just can't have a formula that takes a starting number such as 00001, adds 10000, and calculates an ending number of 10001. Or maybe there is a formula...that's what I'm looking for....I hope that explains better....
Thanks again !

20070104, 22:35 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Serialized alphanumeric formula (Excell 2003)
You can use these userdefined functions:
Function To33(lngNum As Long) As String
Dim lngTmp As Long
Const strDigits = "0123456789ABCDEFGHJKLMNPQRTUVWXYZ"
If lngNum = 0 Then
To33 = "0"
Else
lngTmp = lngNum
Do While Not lngTmp = 0
To33 = Mid(strDigits, lngTmp Mod 33 + 1, 1) & To33
lngTmp = lngTmp 33
Loop
End If
End Function
Function From33(strNum As String) As Long
Dim i As Integer
Const strDigits = "0123456789ABCDEFGHJKLMNPQRTUVWXYZ"
For i = 1 To Len(strNum)
From33 = (InStr(strDigits, Mid(strNum, i, 1))  1) + 33 * From33
Next i
End Function
With a serial number in A1, the next one can be calculated by the formula
=To33(From33(A1)+1)
If you want leading zeros to pad the number to length 4, use a formula such as
=RIGHT("0000"&To33(From33(A1)+1),4)