UC知道excel带字母数字排序
来源：百度知道 编辑：UC知道 时间：2024/05/29 07:35:23
有280个序列,在excel自动排序中，怎么会出现下列排序：
ab0.txt
ab1.txt
ab10.txt
ab100.txt
ab101.txt
ab103.txt
ab106.txt
ab108.txt
ab109.txt
ab11.txt
ab110.txt
ab111.txt
ab112.txt
ab114.txt
ab115.txt
ab117.txt
ab119.txt
ab12.txt
ab120.txt
ab121.txt
ab122.txt
ab124.txt
ab126.txt
ab129.txt
ab13.txt
ab130.txt
ab131.txt
ab132.txt
ab133.txt
ab134.txt
ab135.txt
ab139.txt
ab140.txt
ab141.txt
ab142.txt
ab143.txt
ab148.txt
ab149.txt
ab15.txt
ab150.txt
ab152.txt
ab153.txt
ab154.txt
ab157.txt
ab158.txt
ab159.txt
ab160.txt
ab161.txt
ab162.txt
ab163.txt
ab167.txt
ab168.txt
ab169.txt
ab17.txt
ab171.txt
ab172.txt
ab177.txt
ab178.txt
ab179.txt
ab18.txt
ab180.txt
ab181.txt
ab189.txt
ab19.txt
ab190.txt
a
ab0.txt
ab1.txt
ab10.txt
ab100.txt
ab101.txt
ab103.txt
ab106.txt
ab108.txt
ab109.txt
ab11.txt
ab110.txt
ab111.txt
ab112.txt
ab114.txt
ab115.txt
ab117.txt
ab119.txt
ab12.txt
ab120.txt
ab121.txt
ab122.txt
ab124.txt
ab126.txt
ab129.txt
ab13.txt
ab130.txt
ab131.txt
ab132.txt
ab133.txt
ab134.txt
ab135.txt
ab139.txt
ab140.txt
ab141.txt
ab142.txt
ab143.txt
ab148.txt
ab149.txt
ab15.txt
ab150.txt
ab152.txt
ab153.txt
ab154.txt
ab157.txt
ab158.txt
ab159.txt
ab160.txt
ab161.txt
ab162.txt
ab163.txt
ab167.txt
ab168.txt
ab169.txt
ab17.txt
ab171.txt
ab172.txt
ab177.txt
ab178.txt
ab179.txt
ab18.txt
ab180.txt
ab181.txt
ab189.txt
ab19.txt
ab190.txt
a
假设你的数据在A列，从A1开始
B1输入公式
=LEFT(A1,2)&TEXT(MID(A1,3,FIND(".",A1)-2),"000")&RIGHT(A1,4)
将B1公式向下复制，即可
公式也可以简化为：
="ab"&TEXT(MID(A1,3,FIND(".",A1)-2),"000")&".txt"