2973Excel提取字元的萬能公式

Excel提取字元的萬能公式

2973

Excel提取字元的萬能公式

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

image

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

步驟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儲存格。

image

【公式說明】

=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儲存格內容每一個字元,使用雙負號運算,區分數值和其它字元。

公式2ISNUMBER(公式1)

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

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

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

image

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

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

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

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

以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我