本文將用到:
MID(文字/數字,提取起始位,提取位數)
VALUE( ) 結果為括號內數值,若無法計算則報錯
ISERROR( ) 結果為返回括號內是否報錯,TRUE或FALSE
SUBSITITUDE(文字/數字,“待替換內容”,“替換為內容”)
有位來自檔案管理小哥的加急問題:
Excel左邊純數字和數字和文字混合,如何在右邊提取純數字?
從文字+數字混合提取純數字
平時工作中表哥也經常遇到這類問題,只是花樣更多、情況更加複雜。
那麼如何處理這類問題呢?
表哥有兩個思路:一是提取,二是替換
思路一:判斷A列是否為純數字,若為純數字則等於A列,若為文字與數字混合,則使用提取函式將A列提取數字到B列。
常用的提取函式有三個,分別為LEFT(),RIGHT()和MID(),觀察A列的檔案編號數字前後,均有文字出現,那麼這裡就選用MID()。以A1為例,在單元格B2輸入公式:
B2=MI
D(A1,2,6)即可。
判斷是否為純數字再選用合適的函式
但是由於A列內容形式不統一,有的只含數字,有的包含文字和數字兩種。如果是文字與數字混合的情況,用MID()提取就好,如果只包含數字則直接等於A列數字。
如何判斷A列是否是純數字呢?
數字和文字區別之一在於可否參與計算,因此可利用這個區別,借用ISERROR(),VALUE()兩個函式來幫我們判斷。
ISERROR()用來判斷括號裡計算出來的結果是否會報錯,如果報錯ISERROR()返回“TRUE”,如果不報錯,則返回“FALSE”。舉個例子:
ISERROR用法說明舉例
比如零是不可以被除的,當計算100/0時,系統會報錯,ISERROR()會返回“TRUE”;但是零除以任何數都等於0,則ISERROR()會返回“FASLE”。
因此,就可以借用ISERROR()與VALUE()組合來判斷A列是否為數字。
以A1為例,ISERROR(VALUE(A1)),如果A1是文字+數字,則返回TRUE,A1需要經過MID()提取後到B1;如果A1是純數字,則返回FALSE,A1不需要使用MID()提取,B1等於A1即可。
ISERROR結果舉例
將兩部分公式組合在一起,以A1為例,在單元格B1輸入:
B1= IF(ISERROR(VALUE(A1))=TRUE,MID(A1,2,6),A1),
B列其他行按照B1下拉填充即可。
表哥tips:
除了利用數字與文字的可否參與計算的區別,還可以利用文字在系統中字元數和位元組數不同這個屬性。如果你感興趣,研究下LENB()和LEN()這兩個函式吧。如果沒有時間琢磨,請關注並留意表哥近期釋出的文章,答案將在那裡揭曉。
思路二:將A列文字替換為空,只留下純數字即所得。
觀察到A列文字只有兩個,”第”和”號”。因此只要替換掉這兩個字,就可以統一為純數字了。
替換的方法有兩種:一是透過Excel工具欄”替換”操作,一是透過函式SUBSITITUDE()。
方法1:Excel工具欄”替換”操作步驟,可參考文章《如何快速準確調整商品及貨物名稱——利用Excel分列及替換功能》
Excel的替換功能
方法2:透過SUBSITITUDE()函式替換文字
思路類似工具欄替換的操作,也是要替換兩次,因此公式中也要替換兩次。以A1為例,在單元格B1中輸入公式:
B1=SUBSTITUTE(SUBSTITUTE(A1,"第",""),"號",""),
B列其他行下拉填充即可。
SUBSTITUDE替換兩次後的結果
以上案例解決思路一定不止文章中所講的幾種,如果你發現了新思路,歡迎私信表哥或在評論區留言,與大家一起分享你的好點子吧。
完結撒花↖(^ω^)↗