每天一點小技能
職場打怪不得慫
編按：在Excel中，文本函數看似簡單，運用起來卻常常讓人摸不著頭腦。因為，我們常常忽略了要更好的掌握一門技術，首先要具備牢固的基礎知識。於是，小E邀請小可，寫了關於文本函數的一系列教程，讓你從九大基礎經典案例中，輕鬆掌握。今天，講的是文本函數中的LEFT、RIGHT函數，LEN、LENB函數，EXACT函數…
大家好，我是部落窩教育的小可，好久不見~
文本函數是工作中必備的函數，使用頻率高，大家多多少少都會一些它的基礎用法。可是在實際工作中，很多童鞋面對數據的提取、查找、轉化、替換等問題時，卻還是看著LEFT、MID、FIND等函數，不知怎麼靈活運用。導致這一現象的一個很大原因是沒有系統的學習過文本函數！下面給大家總結了各個文本函數的經典案例，很高興和大家分享~
本期目錄先呈上~~~
一、LEFT和RIGHT1.1提取混合內容中的姓名1.2將編號變成「0000」形式1.3提取字符串左側的連續數字1.4姓氏+先生/女士二、LEN和LENB2.1分離出中文2.2分離出英文三、EXACT函數3.1一對多的比較3.2多對多的比較
先介紹一點基礎知識——字符和位元組
位元組：表示數據量的多少，是計算機信息技術用於計量存儲容量的一種計量單位；
字符：是指計算機中使用的文字和符號。
注意：一般在英文狀態下一個字母或字符占用一個位元組，一個漢字用兩個位元組表示。
一、LEFT和RIGHT
1.1提取混合內容中的姓名
要求：A列是姓名和學號，需要將姓名提取到B列。
方法：在B3單元格輸入公式：=LEFT(A3,LENB(A3)-LEN(A3))，向下複製填充公式。
解讀：LENB(A3)-LEN(A3)表示用LENB計算出位元組數，用LEN函數計算出字符數後，兩者相減得出文本中雙位元組（即漢字）的字符長度。最後，再用LEFT函數從左邊提取雙位元組的文本。
掃碼群里下載練習課件
1.2將編號變成「0000」形式
要求：將E列的編號設置成「0000」格式。
方法：在F3單元格輸入公式：=RIGHT("000"&E3,4)，向下複製填充公式。
解讀：因為最終編號限制在四位數，少於四位用「0」在前面補位，所以可以在每個編號前面加上「000」（也可以加上四個0），再用LEFT函數從右邊提取最後四位數。
補充：將編號設置成「0000」格式，也可以用自定義格式，但上述方法也是很機智的哦~~~
1.3提取字符串左側的連續數字
要求：提取出A列右側的連續數字到B列。
方法：在B12單元格輸入公式：=LOOKUP(9E+307,--RIGHT(A12,ROW($1:$10)))，向下複製填充公式。
解讀：先使用RIGHT函數從A12單元格右側，截取長度為1~10的文本字符串，再使用減負運算將文本數字轉換為可運算的數值，將文本內容（漢字、英文、標點）轉換為錯誤值「#VALUE！」。最後使用VLOOKUP函數，以9E+307作為查找值，在由錯誤值和數值組成的內存數組中，提取最後一個數值。
注意：9E+307是使用科學記數法表示的9*10^307，近似Excel允許輸入的最大值。
1.4姓氏+先生/女士
①無複姓
要求：在C列設置尊稱為「姓氏+先生/女士」（無複姓）
方法：在C19單元格輸入公式：=LEFT(A19,1)&IF(B19="女","女士","先生")，向下複製填充公式。
解讀：用LEFT函數從左邊提出字符長度為1的姓氏。再利用IF函數判斷，如果性別為「女」則加上「女士」；反之，則加上「先生」。
②有複姓
要求：在H列設置尊稱為「姓氏+先生/女士」（有複姓）
方法：在H19單元格輸入公式：=LEFT(E19,OR(LEFT(E19,{1,2})=$E$30:$E$32)+1)&IF(F19="男","先生","女士")，按<Ctrl+Shift+Enter>三鍵結束，向下複製填充公式。
解讀：
LEFT(E19,{1,2})部分，在姓名中用LEFT函數從左邊分別提取1個和2個文本字符串，以內存數組的形式保存在公式中。（筆者把結果展示在了G列）
LEFT(E19,{1,2})=$E$30:$E$32部分，將提取出的內存文字與$E$30:$E$32複姓區域分別進行匹配，如果有一個提取出的內存文字能夠與某個複姓匹配，則在OR函數的外層嵌套下為TURE，反之為則FALSE。再在最後+1，讓單姓顯示為1，複姓顯示為2.
LEFT(E19,OR(LEFT(E19,{1,2})=$E$30:$E$32)+1)，若有複姓LEFT函數提取2個文本字符串，若沒有複姓LEFT函數提取1個文本字符串。（結果如圖I列）
二、LEN和LENB
2.1分離出中文
方法：在B3單元格輸入公式：=RIGHT(A3,LENB(A3)-LEN(A3))，向下複製填充公式。
2.2分離出英文
方法：在C3單元格輸入公式：=LEFT(A3,2*LEN(A3)-LENB(A3))，再向下複製填充公式。
解讀：LENB函數將每個漢字（雙位元組字符）的字符數按2計數，LEN函數則對所有的字符數都按1計算。所以，「LENB(A3)-LEN(A3)」返回的結果就是文本字符串中的漢字個數；「LENB(A3)-LEN(A3)」返回的結果就是文本字符串中的英文字母（單位元組字符）個數。
三、EXACT函數
EXACT函數，用來判斷兩個單元格的內容是否完全相同，如果文本字符串完全相同，那麼函數的返回TRUE;如果不完全相同，那麼返回FALSE。
EXACT函數的語法：
EXACT（text1，text2）
注意：在excel中使用等號比較文本值時不區分字母大小寫，而用EXACT可以區分字母大小寫！
如圖，用EXACT函數比較「A」和「a」結果顯示FALSE，用等於號（=）連接結果則顯示TRUE。
補充：EXACT函數的參數還可以是單元格區域引用。
3.1一對多的比較
第一個參數是一個單元格引用；第二個參數是多個單元格區域引用。
如圖，在C8單元格輸入公式：=EXACT(B8,A8:A12)，並向下複製填充。則EXACT函數將返回B8與A8:A12單元格區域每個元素比較的結果。
3.2多對多的比較
如圖，在H8單元格輸入公式：=EXACT(F8:F12,G8:G12)，並向下複製填充。則兩個參數的每一個元素會分別進行比較。
今天的文本函數典型案例就分享到這裡咯，如果對你有幫助，還請多多支持喲^0^~~
掃一掃添加老師微信
掃一掃，在線諮詢Excel課程
Excel教程相關推薦
為什麼說勸你儘早學會這些基礎邏輯函數？這是我見過最好的答案！
如果連這個最基礎的求和統計表都搞不定，就不要抱怨工資低了……「我面試了幾十個大學生，發現他們竟然還在用分列法拆分數據，難怪效率那麼低……」
入職HR第一天，新同事就因為一張不合格的Excel員工檔案表，氣得老闆差點開了他……
想要全面系統學習Excel，不妨
職場打怪不得慫
編按：在Excel中，文本函數看似簡單，運用起來卻常常讓人摸不著頭腦。因為，我們常常忽略了要更好的掌握一門技術，首先要具備牢固的基礎知識。於是，小E邀請小可，寫了關於文本函數的一系列教程，讓你從九大基礎經典案例中，輕鬆掌握。今天，講的是文本函數中的LEFT、RIGHT函數，LEN、LENB函數，EXACT函數…
大家好，我是部落窩教育的小可，好久不見~
文本函數是工作中必備的函數，使用頻率高，大家多多少少都會一些它的基礎用法。可是在實際工作中，很多童鞋面對數據的提取、查找、轉化、替換等問題時，卻還是看著LEFT、MID、FIND等函數，不知怎麼靈活運用。導致這一現象的一個很大原因是沒有系統的學習過文本函數！下面給大家總結了各個文本函數的經典案例，很高興和大家分享~
本期目錄先呈上~~~
一、LEFT和RIGHT1.1提取混合內容中的姓名1.2將編號變成「0000」形式1.3提取字符串左側的連續數字1.4姓氏+先生/女士二、LEN和LENB2.1分離出中文2.2分離出英文三、EXACT函數3.1一對多的比較3.2多對多的比較
先介紹一點基礎知識——字符和位元組
位元組：表示數據量的多少，是計算機信息技術用於計量存儲容量的一種計量單位；
字符：是指計算機中使用的文字和符號。
注意：一般在英文狀態下一個字母或字符占用一個位元組，一個漢字用兩個位元組表示。
一、LEFT和RIGHT
1.1提取混合內容中的姓名
要求：A列是姓名和學號，需要將姓名提取到B列。
方法：在B3單元格輸入公式：=LEFT(A3,LENB(A3)-LEN(A3))，向下複製填充公式。
解讀：LENB(A3)-LEN(A3)表示用LENB計算出位元組數，用LEN函數計算出字符數後，兩者相減得出文本中雙位元組（即漢字）的字符長度。最後，再用LEFT函數從左邊提取雙位元組的文本。
掃碼群里下載練習課件
1.2將編號變成「0000」形式
要求：將E列的編號設置成「0000」格式。
方法：在F3單元格輸入公式：=RIGHT("000"&E3,4)，向下複製填充公式。
解讀：因為最終編號限制在四位數，少於四位用「0」在前面補位，所以可以在每個編號前面加上「000」（也可以加上四個0），再用LEFT函數從右邊提取最後四位數。
補充：將編號設置成「0000」格式，也可以用自定義格式，但上述方法也是很機智的哦~~~
1.3提取字符串左側的連續數字
要求：提取出A列右側的連續數字到B列。
方法：在B12單元格輸入公式：=LOOKUP(9E+307,--RIGHT(A12,ROW($1:$10)))，向下複製填充公式。
解讀：先使用RIGHT函數從A12單元格右側，截取長度為1~10的文本字符串，再使用減負運算將文本數字轉換為可運算的數值，將文本內容（漢字、英文、標點）轉換為錯誤值「#VALUE！」。最後使用VLOOKUP函數，以9E+307作為查找值，在由錯誤值和數值組成的內存數組中，提取最後一個數值。
注意：9E+307是使用科學記數法表示的9*10^307，近似Excel允許輸入的最大值。
1.4姓氏+先生/女士
①無複姓
要求：在C列設置尊稱為「姓氏+先生/女士」（無複姓）
方法：在C19單元格輸入公式：=LEFT(A19,1)&IF(B19="女","女士","先生")，向下複製填充公式。
解讀：用LEFT函數從左邊提出字符長度為1的姓氏。再利用IF函數判斷，如果性別為「女」則加上「女士」；反之，則加上「先生」。
②有複姓
要求：在H列設置尊稱為「姓氏+先生/女士」（有複姓）
方法：在H19單元格輸入公式：=LEFT(E19,OR(LEFT(E19,{1,2})=$E$30:$E$32)+1)&IF(F19="男","先生","女士")，按<Ctrl+Shift+Enter>三鍵結束，向下複製填充公式。
解讀：
LEFT(E19,{1,2})部分，在姓名中用LEFT函數從左邊分別提取1個和2個文本字符串，以內存數組的形式保存在公式中。（筆者把結果展示在了G列）
LEFT(E19,{1,2})=$E$30:$E$32部分，將提取出的內存文字與$E$30:$E$32複姓區域分別進行匹配，如果有一個提取出的內存文字能夠與某個複姓匹配，則在OR函數的外層嵌套下為TURE，反之為則FALSE。再在最後+1，讓單姓顯示為1，複姓顯示為2.
LEFT(E19,OR(LEFT(E19,{1,2})=$E$30:$E$32)+1)，若有複姓LEFT函數提取2個文本字符串，若沒有複姓LEFT函數提取1個文本字符串。（結果如圖I列）
二、LEN和LENB
2.1分離出中文
方法：在B3單元格輸入公式：=RIGHT(A3,LENB(A3)-LEN(A3))，向下複製填充公式。
2.2分離出英文
方法：在C3單元格輸入公式：=LEFT(A3,2*LEN(A3)-LENB(A3))，再向下複製填充公式。
解讀：LENB函數將每個漢字（雙位元組字符）的字符數按2計數，LEN函數則對所有的字符數都按1計算。所以，「LENB(A3)-LEN(A3)」返回的結果就是文本字符串中的漢字個數；「LENB(A3)-LEN(A3)」返回的結果就是文本字符串中的英文字母（單位元組字符）個數。
三、EXACT函數
EXACT函數，用來判斷兩個單元格的內容是否完全相同，如果文本字符串完全相同，那麼函數的返回TRUE;如果不完全相同，那麼返回FALSE。
EXACT函數的語法：
EXACT（text1，text2）
注意：在excel中使用等號比較文本值時不區分字母大小寫，而用EXACT可以區分字母大小寫！
如圖，用EXACT函數比較「A」和「a」結果顯示FALSE，用等於號（=）連接結果則顯示TRUE。
補充：EXACT函數的參數還可以是單元格區域引用。
3.1一對多的比較
第一個參數是一個單元格引用；第二個參數是多個單元格區域引用。
如圖，在C8單元格輸入公式：=EXACT(B8,A8:A12)，並向下複製填充。則EXACT函數將返回B8與A8:A12單元格區域每個元素比較的結果。
3.2多對多的比較
如圖，在H8單元格輸入公式：=EXACT(F8:F12,G8:G12)，並向下複製填充。則兩個參數的每一個元素會分別進行比較。
今天的文本函數典型案例就分享到這裡咯，如果對你有幫助，還請多多支持喲^0^~~
掃一掃添加老師微信
掃一掃，在線諮詢Excel課程
Excel教程相關推薦
為什麼說勸你儘早學會這些基礎邏輯函數？這是我見過最好的答案！
如果連這個最基礎的求和統計表都搞不定，就不要抱怨工資低了……「我面試了幾十個大學生，發現他們竟然還在用分列法拆分數據，難怪效率那麼低……」
入職HR第一天，新同事就因為一張不合格的Excel員工檔案表，氣得老闆差點開了他……
想要全面系統學習Excel，不妨
收藏