Chuck,

It is OK for the dot numbers to come before the dashed number as they would
be in a series on the shelf in the correct order as shown. As long as the
1. come before the 11. before the 111. and the numbers after the dot start
with .1 then a .11 then a .111, etc. Same goes for the dashed numbers.

I also do not have to deal with every type of number all the time except
for my own. What I have tried to do is build a table of numbers that would
cover most if not all situations. I spent a week looking at every possible
number I could deal with and I believe they are all represented by the
table.

Marsh has commented there are some difficult number patterns to deal with.
These can be skipped for the more likely one, i.e. part numbers in my own
product line I am hoping.

Hope this info helps a bit.

Thanks

Tom



On Wed, 26 Sep 2007 00:27:07 -0400, Chuck wrote:

On Tue, 25 Sep 2007 17:12:54 -0400, Tom wrote:

On Tue, 25 Sep 2007 15:39:56 -0400, Chuck wrote:

On Mon, 24 Sep 2007 18:15:41 -0400, Tom wrote:

Chuck,

Here that file with everything sorted.

Thanks

Part Num,L,M,R
1.1,#a,#,s
1.11,#a,##,s
1.111,#a,###,s
1.1111,#a,####,s
1.11111,#a,#####,s
11.1,##a,#,s
11.11,##a,##,s
11.111,##a,###,s
11.1111,##a,####,s
11.11111,##a,#####,s
111.1,###a,#,s
111.11,###a,##,s
111.111,###a,###,s
111.1111,###a,####,s
111.11111,###a,#####,s
1-1,#a,#,s
1-11,#a,##,s
1-111,#a,###,s
1-1111,#a,####,s
1-1111A,#a,####,a
1-1111AA,#a,####,aa
11-11A12,##a,##,a##
11-111A12,##a,###,a##
111-1,###a,#,s
111-11,###a,##,s
111-111,###a,###,s
111-1111,###a,####,s
111-1111A,###a,####,a
111-1111AA,###a,####,aa
111-1111AAA,###a,####,aaa
111-11111,###a,#####,s
111-11111A,###a,#####,a
111-11111AA,###a,#####,aa
111-11111AAA,###a,#####,aaa


A 1.?? comes before a 11.?? and before a 111.??
A 1.1 comes before a 1.11 and comes before a 1.111, etc.

Thanks!

Yes, but anything with a dot anywhere in the sequence is sorted before anything
with a dash anywhere in the sequence. Hence 111. is sorted before 1-

Your UDF (User Defined Function) ("Public Function
StandardizePartNum(PartNum)") (as written) will not handle this. You need
equations that first look for dots, dashes, and neither drops nor dashes. Then
each group must be handled separately with its own UDF

As I said, possible, but complex equations.

Chuck
--
Tom