利用公式分拆字符串的字母和數字

王志軍

同事前來求助，A列的數據格式是字母在前、數字在后的形式，現在需要將數據拆分開來，并以加0的形式補足為四位，例如"A01"分拆為A、 01，然后分別補足為OOOA、 0001。除了手工分拆之外，可否利用公式完成呢？

字母列可以利用“=RIGHT（"000"&LEFT（A1，MIN（FIN D（ROW（$1：$10）一1，A1&1/17））-1），4）”的公式，ROW函數可以返回一個引用的行號，FIND函數可以返回一個字符串在另一個字符串出現的起始位置，MIN函數可以返回一組數值中的最小值，LEFT函數是從第一個字符開始返回指定數量的字符最后利用RIGHT函數從右開始返回指定數字的字符公式輸入之后按下“Ctrl+Shift+Enter”組合鍵轉換為數組公式，向下拖拽或雙擊填充柄，很快就可以看到圖1所示的字母效果；數字列可以使用“=RIGHT（"000”&MID（A1，MIN（FIND（ROW（$1.$10）一1，A1&1/17）），99），4）”的數組公式，公式執行之后向下拖拽或雙擊填充柄，很快就可以看到圖2所示的效果。

如果數據量比較大，使用數組公式的效率可能比較低，此時可以直接使用普通公式，字母列使用“=RIGHT（”0000”&LEFT（A1，LEN（A1）-SUM（ISNUMBER（MID（A1，{1；2；3；4；5；6；7；8}，1）+1）+0）），4）”的公式，這里首先使用MID函數將字符串的字母和數字逐一提取出來，接下來使用ISNUMBER函數判斷是否屬于數值，返回值為TRUE，通過"+0"將TRUE轉換為"1"，接下來通過LEN函數計算字符串的長度，減去數字的個數就是字母的個數；數字列使用“=TEXT（RIGHT（A1，SUM（ISNUMBER（MID（A1，{1；2；3；4；5；6；7；8}，1）+1）+0）），"0000"）”的公式，這里同樣使用MID函數將字符串的字母和數字逐一提取出來，接下來使用ISNUMBER函數判斷是否屬于數字，返回值為TRUE，通過“+0”將TRUE轉換為“1”，接下來計算數字的個數，并提取所有的數字，最終效果如圖3所示。endprint