Excel教程:excel表格提取文字中的數字,會這幾招就夠了

點擊圖片  1元搶購  Excel、Word、PS等課程

有讀者留言希望能夠整理一期內容，講解從數字、漢字中提取漢字或者數字的方法，本文將對比較常見的幾類提取情況進行講解。使用公式提取單元格內的數字，要根據具體的數據結構找到某種規律從而設計出對應的公式。當然也有所謂的萬能提取公式，不過非常複雜並且計算量大，因此只在文末簡單說明。

第一類情況：數字在左側

例一：數字在左側，且數字位數固定。

在這個例子中，數據非常有規律，數字都在左邊的三位，要想將學號單獨提取出來，只需要使用left函數即可：=LEFT(A2,3)

公式也非常簡單，第一個參數是要提取數字的單元格，第二個參數是要提取幾位（從左邊算起）。對於例一的這一類數據，使用的時候根據數字的長度修改第二參數即可。

例二：數字在左側，數字位數不固定，但是有明顯的分隔符號。

如果數字長度不是固定的三位，使用之前的方法就不行了，如下圖。這時就需要找到數據源的規律，再利用規律進行操作。當前的數據統一存在一個"-"號，可以根據"-"號的位置來確定數字的長度，再用left提取。

對於這種情況需要用到另一個函數來幫忙，那就是find函數。

利用公式=FIND("-",A2,1)可以確定"-"的位置。

find函數有三個參數，第一個參數是要找什麼內容，第二個參數是從哪裡找，第三個參數是從第幾個字開始找（從左邊算起）。

=FIND("-",A2,1) 意思是在A2單元格找「-」，從開始位置找（左邊第一位）。公式的結果是數字，代表「-」在單元格的位置（第幾個字符），此時我們需要提取的數字長度就是find的結果減1，因此方法就有了：=LEFT(A2,FIND("-",A2,1)-1)

這個方法的適用範圍也比較廣泛，只要可以發現比較明顯的分隔符號（可以是符號，漢字，字母等等內容），都可以使用這個方法。

例三：數字在左邊，位數不固定並且沒有明顯的分隔符號。

如果數據是這種情況，那麼前面的方法都不能用了，數字長度不確定，也沒分隔符號，唯一的規律就是數據裡只有數字和漢字（沒有字母以及其他符號）。

在Excel中，字符的長度有兩種，半角字符（數字，字母以及英文方式下輸入的符號）長度為1，而全形字符（漢字和中文符號等）長度為2。可以通過一個實例來了解這一點：

len函數是一個專門計算單元格內容長度的函數，不區分全形半角，其長度與我們平時理解的一致，相當於字符的「個數」。下面再來看看區分全形和半角的情況：

lenb函數的作用於len函數一致，區別就是計算內容的長度時會區分半角全形，從結果可以看出明顯的差異。其中文字按全形計算的長度是不區分全半角的2倍，而數字、字母在兩種情況下則完全一致。

對於例三的情況，就需要使用len與lenb來確定數字的長度，再用left提取。

利用公式=LEN(A2)*2-LENB(A2)可以確定數字的長度，如下圖所示。

為什麼len*2-lenb就會得到數字的長度呢？

根據前面兩種情況下的對比結果，lenb統計長度，文字長度是len統計值的兩倍，而數字長度與len統計值一致，所以公式len*2-lenb就可以計算出數字的長度了。

數字的長度統計出來以後，解決方法顯而易見：=LEFT(A2,LEN(A2)*2-LENB(A2))

例三這種情況同樣比較典型，只要記住是len*2-lenb就可以了，如果確實記不住，那麼遇到問題的時候可以單獨算一算，先把數字的長度算出來，再組合left就行了。

例四：數字在左邊，並且含有字母等半角字符。

這種情況很複雜，唯一的規律就是數字在左邊。來看看如何解決吧。

可以利用公式：=-LOOKUP(1,-LEFT(A2,ROW($1:$9)))。

關於這個公式，要解釋的話估計還得五千字，所以各位只需要記得套路即可，唯一有可能修改的就是最後面那個9，當最長的數字超過9位時，就要修改這個9，可以直接用99來完成。

最後，這個公式適用於任何數字在左邊的情況。 

第二類情況：數字在右邊

數字在右邊的情況與數字在左邊非常類似，以下僅做簡單說明：

數字位數固定的直接用right提取；

數字位數不固定但有分隔符號的使用find函數配合mid函數 完成提取：=MID(A2,FIND("-",A2,1)+1,9)

（註：公式中最後一個參數值「9」需要根據數字的最大位數修改，譬如提取的數字最大位數是10，則需要寫成10或者大於10的數字。）

數字位數不固定同時沒有分隔符的還是可以使用len和lenb的組合：=RIGHT(A2,LEN(A2)*2-LENB(A2))

什麼規律都沒有的繼續讓lookup發大招：=-LOOKUP(1,-RIGHT(A2,ROW($1:$9)))

第三類情況：數字在中間

數字在中間這種非常特殊，比較規範的數據源中一般不會出現這樣的情況，舉一個例子吧：

例如圖中這種數據，如果數字前面或者後面的長度固定，可以先用函數將內容變成前面討論過的情況再去處理：

公式比較簡單，不做解釋了，至於後面一步怎麼做，根據前面的學習你已經會了吧？

下面要說的這個方法就是一種比較通用的方法了，有人稱其為萬能提取公式：

=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))

數組公式，需要按三鍵「Ctrl+shift+回車」結束。 

小結

關於如何使用公式提取單元格內的數字，以上將絕大多數情況都進行了說明，當然不是全部，例如含有小數的情況，因為有了小數點，就更加特殊了。

最後的那個數組公式可以算是一個比較通用的公式了。需要強調的是：大部分的問題之所以變得非常麻煩，就是因為不規範的數據源導致的，因此養成好的習慣，一個單元格只存放一種屬性的數據，例如把名稱與數量、價格等信息分開存放，這樣就會大大地提高統計效率。

當然站在提高公式運用能力的角度來說，數字提取這一類問題也是函數練習的好題目。希望大家能夠結合以前學過的函數，思考出更多的公式來實現數字的提取。

在線諮詢Excel課程

想要全面系統學習Excel，不妨關注部落窩教育的《一周Excel直通車》視頻課或者《Excel極速貫通班》。

《一周Excel直通車》視頻課

包含Excel技巧、函數公式、

數據透視表、圖表。

一次購買，永久學習。

最實用接地氣的Excel視頻課

《一周Excel直通車》

風趣易懂，快速高效，帶您7天學會Excel

38 節視頻大課

（已更新完畢，可永久學習）

理論+實操一應俱全

主講老師： 滴答

 

Excel技術大神，資深培訓師；

課程粉絲100萬+；

開發有《Excel小白脫白系列課》

        《Excel極速貫通班》。

原價299元

限時特價 99 元

少喝兩杯咖啡，少吃兩袋零食

就能習得受用一生的Excel職場技能！

  長按下面二維碼立即購買學習

購課後，加客服微信：blwjymx2領取練習課件

讓工作提速百倍的「Excel極速貫通班」

↓ 點擊閱讀原文，免費試聽。