1、16個Excel文本函數(shù)，這篇文章教你全弄清楚！利用Excel函數(shù)解決文本處理問題，是最常見不過的任務(wù)啦比如下面這些，你有沒有遇到過？合并兩個單元格的內(nèi)容？把省市區(qū)分拆？提取身份證號碼中的出生日期？批量替換數(shù)據(jù)的單位？在我眼里，每個文本函數(shù)都是一個詩人接下來就帶你領(lǐng)略，各種文本函數(shù)的風騷之處。一共分為六個部分，文本合并、文字提取、字符清洗、文字替換、精確查找、長度計算?？傆心阋蒙系?！1 / 38文本合并合并和提取是文本處理中最最常見的任務(wù)。例如，將下表中各個單元格的文字合成1個新的句子：而利用不同的函數(shù)公式，操作方法和結(jié)果都有所不同。&連字符&能夠直接將一個一個的文本連接起來

2、，形成一個新的文本：公式：=A1&B1&C1結(jié)果：愛老虎油然而，&連字符先天殘疾，有很多個字符時，也不能直接引用整片區(qū)域進行合并，依然只能一個個手工添加。操作過程如下：Phonetic 函數(shù)用Phonetic就省事很多，它可以引用一個區(qū)域（只能一個），將區(qū)域內(nèi)所有單元格的文本型數(shù)據(jù)拼合在一起。例如將下表中的全部文本拼合起來：可以使用如下公式公式：=phonetic(A1:D1)結(jié)果：愛老虎油但是明明D1里有一個數(shù)字1314，為什么沒出現(xiàn)在結(jié)果里呢？因為，phonetic只是個兼職合并函數(shù)而已。它本職工作是提取日文拼音，是日文版來客串一下的，人家只是恰好可以合并文字而已。

3、這個客串函數(shù)個性非常獨特，因為它眼里只有文本型數(shù)據(jù)，對公式結(jié)果、數(shù)值、日期時間等等通通視而不見。所以1314就是被狗吃掉了此公式的輸入就比&連字符簡單多了：Concat 函數(shù)而Concat就比&和phonetic更加完美，因為它既能引用一個區(qū)域直接合并，又不會漏掉數(shù)值、日期和公式結(jié)果，還能引用多個區(qū)域，簡直不能更完美了。公式：=concat(A1:D1)結(jié)果：愛老虎油1314不過，很遺憾，完美版本的Concat只有2016版才有。在之前的版本中，它有個前身，叫 Concatenate。Concatenate和&公式一個德性，只能逐個添加。名稱又太長，還不如用&來

4、得方便。估計是Office工程師自己都忍不了了，才在2016版添加了一個加強版吧。這個加強版可棒了：以上三種合并函數(shù)，分別合成的結(jié)果放在一起，結(jié)果就是醬紫的： 一個宅男表哥用公式向喜歡的妹子發(fā)出三行愛的告白。結(jié)果，這個妹子是個表妹，迅速用下面這個函數(shù)做出了回應(yīng)Textjoin 函數(shù)用分隔符將多個字符串聯(lián)起來。公式：textjoin(“！”,True,A1:D1)&”！”結(jié)果：你！是！個！好人！于是，此公式的含義為，將A1：D1中每個單元格的內(nèi)容用嘆號串聯(lián)起來，如果有空格則忽略，最后缺少的嘆號用&單獨補一個。因為有函數(shù)輔助輸入工具條，寫這個公式也不難：有一個神奇的函數(shù)，

5、可以按照指定的次數(shù)，將自己反復拼合起來，俗稱自擼。啊，不，是重復。16個Excel文本函數(shù)，這篇文章教你全弄清楚！2016-09-13  過河卒沖  摘自 幻方秋葉PPT  閱 989  轉(zhuǎn) 49 轉(zhuǎn)藏到我的圖書館微信 分享：利用Excel函數(shù)解決文本處理問題，是最常見不過的任務(wù)啦比如下面這些，你有沒有遇到過？合并兩個單元格的內(nèi)容？把省市區(qū)分拆？提取身份證號碼中的出生日期？批量替換數(shù)據(jù)的單位？在我眼里，每個文本函數(shù)都是一個詩人接下來就帶你領(lǐng)略，各種文本函數(shù)的風騷之處。一共分為六個部分，文本合并、文字提取、

6、字符清洗、文字替換、精確查找、長度計算?？傆心阋蒙系?！文本合并合并和提取是文本處理中最最常見的任務(wù)。例如，將下表中各個單元格的文字合成1個新的句子：而利用不同的函數(shù)公式，操作方法和結(jié)果都有所不同。&連字符&能夠直接將一個一個的文本連接起來，形成一個新的文本：公式：=A1&B1&C1結(jié)果：愛老虎油然而，&連字符先天殘疾，有很多個字符時，也不能直接引用整片區(qū)域進行合并，依然只能一個個手工添加。操作過程如下：Phonetic 函數(shù)用Phonetic就省事很多，它可以引用一個區(qū)域（只能一個），將區(qū)域內(nèi)所有單元格的文本型數(shù)據(jù)拼合在一起。例如將下表中的全部文本拼合

7、起來：可以使用如下公式公式：=phonetic(A1:D1)結(jié)果：愛老虎油但是明明D1里有一個數(shù)字1314，為什么沒出現(xiàn)在結(jié)果里呢？因為，phonetic只是個兼職合并函數(shù)而已。它本職工作是提取日文拼音，是日文版來客串一下的，人家只是恰好可以合并文字而已。這個客串函數(shù)個性非常獨特，因為它眼里只有文本型數(shù)據(jù)，對公式結(jié)果、數(shù)值、日期時間等等通通視而不見。所以1314就是被狗吃掉了此公式的輸入就比&連字符簡單多了：Concat 函數(shù)而Concat就比&和phonetic更加完美，因為它既能引用一個區(qū)域直接合并，又不會漏掉數(shù)值、日期和公式結(jié)果，還能引用多個區(qū)域，簡直不能更完美了。公式：

8、=concat(A1:D1)結(jié)果：愛老虎油1314不過，很遺憾，完美版本的Concat只有2016版才有。在之前的版本中，它有個前身，叫 Concatenate。Concatenate和&公式一個德性，只能逐個添加。名稱又太長，還不如用&來得方便。估計是Office工程師自己都忍不了了，才在2016版添加了一個加強版吧。這個加強版可棒了：以上三種合并函數(shù)，分別合成的結(jié)果放在一起，結(jié)果就是醬紫的： 一個宅男表哥用公式向喜歡的妹子發(fā)出三行愛的告白。結(jié)果，這個妹子是個表妹，迅速用下面這個函數(shù)做出了回應(yīng)Textjoin 函數(shù)用分隔符將多個字符串聯(lián)起來。公式：textjoin(

9、“！”,True,A1:D1)&”！”結(jié)果：你！是！個！好人！于是，此公式的含義為，將A1：D1中每個單元格的內(nèi)容用嘆號串聯(lián)起來，如果有空格則忽略，最后缺少的嘆號用&單獨補一個。因為有函數(shù)輔助輸入工具條，寫這個公式也不難：有一個神奇的函數(shù)，可以按照指定的次數(shù)，將自己反復拼合起來，俗稱自擼。啊，不，是重復。Rept 函數(shù)它的語法是Rept(字符串,重復次數(shù))。好想讓妹子用Rept再來一次暴擊：公式：rept(A1,3)結(jié)果：你是個好人！你是個好人！你是個好人！公式含義再明顯不過了。好喜歡Rept函數(shù)輸入后按下Enter鍵的感覺：文字提取提取型文本函數(shù)，就是從1個文本中挑出一部分。

10、常用的提取函數(shù)就有Left、Right、Mid（middle的簡寫）三種。Left 函數(shù)從左邊開始算起，提取若干字符。例如從下面詩句提取左邊的7個字符：公式：=Left(A1,7)結(jié)果：一江春水向東流Right 函數(shù)而提取右邊的7個字符，就要用函數(shù)Right：公式：=Right(A1,7)結(jié)果：我也沒有女盆友Left 和 Right 函數(shù)語法結(jié)構(gòu)相同：函數(shù)名(字符串,提取數(shù)量)。只是一左一右，提取的方向不一樣。但是第三個函數(shù)Mid就厲害得多，它能夠從中間指定的位置開始提取。Mid 函數(shù)基本語法是 Mid(字符串,起始位置,提取數(shù)量)例如，從下面詩句中提取清淚兩字，就可以從第11個字符開始提取2

11、個字符（逗號也算一個）。公式：=Mid(A3,11,2)&Right(A3,1)結(jié)果：清淚流注意到了嘛，上面的公式還用到了連字符&，將兩個公式計算的結(jié)果拼在一起，得到了最終結(jié)果。字符清洗從網(wǎng)頁上或神隊友那弄來的數(shù)據(jù)，可能會有莫名其妙的空格和換行符。這些字符雖然看不見，但卻會導致公式計算結(jié)果出錯。所以通常會用Clean或Trim兩個函數(shù)進行清洗。他們語法也一樣，都是 函數(shù)名(字符串)。但功能上卻有細微的差別：Clean 函數(shù)字面意思是清潔，它只清除換行符等看不見的非打印字符，卻無法去除空格。Trim 函數(shù)字面意思是修剪，它會裁頭去尾，將前后的空格以及文本內(nèi)部多余的空格全

12、部清除，但是按英文使用習慣，英文中間會自動保留一個空格字符作間隔。例如，A1單元格中的字符串有很多多余的空格及2個換行符。分別用Clean和Trim函數(shù)處理的結(jié)果如下：文字替換Replace和Substitue這哥倆的能力是”置換“。功能類似，但是定位的方法不同。Substitute 函數(shù)Substitute是以字符定位字符。例如，找出詩句中的“船”字，然后替換成“床”，公式和結(jié)果如下：公式：=Substitute(A1,”船”,”床”)結(jié)果：百年修得同床度，霉霉三月又分手Replace 函數(shù)而Replace則是以位置定位字符。例如，從第13個字符開始提取1個字符“漢“，然后把”漢“替換成新的

13、字符“妹“，公式和結(jié)果如下：公式：=Replace(A1,13,1,”妹”)結(jié)果：此情可待成追憶，我要做撩妹高手感覺自己是在冒著跪榴蓮的風險在寫這篇推送替換函數(shù)還有一項獨門秘技：把替換為的字符寫成空值(“”)，替換就變成了刪除。看哪個字符不順眼，一言不合就可以讓它消失。精確查找Find和Search，都能掘地三尺，精準定位某個字符在文本中的具體位置。Find 函數(shù)公式：=Find('King',A1)結(jié)果：2計算結(jié)果說明King在整句中是從第2個字符開始出現(xiàn)的。Search 函數(shù)公式：=Search('洪荒',A1)結(jié)果：6結(jié)果表明，“洪荒”在整句中是從第6個字

14、符開始的。雖然 Find 和 Search 都是返回某個字符在字符串中的位置信息，結(jié)果都是一個數(shù)值。但還是有細微差別，Search 可以用通配符模糊查找。例如，“K?ng”就能把 King、Kong、Kang 都找出來，而Find卻做不到。但是，計算返回結(jié)果是一個位置數(shù)值，有用嗎？接著往下看，你就知道可以怎么用！她是怎么知道的？長度計算Excel中有兩把度量文本長度的尺子：Len 函數(shù) 和 LenB 函數(shù)Len 函數(shù)不管中文還是英文、數(shù)字，Len都將每個字符算作1。 公式：=Len(A1)結(jié)果：15兩句七律加一個符號，總共15個字符。LenB 函數(shù)LenB后面多出來的那個B是Byte

15、(字節(jié)) 的意思，是按字節(jié)來算。1個漢字及中文標點都是雙字節(jié)，長度都是2。但英文字母和數(shù)字通常都是1個字節(jié)，長度只有1。于是，同樣是14個漢字和一個標點的詩句，用LenB的計算結(jié)果不一樣。公式：=LenB(A1)結(jié)果：30LenB 和 Len之間的差異，常常用來處理中英文混合的情況，比如公式：=LenB(“King是好人”)-Len(“King是好人”)結(jié)果：3由于漢字LenB計算出來的長度是Len的兩倍，但英文字母長度相等，兩個公式之差，就是漢字的個數(shù)。以上結(jié)果正說明字符串中有3個漢字。如果再配合Right函數(shù)，就可以把中文單獨提取出來。Text 格式轉(zhuǎn)換函數(shù)text函數(shù)能夠讓單元格數(shù)值按照

16、指定的格式顯示出來，例如固定顯示成4位數(shù)，不足就補0的處理方法如下：公式：=TEXT(A1,'0000')結(jié)果：0069以上就是常用的文本函數(shù)用法，然而很多人可能會有這樣的疑問?！昂喜⒑吞崛∮迷谀暮芎美斫猓墒遣檎乙粋€字符并返回位置信息，到底有什么用？”有些函數(shù)計算結(jié)果自身并沒有多大意義，卻能夠輔助完成其他任務(wù)。比如Len先求出長度后，就可以按照長度來排個名次更厲害的是，計算結(jié)果還可以作為其他函數(shù)的參數(shù)啊。比如要把下圖中每一個數(shù)字提取出來變成 QiuYeXXXX的格式，只用一個函數(shù)，是辦不到的。但是多個函數(shù)組合起來使用，上萬行的數(shù)據(jù)也能雙擊搞定，只需要花幾秒鐘時間寫一個公式就夠

17、了：圖中演示的公式就綜合運用了4種文本函數(shù)，逐步求值的結(jié)果如下圖：文本函數(shù)遠不止這些，但常用的就是下面這16個啦！其他函數(shù)，需要用時再找吧我真的不會去背這些函數(shù)，但是會根據(jù)他們能解決的問題進行觀察和梳理，留意他們的特點。然后在看到其他案例時，自己再去了解一番同時加深理解。函數(shù)看起來枯燥乏味，但只要摸清脈絡(luò)，就會變得很好玩。想當初愛上Excel，也就是純粹覺得好玩。而文本函數(shù)中最喜歡的，就是Rept了。因為它可以完美表達心情：Rept(“你是個好人！”,6)Rept('?',9)常用文本函數(shù)及應(yīng)用實例一、什么是文本：在excel中，除數(shù)值、日期時間、邏輯值和錯誤值以外的所有值。一

18、個文本值由N個（0<=N<=32767）個字符組成，也叫字符串。所謂字符串，是計算機可識別的單個符號，分為單字節(jié)字符和雙字節(jié)字符。所謂字節(jié)。字節(jié)(Byte)是計算機信息技術(shù)用于計量存儲容量和傳輸容量的一種計量單位。字符好比人，字節(jié)好比房子,中文和中文的標點符號及全角字符都屬于雙字節(jié)字符，普通數(shù)字及英文字母，屬于單字節(jié)字符。單字節(jié)字符屬于一個人住一套房子，而雙字節(jié)字符是一個富二代住兩套房子。二、excel文本函數(shù)范圍在Excel 2003中有34個標準文本函數(shù)及很多非標準文本函數(shù)。主要包括len mid left right find search substitude replac

19、e trim clean rept 及這些函數(shù)后帶b的函數(shù)。1、len，返回文本字符串中的字符數(shù)；lenb，返回文本字符串中用于代表字符的字節(jié)數(shù)。Len相當于返回人數(shù)，而lenb相當于返回房子數(shù)。語法，lenb(text)、lenb(text)。Text是要查找的其長度的文本，空格及控制符均作為字符進行計數(shù)。函數(shù)返回值：數(shù)值（整數(shù)）。以下例子將探討不同參數(shù)的下的字符及字節(jié)長度  A、其中的時間格式下的，字符長度是轉(zhuǎn)換成數(shù)字后計算。如 2010-1-31，其轉(zhuǎn)換為數(shù)字后是40209，其長度是5。B、時間參數(shù)09:25，轉(zhuǎn)化為數(shù)字格式后是0.392361111111111，其長度就變?yōu)?

20、7了。C、邏輯值TRUE是按照單詞字符計算的。現(xiàn)在發(fā)現(xiàn)，時間和日期參數(shù)，其字符長度有很強的隱蔽性，當將其轉(zhuǎn)換成數(shù)字時，就發(fā)現(xiàn)廬山真面目了。就像現(xiàn)在的貪官，表面廉潔奉公，其實內(nèi)在的壞水很長很長的。TEXT參數(shù)的特性：1、文本值，按文本本身進行計算。2，數(shù)值，轉(zhuǎn)換成數(shù)字型文本計算。3，日期、時間，按轉(zhuǎn)化為數(shù)值后的文本計算。4邏輯值，按轉(zhuǎn)換后的TRUE、FALSE進行計算。5、查看轉(zhuǎn)換結(jié)果，A1&”.從以上特性來看，text除了錯誤值以外，可謂參數(shù)包羅萬象。需要說明的是，Text    是要查找其長度的文本，空格及控制字符都將作為字符進行計數(shù)。具體應(yīng)用如下：如果

21、我們要計算中文個數(shù)，LEN()的一個簡單運用即可。 這里我們主要運用了，中文占據(jù)雙字節(jié)，二字母占據(jù)單字節(jié)。2、LEFT，基于所指定的字符返回文本字符串的第一個或前幾個。LEFTB，基于所指定的字節(jié)返回文本字節(jié)的第一個或前幾個。其中文含義是，左邊。主要用于提取字符左邊的字符。有點像中國一句俗話：槍打露頭鳥。LEFT函數(shù)，主要狙擊站在樹枝左邊的鳥。語法：LEFT(text,num_chars),LEFTB(text,num_bytes)Num_chars 指定所提取的字符，num_bytes 指定所提取的字節(jié)。需要說明的是：num_chars(num_bytes) 必須大于或等于 0。如

22、果 num_chars(num_bytes) 大于文本長度，則函數(shù)返回所有文本。如果省略 num_chars(num_bytes)，則假定其為 1。如果 num_chars(num_bytes) 不是整數(shù)，則按其整數(shù)部分返回結(jié)果返回結(jié)果如下表所示， RIGHT函數(shù)，和LEFT函數(shù)，區(qū)別在于從右邊還是從左邊。也就是說，RIGHT函數(shù)，主要打擊站在樹枝右邊的鳥。如表所示： NUMBER 型參數(shù)的特性：1、若是數(shù)字型文本，函數(shù)內(nèi)部會將它轉(zhuǎn)換成數(shù)值。2、若數(shù)值不是整數(shù)，先取整再計算。3、MID,返回文本字符串中從指定位置開始的特定數(shù)目的字符。MIDB，則是返回字節(jié)數(shù)。函數(shù)返回值，是

23、文本。語法：MID(text,start_num,num_chars)MIDB(text,stsrt_num,num_bytes)MID中文是中間的意思，主要提取字符中的值，但是，它的功能也涵蓋了left及right.多少也有點“越權(quán)”。O(_)O。當然，只要能為“人民辦事實”，都是好函數(shù)啊。參數(shù)說明：如果 start_num 大于文本長度，則 MID 返回空文本 ("")。如果 start_num 小于文本長度，但 start_num 加上 num_chars 超過了文本的長度，則 MID 只返回至多直到文本末尾的字符。如果 start_num 小于 1，則 MID 返回

24、錯誤值 #VALUE!。如果 num_chars 是負數(shù)，則 MID 返回錯誤值 #VALUE!。如果 num_bytes 是負數(shù)，則 MIDB 返回錯誤值 #VALUE!。示例如表：例如提取身份證信息。 在這里巧妙的利用的len（）函數(shù)，取整，及除以2.2得循環(huán)得到6和84、FIND/FINDB，在一個文本值中查找另一個文本值（區(qū)分大小寫）；SEARCH/SEZRCHB，返回從 start_num 開始首次找到特定字符或文本字符串的位置上特定字符的編號。使用 SEARCH 可確定字符或文本字符串在其他文本字符串中的位置，（不區(qū)分大小寫）。函數(shù)返回值均為數(shù)值。FIND語法：FIND(

25、find_text,within_text,start_num)FINDB(find_text,within_text,start_num)Find_text    是要查找的文本。Within_text    是包含要查找文本的文本。Start_num    指定開始進行查找的字符。within_text 中的首字符是編號為 1 的字符。如果忽略 start_num，則假設(shè)其為 1。SEARCH語法：SEARCH(find_text,within_text,start_num)SEARCHB(find_

26、text,within_text,start_num)說明：Find_text    是要查找的文本?？梢栽?find_text 中使用通配符，包括問號 (?) 和星號 (*)。問號可匹配任意的單個字符，星號可匹配任意一串字符。如果要查找真正的問號或星號，請在該字符前鍵入波形符 ()。Within_text    是要在其中查找 find_text 的文本。Start_num    是 within_text 中開始查找的字符的編號示例如下： 5、SUBSTITUTE，在文本字符串中用新文本值代

27、替舊文本值。函數(shù)返回值：文本。中國有句俗話，叫：舊貌換新顏，SUBSTITUTE可謂恰如其分。中國三十年來的改革開放展現(xiàn)的新跡象，SUBSTITUTE函數(shù)也可表達一二啊。語法：SUBSTITUTE(text,old_text,new_text,instance_num)Text    為需要替換其中字符的文本，或?qū)形谋镜膯卧竦囊谩ld_text    為需要替換的舊文本。New_text    用于替換 old_text 的文本。Instance_num    為一

28、數(shù)值，用來指定以 new_text 替換第幾次出現(xiàn)的 old_text。如果指定了 instance_num，則只有滿足要求的 old_text 被替換；否則將用 new_text 替換 Text 中出現(xiàn)的所有 old_text。示例如下 SUBSTITUTE，函數(shù)用來計算單元格數(shù)字出現(xiàn)的次數(shù)，非常巧妙。6、REPLACE，使用其他文本字符串并根據(jù)所指定的字符數(shù)替換某文本字符串中的文本；REPLACEB。函數(shù)返回值：文本語法：REPLACE(old_text,start_num,num_chars,new_text)REPLACEB(old_text,start_num,num_bytes,new_text)Old_text    是要替換其部分字符的文本。Start_num    是要用 new_text 替換的 old_text 中字符的位置。Num_chars    是希望 REPLACE 使用 new_text 替換 old_text 中字符的個數(shù)。Num_bytes    是希望 REPLACE 使用 new_text 替換 old_text