應用場景:工作中,常常需要從單元格資料中擷取一部分進行統計、計算,或者從不同系統匯出的資料中提取中文、數字和英文等,如何快速提取需要的資料呢?介紹4種常用方法。
第一種方法,用文字擷取函式三劍客(LEFT/RIGH/MID函式)和文字長度度量函式(LEN /LENB)來提取
1、LEFT函式和RIGHT函式
引數:
LEFT (①要擷取的文字或字串,②要擷取的字元個數)
RIGHT (①要擷取的文字或字串,②要擷取的字元個數)
2、MID函式
引數:
MID(①要擷取的文字或字串,②從哪個位置開始擷取,③要擷取的字元個數)
3、LEN和LENB函式
LEN()& LENB()函式的引數很簡單,只有一個,就是您要測量長度的文字,LEN()函式和LENB()函式的區別就是前者測量字元數,後者測量位元組數,只有在文字字串中包含漢字時才會有區別。
4、案例
案例1:從身份證號中擷取省市區程式碼、出生年月和順序及校驗碼
省區程式碼:從左邊取6位。C2公式:Left(B2,6)
出生年月:從第7位資料開始取,要取出8位數。D2公式:Mid(B2,7,8)
順序號和校驗碼:從右邊4位數。E2公式:Right(B2,4)
案例2: 將單元格中的中英文分開
提取英文:
①在B2單元格錄入公式:=LEFT(A2,LEN(A2)*2-LENB(A2))
②下拉填充公式。
公式解析:
LEN(A2)*2得出的結果是:12 (效果相當於:love愛love愛)
LENB(A2)得出的結果是:8 (效果相當於: love愛愛)
LEN(A2)*2-LENB(A2)得出的結果是:12-8=4(效果相當於:love愛love愛- love愛愛=love)
最後用LEFT函式從左取相對應的英文字母數即可提取英文單詞。
提取中文:
①在C2單元格錄入公式:=RIGHT(A2,LENB(A2)-LEN(A2))
②下拉填充公式。
公式解析:
LENB(A2)得出的結果是:8 (效果相當於:love愛愛)
LEN(A2)得出的結果是:6(效果相當於:love愛)
LENB(A2)-LEN(A2)得出的結果是8-6=2 (love愛愛- love愛=愛)
最後用RIGHT函式從右取相對應的漢字字元數即可提取中文。
第二種方法:用EXCEL快速填充功能提取
快速填充是EXCEL2013以上版本的一個新功能,她能模擬、識別你的操作,推測你內心的想法,然後按照你的想法進行資料填充。
運用快速填充時,首先直接輸入一組到三組資料,讓Excel自動識別你的意圖,再進行快速填充,快速填充的快捷鍵為Ctrl+E,也可以單擊“資料”選項卡 > 在“資料工具”組中 > 單擊“快速填充”或選中示例單元格,拖動填充柄往下填充,然後在“自動填充”選項中選擇“快速填充”來實現。(詳細教程可見“EXCELl學習微課堂分享的《EXCEL快速填充功能如此牛逼,你知道嗎?》)
案例動態演示:
第三種方法:用自定義函式myget
函式有兩引數,=myget(①從哪提取,②提取什麼)
=myget(字串,0) 取出數字
=myget(字串,1) 取出中文字元
=myget(字串,2) 取出英文字母
=myget(字串,3) 取出特殊字元
=myget(字串,4) 取出取第一個數字的位置
=myget(字串,5) 取出取最後一個數字的位置
方法步驟:
首先匯入自定義函式的模板檔案,點開發工具→VBA或都按ALT+F11 調出VBA 編輯器 →在工程視窗右鍵選擇【匯入檔案】→選擇VBA 模組檔案(提取中文、英文和數字自定義函式myget.bas)→關閉VBA 編輯器。然後再輸入公式即可。
動態演示:
第四種方法:用【資料】→【分列】的方法
案例步驟:
1、【選中需分列的列】→【資料】→【分列】,在彈出的文字分列嚮導設定視窗中,根據實際情況選擇分隔符號或固定寬度,案例中都是用/分隔的,所以選【分隔符號】→【下一步】
2、選擇分隔符號為其他符號【/】→【下一步】
3、選列區域的格式,案例中選擇預設的常規格式即可,目標區域選擇$B$1(即分列的結果放在哪)點【完成】資料按要求進行了分類提取到3列。
動態演示:
小夥伴們,EXCEL快速提取方法都齊了,大家可根據需要選取不同的方法!
我是EXCEL學習微課堂,分享EXCEL學習小技巧,小經驗。如果分享的內容對您有幫助,歡迎點贊、收藏、評論、轉發,更多的EXCEL技能,可以關注“EXCEL學習微課堂”。如需自定義函式myget程式碼檔案,可私信聯絡我!