在工作過程中會有將的文字、字母和數字分離,如果文字、字母和數字的組合是規則的,則可以用簡單的單個函數LEFT、MID以及RIGHT三個函數來處理。如果是不規則的,可以參考以下兩種方法。
如圖1所示:
一、函數法
1.分離文字
在B2單元格輸入公式:
=LEFT(A2,(LENB(A2)-LEN(A2)))
通過公式填充就可以得到文字了。
【解析】
因為漢字佔兩個字節,而字母和數字只佔一個字節。所以用LENB(A2)-LEN(A2)可以得出漢字的數目。
如圖2所示:
2.分離數字
在D2單元格中輸入數組公式:
=MID(A2,MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,A2),"")),20)
通過填充就可以得到數字了。
【解析】
MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,A2),""))用來尋找第一個數字所在的位置。由於後面都是數字,可以用MID來提取數字。如果數字多,可以換成比20更大的數字。
1.ROW($A$1:$A$10)-1 用來生成0-9的內存數組;
2.FIND函數用來尋找各個數字所在的位置;
3.IFERROR函數用來屏蔽錯誤值;
4.MIN函數用來尋找第一個數字所在的位置。
【備註】
其實,可以用數組公式=RIGHT(A2,LEN(A2)-MIN(IFERROR(FIND(ROW($A$1:$A$10)-1,A2),""))+1)來提取數字。思路差不多。
如圖3所示:
3.提取字母
文字和數字出來了,可以利用前兩者來提取字母,用SUBSTITUTE函數。
在C2單元格中輸入公式:
=SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")
這樣就可以得到字母啦。
如圖4所示:
這個函數比較簡單就不做解析啦。
【備註】
能達到以上效果的函數不僅僅限於列出的這幾個,還有很多其他的函數組合能達到這個效果。
二、快速填充法
在OFFICE2013版本以及更高版本中有一種比較智能的填充形式——快速填充。根據你錄入的數據,判斷你的意圖,並根據你的意圖自動填充。這個功能有N種用法,是一個實用性很強的一個功能。
如圖5我已經在手動錄入了一部分數據。
選中A5然後按組合鍵Ctrl+E,就可以快速填充,如圖6所示:
當然,也可以按住鼠標左鍵填充,如圖7所示: