自然或人为排序

问题描述

我已经为此工作了几个月.我只是无法获得 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.

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