文本數字混合后，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 -

免責聲明：本文系轉載，版權歸原作者所有；旨在傳遞信息，不代表本站的觀點和立場和對其真實性負責。如需轉載，請聯系原作者。如果來源標注有誤或侵犯了您的合法權益或者其他問題不想在本站發布，來信即刪。