Excel提取字元的萬能公式
|
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值省略，其餘數值按次序從個位開始向左排列。最終的多位數即數值提取結果。
以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~
更多相關影片教學：請點我
更多相關文章：請點我