文本数字混合后，Excel排序失灵？别怕，有ta!

大家知道的排序依据是什么吗？

首先

1.对数值的排序依据是数值的大小；

2.对文本的排序依据是文本一个字符一个字符地进行，例如姓名，先看首字母excel怎么在一列数字前补零，然后首字母相同再看次字母......

3.对文本与数字组合形式，排序的规则比较复杂。

实例

如下图左侧A列工号是由字母和数字组成的，如何「先按字母升序，然后按照数字大小升序」式的“升序”排序成右侧的样子？

↓

(正确的文本排序)

首先，我们对A列进行默认的升序排序，发现排序后的结果并没有按照我们想象的那样：顺序全是乱的。

(错误的排序)

分析：排序没能实现预期是因为，字母（文本）和数字（数值）组合后就变成了文本，那么排序的规则是按照文本的排序规则：一个字符一个字符进行排序。

先对第一个字符排序

（本例是字母）

↓

再对第二个字符排序

（本例是文本型的数字）

例如，A11＜A3

但这与我们认为的A11＞A3是相违背的

↓

然后对第三个、第四个字符进行排序……

因此如果数字的位数不一样，排序就会跟我们的预期不一致；

但当位数一致时，排序就不会出现问题。

思路：所以，我们可以通过构造0占位符，使数字的位数保持一致即可。也就是说，我们无法对A11,A3直接比较，但是我们可以对A011,A003进行逐字比较大小！

因此，问题转化为，先将组合文本拆成字母和数字，然后将数字转换成统一的3位数文本，然后再组合字母文本及3位数的数字文本即可！

注意：以下操作是在 2019版本中进行的，不同版本，操作界面会有些许差异~

输入公式构建辅助列

我们分析看到，本例的工号后面的数字最多只有3位，所以我们确保所有的数字都是3位即可excel怎么在一列数字前补零，不足3位的，圈零顶位。

在辅助列首行输入

=LEFT(A4,1)&TEXT((A4,LEN(A4)-1),"000")

嵌套公式说明：

1. 左侧的=LEFT(A4,1)：是提取原工号中左端的字母（LEFT函数是从左侧起提取指定个字符），这里的结果是A；

2. =(A4,LEN(A4)-1)：是提取原工号中的数字；

（LEN函数是先提取字符串的个数，然后 “LEN(A4)-1”是计算减去1个字母后的个数，也就是数字的个数excel怎么在一列数字前补零，此处工号A11的数字个数是2，然后用函数从右侧提取2个字符，此处是11）

3. =TEXT((A4,LEN(A4)-1),"000")：是将提取出来的数字变为三位数的显示形式，不足的位数用0补齐，此处结果则是011。

4. 最后连接符【&】将左侧LEFT函数和右侧的嵌套函数组合，输出结果便是字母加三位数的数字（不足圈零顶位），此处是A011。

填充公式

鼠标放在以上公式的单元格右下角，变成“+”型时，双击，填充公式即可。

升序辅助列

然后我们对占位后的辅助列G列，进行升序排序，最后删除辅助列，大功告成！

全部操作过程见动图：

练习源文件

友情提示：记住提取码，然后复制链接到浏览器中打开，输入提取码即可~

链接：

提取码：js34

原创不易，如果你喜欢这篇文章，欢迎分享到朋友圈，或者转发给同事朋友。也欢迎你在留言区，分享您的压箱底神器。

- END -

免责声明：本文系转载，版权归原作者所有；旨在传递信息，不代表本站的观点和立场和对其真实性负责。如需转载，请联系原作者。如果来源标注有误或侵犯了您的合法权益或者其他问题不想在本站发布，来信即删。