# Thread: Serialized alphanumeric formula (Excell 2003)

1. ## 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 -

2. ## 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?

3. ## Re: Serialized alphanumeric formula (Excell 2003)

Can you provide some examples of what you want to accomplish?

4. ## 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 0-9 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 !

5. ## Re: Serialized alphanumeric formula (Excell 2003)

You can use these user-defined 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)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•