Results 1 to 5 of 5
  1. #1
    New Lounger
    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 -

  2. #2
    Platinum Lounger
    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Serialized alphanumeric formula (Excell 2003)

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

  4. #4
    New Lounger
    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 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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •