blocks|key|1097159|text|在本机Excel中，可以添加多个排序列以返回每个字符的ASCII代码，但如果该字符是数字，则向代码中添加一个大数字(例如1000)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1097160|然后对每个助手列进行排序，包括表中的第一个列，而不是排序。|1097161|公式：|1097162|=IFERROR(CODE(MID($A1,COLUMNS($A:A),1))%2BAND(CODE(MID($A1,COLUMNS($A:A),1))>=48,CODE(MID($A1,COLUMNS($A:A),1))<=57)*1000,"")|code-block|syntax|javascript|1097163|排序对话框：|1097164|​|1097165|📷|atomic|offset|length|1097166|1097167|结果：|1097168|1097169|1097170|1097171|您可以使用VBA实现类似的算法，也可以使用SQL实现。我不知道VBS或Access的事。|1097172|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/0f4523cd38f075795d16d1a07ed037ae.png|imageAlt|1|https://developer.qcloudimg.com/http-save/yehe-900000/6f7074fb4e01b3a4886969e0619d4109.png^0|0|0|0|0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1E|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1F|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1H|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|1I|8|@]|9|@$R|1J|S|1K|1|1L]]|A|$]]|$1|T|3|N|5|6|7|1M|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1N|8|@]|9|@]|A|$]]|$1|W|3|N|5|6|7|1O|8|@]|9|@]|A|$]]|$1|X|3|P|5|Q|7|1P|8|@]|9|@$R|1Q|S|1R|1|1S]]|A|$]]|$1|Y|3|N|5|6|7|1T|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1U|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18|19|-4]]|1A|$5|14|15|16|A|$17|1B|19|-4]]]]

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:

<pre><code>=IFERROR(CODE(MID($A1,COLUMNS($A:A),1))+AND(CODE(MID($A1,COLUMNS($A:A),1))&gt;=48,CODE(MID($A1,COLUMNS($A:A),1))&lt;=57)*1000,"")
</code></pre>

The Sort dialog:

<a href="https://i.stack.imgur.com/6NqoX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6NqoX.png" alt="enter image description here"></a>

The results:

<a href="https://i.stack.imgur.com/K6YMn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K6YMn.png" alt="enter image description here"></a>

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

blocks|key|1884316|text|您可以尝试使用格式对左填充字符串按顺序|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1884317|select+column
from+my_table+
order+by++Format(column,+"0000000000")|code-block|syntax|javascript|1884318|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could try using format for left padding the string in order by 

<pre><code>select column
from my_table 
order by Format(column, "0000000000")
</code></pre>

blocks|key|928296|text|添加排序列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|928297|,+iif+(left(fieldname,+1)+between+'0'+and+'9',+1,+0)+sortField
etc
order+by+sortField,+FieldName|code-block|syntax|javascript|928298|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Add a sorting column:

<pre><code>, iif (left(fieldname, 1) between '0' and '9', 1, 0) sortField
etc
order by sortField, FieldName
</code></pre>

blocks|key|1884362|text|假设您的数据在"A“栏中。如果将这个公式放在"B“列=IFERROR(IF(LEFT(A1,1)%2B1>0,"ZZZZZZZ+++++"&A1,A1),A1)中，它将自动在所有数值前面添加Z，以便在对A排序时，它们会自然地出现在所有字母值之后。稍后你可以找到并替换那个有趣的ZZZZZZ字符串..。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1884363|entityMap^0|Q|1G|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Lets say you have your data in column "A". If you put this formula in column "B" <code>=IFERROR(IF(LEFT(A1,1)+1&gt;0,"ZZZZZZZ "&amp;A1,A1),A1)</code>, it will automatically add Z in front of all numerical values, so that they will naturally appear after all alphabetical values when you sort A-Z. later you can find&amp;replace that funny ZZZZZZ string...

blocks|key|1884776|text|有许多方法，但工作量可能最少的是构建两个列，在这种情况下分隔分隔符(-)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1884777|然后将结果(空格或0)“填充”右对齐，然后对这两列进行排序。|1884778|因此，在查询构建器中，我们有以下内容：|1884779|SELECT+Field1,+
Format(
Mid(field1,1,IIf(InStr(field1,"-")=0,50,InStr(field1,"-")-1)),
">@@@@@@@@@@")+AS+Expr1,
Format(
Mid(field1,IIf(InStr(field1,"-")=0,99,InStr(field1,"-")%2B1)),
">@@@@@@@@@@")+AS+Expr2
FROM+Data|code-block|syntax|javascript|1884780|当我们运行上面的原始查询时，我们得到以下内容：|1884781|​|1884782|📷|atomic|offset|length|1884783|1884784|因此，现在在查询生成器中，只需对第一个派生列进行排序，然后对第二个派生列进行排序。|1884785|例如：|1884786|1884787|1884788|1884789|运行查询，我们将得到以下结果：|1884790|1884791|1884792|1884793|编辑：|1884794|从你想要的结果来看，上面的排序似乎是错误的。我们必须右转，然后用0来填补。|1884795|所以第二次尝试：|1884796|SELECT+Field1,+
Left(Mid(field1,1,IIf(InStr(field1,"-")=0,30,InStr(field1,"-")-1))+
&+String(30,"0"),30)+AS+Expr1,+
Left(Mid(field1,IIf(InStr(field1,"-")=0,99,InStr(field1,"-")%2B1))+
&+String(30,"0"),30)+AS+Expr2
FROM+Data|1884797|因此，结果如下：|1884798|1884799|1884800|1884801|考虑到您的小表大小，那么上面的查询应该会执行得很好。|1884802|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/a72f45573849aeef174d8a6a46e2e94b.png|imageAlt|1|https://developer.qcloudimg.com/http-save/yehe-900000/9e19d04b399abc81768ef2c0499b4523.png|2|https://developer.qcloudimg.com/http-save/yehe-900000/106626b3f86365391d6624f44dc7db8c.png|3|https://developer.qcloudimg.com/http-save/yehe-900000/0a306b3f8b70f94fb8d8a7b23d6f40e1.png^0|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|1|1|0|0|0|0|0|1|2|0|0|0|0|0|0|0|0|0|1|3|0|0|0^^$0|@$1|2|3|4|5|6|7|20|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|21|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|22|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|23|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|24|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|25|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|26|8|@]|9|@$R|27|S|28|1|29]]|A|$]]|$1|T|3|N|5|6|7|2A|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|2B|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|2C|8|@]|9|@]|A|$]]|$1|Y|3|N|5|6|7|2D|8|@]|9|@]|A|$]]|$1|Z|3|P|5|Q|7|2E|8|@]|9|@$R|2F|S|2G|1|2H]]|A|$]]|$1|10|3|N|5|6|7|2I|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|2J|8|@]|9|@]|A|$]]|$1|13|3|N|5|6|7|2K|8|@]|9|@]|A|$]]|$1|14|3|P|5|Q|7|2L|8|@]|9|@$R|2M|S|2N|1|2O]]|A|$]]|$1|15|3|N|5|6|7|2P|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|2Q|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|2R|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2S|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|H|7|2T|8|@]|9|@]|A|$I|J]]|$1|1E|3|1F|5|6|7|2U|8|@]|9|@]|A|$]]|$1|1G|3|N|5|6|7|2V|8|@]|9|@]|A|$]]|$1|1H|3|P|5|Q|7|2W|8|@]|9|@$R|2X|S|2Y|1|2Z]]|A|$]]|$1|1I|3|N|5|6|7|30|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|6|7|31|8|@]|9|@]|A|$]]|$1|1L|3|-4|5|6|7|32|8|@]|9|@]|A|$]]]|1M|$1N|$5|1O|1P|1Q|A|$1R|1S|1T|-4]]|1U|$5|1O|1P|1Q|A|$1R|1V|1T|-4]]|1W|$5|1O|1P|1Q|A|$1R|1X|1T|-4]]|1Y|$5|1O|1P|1Q|A|$1R|1Z|1T|-4]]]]

There a number of approaches, but likely the least amount of work is to build two columns that split out the delimiter (-) in this case.

You then “pad” the results (spaces, or 0) right justified, and then sort on the two columns. 

So in the query builder we have this:

<pre><code>SELECT Field1, 
Format(
Mid(field1,1,IIf(InStr(field1,"-")=0,50,InStr(field1,"-")-1)),
"&gt;@@@@@@@@@@") AS Expr1,
Format(
Mid(field1,IIf(InStr(field1,"-")=0,99,InStr(field1,"-")+1)),
"&gt;@@@@@@@@@@") AS Expr2
FROM Data
</code></pre>

When we run the above raw query we get this:

<a href="https://i.stack.imgur.com/Vu23g.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Vu23g.png" alt="enter image description here"></a>

So now in the query builder, simply sort on the first derived column, and then sort on the 2nd derived column.

Eg this:

<a href="https://i.stack.imgur.com/YG2AS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YG2AS.png" alt="enter image description here"></a>

Run the query, and we get this result:

<a href="https://i.stack.imgur.com/E1NoU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E1NoU.png" alt="enter image description here"></a>

Edit:

Looking at you desired results, it looks like above sort is wrong. We have to RIGHT just and pad with 0’s.

So this 2nd try:

<pre><code>SELECT Field1, 
Left(Mid(field1,1,IIf(InStr(field1,"-")=0,30,InStr(field1,"-")-1)) 
&amp; String(30,"0"),30) AS Expr1, 
Left(Mid(field1,IIf(InStr(field1,"-")=0,99,InStr(field1,"-")+1)) 
&amp; String(30,"0"),30) AS Expr2
FROM Data
</code></pre>

The results are thus this:

<a href="https://i.stack.imgur.com/PuHnG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PuHnG.png" alt="enter image description here"></a>

Given your small table size, then the above query should perform quite well.

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

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

<pre><code>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
</code></pre>

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

<pre><code>AN3H10A, 
KD1051, 
KD1062, 
KD1092, 
KD1108, 
KD1108, 
K117GM-8, 
K129-1, 
K129-15, 
MS24665-1,
M8000-3, 
SAYA4008, 
SK271B-200L
</code></pre>

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

Any help would be appreciated.

Jeff

Natural or Human Sort order

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我已经做了好几个月了。我只是无法得到natural (真α-数字)结果。我感到震惊的是，我不能得到他们，因为我能够从1992年以来在RPG与EBCDIC。我正在寻找任何解决方案，在SQL，VBS或简单的excel或access。以下是我掌握的数据：299-8, 3410L-87, 3410L-88, 420-A20, 420-A21, 420A-40, 4357-3, AN3H10A, K117G

问自然或人类排序
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问自然或人类排序EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问自然或人类排序
EN