Create Incrementing AlphaNumeric Values 1

[Guest_imported]

How can I generate an incrementing alphanumeric value using this formula :

for example AAA01 is incrementing and reaches AAA99 the next value when AAA99 is incremented should be AAB01 and not AAA100. So the values that will be generated will be like this

AAA01 to AAA99
AAB01 to AAB99
and so on until it reaches AAZ99

 

[mymou]

Cant think of an easy way. I think you might be entering a very strange world of making up your own number system.

AAB11

(1*26^3)+(1*26^2)+(2*26^1)

I guess you could * by 100 (and add 11 in this case) to separate the numbers from letters. (100(17576+676+26*2)+11)=18315 and this also works in reverse.

If there is a simple function (which i suspect there must be (see excel columns!) - it makes everything very dull!!

Have fun

Stew

 

[MichaelRed]

Already a stressful day, so this MAY not be the end of this one. I liked stewarts' sort-of-baseN math thinggy, but I thought this MIGHT be easier for some to understand.

Public Function basIncrAlphN(strAlphN As String) As String

    'To Create an Incrementing A-N field with rollover
    'AAA01 to AAA99
    'AAB01 to AAB99

    Dim Idx As Integer
    Dim MyChr As String * 1
    Dim MyValStr As String
    Dim MyStrStr As String
    Dim MyValVal As Integer

    'Easy on me, make user supply the last value.  "I" don't know where it is

    For Idx = 1 To Len(strAlphN)
        MyChr = Mid(strAlphN, Idx, 1)
        If (IsNumeric(MyChr)) Then
            MyValStr = MyValStr & MyChr
         Else
            MyStrStr = MyStrStr & MyChr
        End If
    Next Idx

    MyValVal = CInt(MyValStr) + 1
    If (MyValVal = 100) Then
        'Do The Rollover
        MyValVal = 1

        'Incr the String Part
        'Just put each char into Seperate Char
        MyStrStr = basIncrStr(MyStrStr)

    End If
    
    basIncrAlphN = MyStrStr & right("00" & Trim(str(MyValVal)), 2)

End Function
Public Function basIncrStr(strIn As String) As String
        
    Dim MyChrs() As String
    Dim Idx As Integer

    ReDim MyChrs(Len(strIn) - 1)

    For Idx = 1 To Len(strIn)
        MyChrs(Idx - 1) = Mid(strIn, Idx, 1)
    Next Idx


    For Idx = UBound(MyChrs) To 0 Step -1
        If (MyChrs(Idx) < &quot;Z&quot;) Then
            'Found a char not at limit.  Process
            MyChrs(Idx) = Chr((Asc(MyChrs(Idx)) + 1))
            Exit For
         Else
            MyChrs(Idx) = &quot;A&quot;
    End If
    Next Idx

    For Idx = 0 To UBound(MyChrs)
        strTemp = strTemp & MyChrs(Idx)
    Next Idx

    basIncrStr = strTemp
End Function

As you may note in the ALL TO BRIEF comments sectionat the top ot the first func, it REQIUIRES the argument of what "String" ot increment. Partially because it is EASIER for me the programmer, partially because I have NO concept of how the user will implement it and partially because it is much more flexible/generic ths way.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Pro Bum et al.

BE aware that if you send a "Mixed string", doufus will promptly sort it into the numerics and non-numerics and do some weirdness in the increment process, as in the following:

? basIncrAlphN("99 This is Mikes 12th birthday Since he was 49&quot
This is Mikes th birthday Since he was 50


Makes perfect (PROGRAMMING) sense? Even if it is useless gibberish!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jjames]

The function below is based on the format AAAnn but can easily be changed...

Public Function Increment(sValue As String) As String
Const MIN_NUMBER = 1
Const MAX_NUMBER = 99
Const MIN_ASCII = 65 'an A
Const MAX_ASCII = 90 'a Z

Dim sAlpha As String
Dim iNumeric As Integer
Dim iAscii As Integer
Dim iPosition As Integer

sAlpha = Left$(sValue, 3)
iNumeric = Val(Right$(sValue, 2))

iNumeric = iNumeric + 1

If iNumeric > MAX_NUMBER Then
'we have to increment the Alpha part

iNumeric = MIN_NUMBER 'reset the numeric part

For iPosition = 3 To 1 Step -1 'loop through each of the alpha characters left to right

iAscii = Asc(Mid$(sAlpha, iPosition, 1)) + 1 'increment the value of character

If iAscii > MAX_ASCII Then
Mid$(sAlpha, iPosition, 1) = Chr$(MIN_ASCII) 'Set this character to min value and continue with next character
Else
Mid$(sAlpha, iPosition, 1) = Chr$(iAscii) 'no more to do, exit the loop
Exit For
End If

Next

End If

Increment = sAlpha & Format$(iNumeric, "00&quot 'Return the incremented value

End Function

 

[mymou]

Hi Guys

Here my suggestion - could be cleaner - but looks like better math.

The way to increment would be to generate a number add one and then generate a 'textnumber'.

Anyone care to tidy it up?


Function ConvertAlfaToNumber(Alfa)

Millions = Left(Alfa, 1)
Thousands = Mid(Alfa, 2, 1)
Hundreds = Mid(Alfa, 3, 1)
Tens = Mid(Alfa, 4, 1)
Units = Right(Alfa, 1)

NumberOfMillions = Asc(Millions) - 64
NumberOfThousands = Asc(Thousands) - 64
NumberOfHundreds = Asc(Hundreds) - 64

MillionsInNumbers = (26 ^ 3) * NumberOfMillions
ThousandsInNumbers = (26 ^ 2) * NumberOfThousands
HundredsInNumbers = (26 ^ 1) * NumberOfHundreds

ConvertAlfaToNumber = MillionsInNumbers + ThousandsInNumbers + HundredsInNumbers + (Tens * 10) + Units

End Function


Function ConvertNumberToAlfa(Number)

NumberOfMillions = Number / (26 ^ 3)
NumberOfMillions = Round(NumberOfMillions, 0)'Hope this always rounds down!!
MillionsInNumbers = NumberOfMillions * (26 ^ 3)

Millions = Chr(NumberOfMillions + 64)

NumberOfThousands = Number - MillionsInNumbers
NumberOfThousands = NumberOfThousands / (26 ^ 2)
NumberOfThousands = Round(NumberOfThousands, 0)
ThousandsInNumbers = NumberOfThousands * (26 ^ 2)

Thousands = Chr(NumberOfThousands + 64)

NumberOfHundreds = Number - MillionsInNumbers - ThousandsInNumbers
NumberOfHundreds = NumberOfHundreds / (26 ^ 1)
NumberOfHundreds = Round(NumberOfHundreds, 0)
HundredsInNumbers = NumberOfHundreds * (26 ^ 1)

Hundreds = Chr(NumberOfHundreds + 64)

TensAndUnits = Number - MillionsInNumbers - ThousandsInNumbers - HundredsInNumbers
If TensAndUnits < 10 Then TensAndUnits = &quot;0&quot; & TensAndUnits
TensAndUnits = CStr(TensAndUnits)
ConvertNumberToAlfa = Millions + Thousands + Hundreds + TensAndUnits


End Function

 

[MichaelRed]

Stewart,

At best, I'm confused.

? ConvertAlfaToNumber(&quot;AAA99&quot
18377

?????????????

What am I supposed to get?

I thought it should be AAB01

What am I missing?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TimLarkin]

a little more simple I thinkthis will be easier to read if you paste it into VB)
This checks for a valid &quot;Old&quot; value string, then increments the &quot;Numeric&quot; value by one, increments previous &quot;Letters&quot; if nescessary, and adds a new letter to the front if needed (ZZZ99 will return AAAA00)

Private Function funcGetNextIncStr(ByVal strYourString As String) As String
    On Error GoTo errorline
    
    Dim intCtr As Integer
    Dim booAddFlag As Boolean
    Dim strMyString As String
    Dim strNewString As String
    Dim intTempNum As Integer
    
    strYourString = Trim(strYourString)
    'validate input string, & change lowercase to upper, if needed.
    If Len(strYourString) < 2 Then
        Err.Raise 999999 'ending # must be 2 chars '05' for exmpl
    Else
        For intCtr = Len(strYourString) To 1 Step -1
            Select Case intCtr '=
                Case Len(strYourString), Len(strYourString) - 1 '>= 2 'should be 0 to 9
                    If Asc(UCase(Mid(strYourString, intCtr, 1))) < 48 Or _
                        Asc(UCase(Mid(strYourString, intCtr, 1))) > 57 Then
                        'not a #
                        Err.Raise 999999
                    Else
                        strMyString = UCase(Mid(strYourString, intCtr, 1)) & strMyString
                    End If
                Case Else
                    If Asc(UCase(Mid(strYourString, intCtr, 1))) < 65 Or _
                        Asc(UCase(Mid(strYourString, intCtr, 1))) > 90 Then
                        'not a letter
                        Err.Raise 999999
                    Else
                        strMyString = UCase(Mid(strYourString, intCtr, 1)) & strMyString
                    End If
            End Select
        Next
        
        'increment by one
        intTempNum = CInt(Right(strMyString, 2)) + 1
        If intTempNum > 99 Then
            intTempNum = 0
            booAddFlag = True
        End If
        strNewString = Format(intTempNum, &quot;00&quot;)
        
        'see if letters already exist and if so copy/change
        If Len(strMyString) > 2 Then
            'some letters already exsist
            For intCtr = Len(strMyString) - 2 To 1 Step -1
                intTempNum = Asc(Mid(strMyString, intCtr, 1))
                If booAddFlag Then
                    'need next letter, ie: change &quot;C&quot; to &quot;D&quot;
                    intTempNum = intTempNum + 1
                    'check to see if still A to Z
                    If intTempNum > 90 Then
                        intTempNum = 65
                    Else
                        booAddFlag = False
                    End If
                End If
                'add letter to new string
                strNewString = Chr(intTempNum) & strNewString
            Next
        End If
        'see if new letter needs  to be added
        If booAddFlag Then
            strNewString = &quot;A&quot; & strNewString
        End If
    End If
    
    funcGetNextIncStr = strNewString
    Exit Function

errorline:
    funcGetNextIncStr = &quot;**Incorrectly formatted input string.** &quot; & strYourString
End Function

Tim

Remember the KISS principle:
Keep It Simple, Stupid!

 

[mymou]

MichaelRed


The idea is simple. If you have a code A ,B, C etc then if you assign a number to each - it is easier to manage. A=1, B=1, C=3 etc. In this simple case if you recieve a B you understand that it is equal to 2 add one to get three and convert back to text to get C. In essence this is all that im trying to do. See my first post.

So have a function to convert Text to numbers and another one to convert numbers back to text. (if you want to increment - change to number - add one - change back to text)

jjames, TimLarkin - simple, works - why bother looking at anything else? Dont know, usually wouldnt.


PROFESSIONAL BUM - you code is seriously limited in numbers. Only a little over 1.5M unique codes.

I also think my 'guess' (and so is my code!!) was wrong

AAB11 - should be

(1*26^4)+(1*26^3)+(2*26^2)+(1*26^1) + (1*26^0)

Bit like
12345 in decimals
(1*10^4)+(2*10^3)+(3*10^2)+(4*10^1) + (5*10^0)

so the numerics need special treatment. Back to the drawing board. Anyone still interested???


Stew

 

[strongm]

The following function should do the trick

It bears a resemblence to jjames solution, but with the added advantages that

1) you can add any number to the A-N string
2) the format of the string does not matter; eg you can add 51 to &quot;A99Z1&quot

Public Function AlphaInc(strAlpha As String, Optional Carry As Long = 1) As String
Dim lp As Long
Dim CharVal As Long
Dim strResult As String
Dim CharSetPos As Long
Dim CharBase As Long
Dim testChar As String

strAlpha = UCase(strAlpha)
strResult = Space(Len(strAlpha))
For lp = Len(strAlpha) To 1 Step -1
testChar = Mid(strAlpha, lp, 1)
If testChar >= &quot;0&quot; And testChar <= &quot;9&quot; Then
CharSetPos = 48
CharBase = 10
Else
CharSetPos = 65
CharBase = 26
End If
CharVal = Asc(Mid(strAlpha, lp, 1)) - CharSetPos
CharVal = CharVal + Carry
Carry = CharVal \ CharBase
CharVal = CharVal Mod CharBase
Mid(strResult, lp, 1) = Chr(CharVal + CharSetPos)
Next

AlphaInc = strResult
End Function

 

[strongm]

Is the above of any interest all?

 

[MichaelRed]

strongm,

It works - at least for incrementing the last number appropiatly. You mention that it can:

&quot;1) you can add any number to the A-N string
.

I tried a few combinations:

? AlphaInc(&quot;AA99Z01&quot;, 12)
AA99Z13

? AlphaInc(&quot;AA99Z1&quot;, 12)
AB00A3

? AlphaInc(&quot;Z1&quot;, 12)
A3

? AlphaInc(&quot;AA99Zxy&quot;, 12)
AA99ZYK

And not that the results, while 'reasonable', would not be necessarily intuitive. It would get my vote for the most effective soloution, with the suggestion that some examples and an explaniation of what they represent be included as &quot;Usage&quot; comments at the top of the code. I think, that by now, we can more or less assume that &quot;pro bum&quot; is not going to tell us what he decided to use. My soloution works - as long as the format of the value stays as pro bum gave as an example. Other usage may like your soloution. however this is such an obviously specific requirement that most of the bells and whistles are wasted effort, except as an illustration of technique and ability.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[strongm]

MichaelRed,

All fair comments. My one riposte would be that I tend to prefer, where possible, generic solutions.

Mike