自然或人为排序 [英] Natural or Human Sort order

问题描述

我已经为此工作了几个月.我只是无法获得 natural (真实的字母数字)结果.自1992年以来我就可以使用 EBCDIC 在 RPG 中获得它们，这让我感到震惊.

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.

我正在寻找SQL，VBS或简单Excel或访问的任何解决方案.这是我的数据:

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

库存是7800条记录，所以我在处理能力上也遇到了一些问题.

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

任何帮助将不胜感激.

杰夫

推荐答案

在本机Excel中，您可以添加多个排序列以返回每个字符的ASCII代码，但是如果字符是数字，则将一个大数字添加到代码(例如1000).

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.

公式:

=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,"")

排序"对话框:

结果:

您可以使用VBA以及可能的SQL来实现类似的算法.我不了解VBS或Access.

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

这篇关于自然或人为排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！