Search code examples
sqlexcelms-access

Natural or Human Sort order

I have been working on this on for months. I just cannot get the natural (True alpha-numeric) results. I am shocked that I cannot get them as I have been able to in RPG since 1992 with EBCDIC.

I am looking for any solution in SQL, VBS or simple excel or access. Here is the data I have:

299-8, 
3410L-87, 
3410L-88, 
420-A20, 
420-A21, 
420A-40, 
4357-3, 
AN3H10A, 
K117GM-8, 
K129-1, 
K129-15, 
K271B-200L, 
K271B-38L, 
K271D-200EL, 
KD1051, 
KD1062, 
KD1092, 
KD1108, 
KD1108, 
M8000-3, 
MS24665-1, 
SK271B-200L, 
SAYA4008

The order I am looking for is the true alpha-numeric order as below:

AN3H10A, 
KD1051, 
KD1062, 
KD1092, 
KD1108, 
KD1108, 
K117GM-8, 
K129-1, 
K129-15, 
MS24665-1,
M8000-3, 
SAYA4008, 
SK271B-200L

The inventory is 7800 records so I have had some problems with processing power as well.

Any help would be appreciated.

Jeff

Solution

  • In native Excel, you can add multiple sorting columns to return the ASCII code for each character, but if the character is a number, then add a large number to the code (e.g 1000).

    Then sort on each of the helper columns, including the first column in the table, but not in the sort.

    The formula:

    =IFERROR(CODE(MID($A1,COLUMNS($A:A),1))+AND(CODE(MID($A1,COLUMNS($A:A),1))>=48,CODE(MID($A1,COLUMNS($A:A),1))<=57)*1000,"")

    The Sort dialog:

    enter image description here

    The results:

    enter image description here

    You can implement a similar algorithm using VBA, and probably SQL also. I dunno about VBS or Access.