2973Excel提取字元的萬能公式 @ 錦子老師

2973

Excel提取字元的萬能公式

江澤民：「錦子老師您好，我有一個表格A欄全部都是文數字的集合，由於筆數最少百筆多則萬筆，我要如何才可以將其中的數字擷取出來到B欄，若為負值，則連負號一併擷取出來？麻煩解惑 ~ 感恩，謝謝！」

錦子老師：「這個問題其實很複雜，接下來說明操作方法如下：

步驟1：點取B2儲存格輸入公式：

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100),ROW($ 1:$100))+1,1)*10^ROW($1:$100)/10)

按CTRL+SHIFT+ENTER鍵完成陣列公式輸入，並將公式複製到B3:B7儲存格。

【公式說明】

=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100),ROW($ 1:$100))+1,1)*10^ROW($1:$100)/10)

公式1：--MID(A2,ROW($1:$100),1)

通過MID函數逐一提取A2儲存格內容每一個字元，使用雙負號運算，區分數值和其它字元。

公式2：ISNUMBER(公式1)

使用ISNUMBER函數判斷公式1每一個字元是否為數值，傳回一組邏輯值(TRUE/FALSE)

公式3：LARGE(公式2*ROW($1:$100),ROW($ 1:$100))

將公式2*ROW($1:$100)使得數值傳回其在A2儲存格文數字混合的位置，其他字元則傳回0。通過LARGE函數，將公式2中的字元位置值集合從大到小重新排序。由於數值在文字中的位置總是大於0，且數值越靠後，位置值越靠前。而其他字元總是小於0的。這裡的重點是將所有的0值置後，同時將所有數字位置值倒排。

公式4：MID(0&A2,公式3+1,1)

MID根據公式3的位置值+1從0&A2中逐一取數，由於公式3非數值的位置值為0，所有非數字傳回值均取首位0，其餘數字不受影響。由於公式3的數字位置值是顛倒的，所以，此時提取出的數字前後也是顛倒的。

公式5：SUM(公式4*10^ROW($1:$100)/10)

前四步得到了A2儲存格中的所有數字和一串代表非數字位置的0組成的有序數組，此時要完成最終的提取，還需要將數位正序排列、去除0值並將其合併。這些通通交由*10^ROW($1:$100)/10完成，它通過構建一個多位數來將各個數位順序擺放，最終將代表文字的有效數位前的0值省略，其餘數值按次序從個位開始向左排列。最終的多位數即數值提取結果。