Tom said:
The code below works 100% with my company's part numbers, but I found a
couple of snafu's when dealing with some of our competitors part numbers.
One series of numbers begins with a 52-, i.e.
52-125487
52-60554
The routine below places these types of part numbers in the order shown.
Once the routine see the first non-numeric, it stops there.
We have another competitor that uses numbers like:
100.01547
101.09874
102.55447
110.12345
When you are trying to parse a string into its component
parts, it is essential that you come up with a rigorous set
of rules that work for all possible cases. Then the problem
becomes one of turning the set of rules into code.
Based on your original examples, I came up with the rules:
1) The initial text will be from 0 to 5 non-numeric
characters.
2) The numeric part can be up to 7 figits and immediately
follows the intial text part. The numeric part may or may
not be followed by a trailing text part that starts with any
non-numeric character.
3) The training text part is the reamainder of the string
starting with the first non-numeric character after the
numeric part.
Your new examples do not conform to those rules, so you must
come up with a different set of rules. My **guess** for the
rules is now:
1) The initial text will be from 0 to 3 characters ending
with a non-numeric character.
2) The numeric part can be up to 7 figits and immediately
follows the intial text part. The numeric part may or may
not be followed by a trailing text part that starts with any
non-numeric character.
3) The trailing text part is any reamainder of the string
starting with the first non-numeric character after the
numeric part.
You must decide if my guesses at the rules is valid for all
of your various situations. Pay particular attention to the
3 that I used. If you might get very short strings (e.g. X3
or just 12) then you will need to modify rule 1) and the
code to accomodate the very short strings.
With all that said, here's my guess at a new procedure:
Public Function StandardizePartNum(PartNum)
Dim k As Integer, j As Integer
If IsNull(PartNum) Then
StandardizePartNum = Null
Exit Function
End If
'find last non-digit
For k = 3 To 1 Step -1
If Not Mid(PartNum, k, 1) Like "#" Then Exit For
Next k
'find numeric portion
For j = k + 1 To Len(PartNum)
If Not Mid(PartNum, j, 1) Like "#" Then Exit For
Next j
StandardizePartNum = _
Left(PartNum, k) & Space(4 - k) _
& Format(Val(Mid(PartNum, k + 1)), "0000000") _
& Mid(PartNum, j)
End Function
