再雜也無懼 在Excel高效提取數字

大家知道Excel的強項是對純數據進行高效處理，不過在日常工作中，總是由很多用戶總是把Excel當Word用，在輸入數據時總愛將數據和文本、貨幣符號等數據參雜混在一起。這樣Excel無法發揮其數據處理作用，其實我們可以通過以下方法，在Excel中快速把夾雜各種符號的純數據提取出來。

混雜「有規律」，函數來提取

如果混雜的數據是比較有規律的，比如數字都是在文本的右邊或者左邊，那麼藉助內置函數LENB和LEN即可進行提取，假設原始數據如下圖所示，數字都是在漢字的右側。因為這裡數字的數據都包含元，先點擊「查找和選擇替換」，在替換欄輸入「元」，替換為置空，點擊「全部替換」（圖1）。

圖1 替換不需要的字元

接著定位到C2輸入公式「=RIGHT(B2,2*LEN(B2)-LENB(B2))，公式的意思是這裡使用Right函數從B2右邊開始截取字元，因為數字字元在右側，通過函數就可以將右側的數字全部截取出來。如果要統計數字，下拉公式後將其提取的數字粘貼（粘貼方式選擇「數值123」）到D列即可使用求和函數進行統計了。如果數字在左側，則使用LEFT函數進行截取（圖2）。

圖2 統計雜亂數字

混雜無規律，組合函數來提取

上述的數字是有規律的分布在文字的左側或者右側，如果數字分布沒有任何規律，比如下列的數據數字是分布在隨機位置，那麼上述函數就無能為力（圖3）。

圖3 混亂排列的數字

對於類似的數據提取需要藉助組合函數進行提取，定位到C2輸入公式「=-LOOKUP(,-MIDB(B2,SEARCHB("?",B2),ROW($1:$8)))」，然後下拉即可將原來混雜的數字全部提取出來（圖4）。

圖4 提取混亂數字

小提示：

嵌套函數從內到外依次解釋：ROW($1:$8)生成一個1-8的序列，即(1;2;3;4;5;6......)，也就是從1-8行原數據里進行查找。SEARCHB("?",B2)，查找B2單元格中第一個半形字元，其中？代表任何單字元數據，SEARCHB("?",B2) 表示從B2開始查找一個數字字元所在的位置，並將其作為MID截取字元的位置。MIDB(B2,SEARCHB("?",B2),，表示在B2單元格中，從第一個半形字元開始截取，截取位數為1-8位的數字。前邊再加負號（-），將截取的文本數值中是數字的轉變為數值且變為了負數。最後通過Lookup查找序列中不大於0的最末尾的值，前邊再加負號(-)，將原來變成負數的值再變為原值。這樣即可從單元格中分離出的所需的數字，而不需要理會數字在字元中的什麼位置。

字母文字混雜，Word來協助

上述函數都是利用數字（單位元組）和漢字（雙位元組）不同，通過查找單位元組的字元來實現數字的提取。如果原始數據包含字母等單位元組的數據，這類數據我們可以藉助Word的查找與替換來實現提取（圖5）。

圖5 字母數字漢字混雜數據

首先將數字列複製然後粘貼到Word中，點擊「查找與替換，在查找內容輸入 「^$ 」全部替換為空，這樣所有字母就會全部消除。這裡^$是字母正則表達式通配符，表示查找文中所有的字母。這樣完成替換後原來數據中的所有字母就全部消失了（圖6）。

圖6 替換字母

現在上述字元中剩下就全部是數字和漢字，同上繼續打開查找與替換，在查找框輸入「[一-龥]」 （查找全部漢字的通配符，「龥」字是繁體，拼音為yù，谷歌輸入法切換到繁體輸入即可），點擊更多勾選下方「使用通配符」，這樣就能查找所有中文字體，最後替換為空（圖7）。

圖7 替換漢字

這樣通過上述方法替換後就只剩下所需的數字了，不過有些數字的還包含半形空格，再次使用查找替換工具（在查找內容中輸入[^w]，替換為空）進行替換，最後將提取的數字全部複製回Excel中即可（圖8）。

圖8 替換漢字

當然Word的查找與替換功能要比Excel更為強大，對於類似需要在Excel中對字元提取或者替換，既可以直接將數據複製到Word中處理，也可以直接在Word中插入Excel表格，然後利用Word來高效處理。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！