2.2.2 靈活的文本處理函數

文本處理函數用來對文本字符串進行處理，此類函數可以幫助用戶對文本字符串進行一系列嵌套處理，最終獲取想要的字符。常見的文本處理函數包括LEN、LENB、LEFT、LEFTB、RIGHT、RIGHTB、MID、MIDB、UPPER、LOWER、SEARCH、SEARCHB、FIND、FINDB、REPLACE、REPLACEB、SUBSTITUTE、SUBSTITUTEB、TRIM、CONCATENATE、EXACT等。工作中經常用到的文本處理函數場景包括截取字符串中的部分字符串、拼接多個字符串生成一個字符串、查找字符串在另一個字符串中的位置、替換字符串中的舊字符串為新字符串。下面通過實例分別對常用的文本處理函數進行舉例說明。

1.LEN、LENB函數

功能說明：

● LEN返回文本字符串中的字符個數。

● LENB返回文本字符串中用于代表字符的字節數。

語法：

● LEN(text)

● LENB(text)

參數：text必需。要查找其字符個數或字節數的文本。

示例：統計單元格A2內字符串“上海A廣州B”里面的中文字符個數與英文字符個數。

公式與步驟：

● 中文字符個數：單元格E2內輸入公式“=LENB(A2) -LEN(A2)”。

● 英文字符個數：單元格E3內輸入公式“=2*LEN(A2) - LENB(A2)”，結果如圖2-35所示。

提示：

● LEN函數統計的是字符的個數，相當于1*中文字符個數+1*英文字符個數（或數字個數）。LENB函數統計的是字節數，相當于2*中文字符個數+1*英文字符個數（或數字個數）。

● 這兩個函數可能并不適用于所有語言。

● 只有在將DBCS語言設置為默認語言時，函數LENB才會將每個中文字符按兩個字節計數。否則，函數LENB與LEN相同，即將每個字符按1個字節計數。支持DBCS的語言包括日語、中文（簡體）、中文（繁體）以及朝鮮語。

2.LEFT函數

功能說明：LEFT從文本字符串的第一個字符開始返回指定個數的字符。

語法：LEFT(text,[num_chars])

參數：

● text必需。要提取字符的文本字符串。

● num_chars可選。LEFT函數指定提取的字符數量。如果省略，默認為1。

示例：截取單元格A2內字符串“上海A廣州B”的前3個字符。

公式與步驟：單元格E2內輸入公式“=LEFT(A2,3)”，結果如圖2-36所示。

3.RIGHT函數

功能說明：RIGHT根據所指定的字符數返回文本字符串中最后一個或多個字符。

語法：RIGHT(text,[num_chars]

參數：

● text必需。要提取字符的文本字符串。

● num_chars可選。RIGHT函數指定提取的字符數量。如果省略，默認為1。

示例：截取單元格A2內字符串“上海A廣州B”的最后3個字符。

公式與步驟：單元格E2內輸入公式“=RIGHT(A2,3)”，結果如圖2-37所示。

4.MID函數

功能說明：MID返回文本字符串中從指定位置開始的特定數目的字符，數目由用戶指定。

語法：MID(text,start_num,num_chars)

參數：

● text必需。要提取字符的文本字符串。

● start_num必需。文本中要提取字符串的起始位置。

● num_chars必需。指定MID函數在文本中截取的字符個數。

示例：截取單元格A2內字符串“上海A廣州B”中“廣州”這兩個字符。

公式與步驟：單元格E2內輸入公式“=MID(A2,4,2)”，結果如圖2-38所示。

5.UPPER、LOWER函數

功能說明：UPPER將文本轉換為大寫字母，LOWER將文本轉換為小寫字母。

語法：

● UPPER(text)

● LOWER(text)

參數：text必需。要轉換為大寫或小寫字母的文本。

示例：分別將字符串“Data Analysis”中的文本轉換為大寫字母或小寫字母。

公式與步驟：

● 轉換為大寫字母：單元格E2內輸入公式“=UPPER(A2)”。

● 轉換為小寫字母：單元格E3內輸入公式“=LOWER(A2)”，結果如圖2-39所示。

6.FIND函數

功能說明：用于在第二個文本串中定位第一個文本串，并返回第一個文本串的起始位置的值，該值從第二個文本串的第一個字符算起。

語法：FIND(find_text,within_text,[start_num])

參數：

● find_text必需。要查找的文本。

● within_text必需。包含要查找文本的文本。

● start_num可選。指定開始進行查找的字符的位置。如果省略，默認為1。

示例：找出字符串“我Love數據Analysis”中“數據”、大寫“L”、小寫“l”的位置。

公式與步驟：

● 字符串“數據”的位置：單元格E2內輸入公式“=FIND("數據",A2,1)”。

● 字符“L”的位置：單元格E3內輸入公式“=FIND("L",A2,1)”。

● 字符“l”的位置：單元格E4內輸入公式“=FIND("l",A2,1)”，結果如圖2-40所示。

提示：

● FIND函數的最后一個參數start_num可以省略，如果省略，默認從第一個字符開始查找。

● FIND函數區分大小寫，因此從第一個字符開始查找大寫“L”的位置是2，從第一個字符開始查找小寫“l”的位置是11。

● FIND函數中的find_text參數不能包含通配符（“?”或“*”）。

7.SEARCH函數

功能說明：SEARCH函數可在第二個文本字符串中查找第一個文本字符串，并返回第一個文本字符串的起始位置的編號，該編號從第二個文本字符串的第一個字符算起。

語法：SEARCH(find_text,within_text,[start_num])

參數：

● find_text必需。要查找的文本。

● within_text必需。包含要查找文本的文本。

● start_num可選。指定開始進行查找的字符的位置。如果省略，默認為1。

示例：找出字符串“我Love數據Analysis”中“數據”、大寫“L”、小寫“l”以及“*數據*”的位置。

公式與步驟：

● 字符串“數據”的位置：單元格E2內輸入公式“=SEARCH("數據",A2,1)”。

● 字符大寫“L”的位置：單元格E3內輸入公式“=SEARCH("L",A2,1)”。

● 字符小寫“l”的位置：單元格E4內輸入公式“=SEARCH("l",A2,1)”。

● 字符串“＊數據＊”的位置：單元格E5內輸入公式“=SEARCH("*數據*",A2,1)”，結果如圖2-41所示。

提示：

● SEARCH函數不區分大小寫，從第一個字符開始查找大寫“L”的位置是2，從第一個字符開始查找小寫“l”的位置也是2。

● SEARCH函數中的find_text參數可以包含通配符（“?”或“*”）。查找“*數據*”字符串的位置，由于通配符“*”匹配的是任意字符，因此“*數據*”匹配的是整個字符串“我Love數據Analysis”。返回的結果是從第一個字符開始查找，字符串“我Love數據Analysis”在字符串“我Love數據Analysis”里面的位置，就是查找字符串的首個字符“我”的位置，最終返回1。

8.SUBSTITUTE函數

功能說明：用于在某一文本字符串中替換指定的文本，把old_text替換成new_text。

語法：SUBSTITUTE(text,old_text,new_text,[instance_num])

參數：

● text必需。要替換其中字符的文本。

● old_text必需。要替換的文本。

● new_text必需。替換old_text的文本。

● instance_num可選。指定要用new_text替換old_text的事件。如果指定了instance_num，只有滿足要求的old_text被替換。如果省略，文本中所有的old_text都會被替換為new_text。

示例：分別將字符串“#我愛數據#數據愛我#”中的第一個“#”替換為“@”、第二個“#”替換為“@”、所有的“#”替換為“@”、前兩個“#”都替換為“@”。

公式與步驟：

● 替換第一個"#"為"@"：單元格D2內輸入公式“=SUBSTITUTE(A2,"#","@",1)”。

● 替換第二個"#"為"@"：單元格D3內輸入公式“=SUBSTITUTE(A2,"#","@",2)”。

● 替換所有的"#"為"@"：單元格D4內輸入公式“=SUBSTITUTE(A2,"#","@")”。

● 替換前二個"#"為"@"：單元格D5內輸入公式“=SUBSTITUTE(SUBSTITUTE(A2,"#","@",1),"#","@",1)”，結果如圖2-42所示。

提示：

● SUBSTITUTE函數里面的參數instance_num可以省略，如果省略表示替換所有的old_text，如果instance_num=2，表示替換第二次出現的字符串。

● 如需分別替換字符串為其他不同字符串，可以使用SUBSTITUTE函數進行嵌套替換，每次替換第一次出現的字符串即可。

9.REPLACE函數

功能說明：根據指定字符數，REPLACE將部分文本字符串替換為不同的文本字符串。

語法：REPLACE(old_text,start_num, num_chars,new_text)

參數：

● old_text必需。要替換其中字符的文本。

● start_num必需。old_text中要替換為new_text的字符起始位置。

● num_chars必需。使用new_text來進行替換的字符數。

● new_text必需。替換old_text中字符的文本。

示例：

● 將字符串“#我愛數據#數據愛我#”中的“我愛數據”替換為“Data”。

● 將手機號碼“13013013000”中間的五位數字替換為“*****”。

公式與步驟：

● 替換“我愛數據”為“Data”：單元格D2內輸入公式“=REPLACE(A2,2,4,"Data")”。

● 替換“13013013000”中間5位為“＊＊＊＊＊”：單元格D3內輸入公式“=REPLACE (A3,4,5,"*****")”，結果如圖2-43所示。

提示：

● REPLACE函數與SUBSTITUTE函數的區別：REPLACE函數是指定起始位置和字符長度進行替換的；而SUBSTITUTE函數是將給定的原始字符串替換成新的字符串。

● REPLACE函數與上面提到的MID函數也有相似之處：MID函數是根據起始位置和字符長度來進行截取；而REPLACE函數除了截取之外，還要將截取的字符串替換掉。

10.CONCATENATE函數

功能說明：將兩個或多個字符串連接為一個字符串。

語法：CONCATENATE(text1,[text2], …)

參數：

● text1必需。要連接的第一個字符串。

● text2, … 可選。要連接的其他字符串。

示例：將“我”“愛”“數據分析”這三個字符串合并成一個字符串。

公式與步驟：

● 方法一：單元格E2內輸入公式“=CONCATENATE(A2,A3,A4)”。

● 方法二：單元格E3內輸入公式“=A2&A3&A4”，結果如圖2-44所示。提示：

● CONCATENATE函數的功能等價于字符“&”的功能，用“&”符號也可以將單元格A2、A3、A4的內容合并成一個字符串。

● 在很多情況下，使用“&”符號比使用CONCATENATE函數連接字符串簡單高效。

11.EXACT函數

功能說明：比較兩個文本字符串，如果它們完全相同，則返回TRUE，否則返回FALSE。

語法：EXACT(text1, text2)

參數：

● text1必需。要比較的第一個文本。

● text2必需。要比較的第二個文本。

示例：分別比較單元格A2與A3、A2與A4的字符串是否相同。

公式與步驟：

● 比較A2與A3是否相同：單元格E2內輸入公式“=EXACT(A2,A3)”。

● 比較A2與A4是否相同：單元格E3內輸入公式“=EXACT(A2,A4)”，結果如圖2-45所示。

提示：

● EXACT函數區分大小寫。

● 符號“=”不能區分大小寫。例如，公式“=("A"="a")”，結果返回TRUE，不是FALSE。

12.TRIM函數

功能說明：除了單詞之間的單個空格之外，移除文本中的所有空格。語法：TRIM(text)

參數：text必需。要從中移除空格的文本。

示例：用TRIM去除字符串“ Data Analysis ”中的空格。

公式與步驟：單元格E2內輸入公式“=TRIM(A2)”，結果如圖2-46所示。

提示：

● 字符串前后兩端的空格全部去除。

● 字符串中單詞之間的空格保留一個。

以上是對文本處理函數使用的介紹，并通過示例對函數進行了功能講解，下面的文本處理函數案例一～案例三是文本處理函數的應用擴展。對于同一個案例采用了多種方法來解決。

13.文本處理函數案例一

本示例將如圖2-47所示的數據表中的文件名稱按照符號“.”進行拆分，分別取左側的文件名稱（不含后綴）和右側的文件后綴。

公式與步驟：

● 截取符號“.”左側（方法一）：單元格B3內輸入公式“=LEFT(A3,FIND(".",A3)-1)”，然后向下拖拽復制公式。

● 截取符號“.”左側（方法二）：單元格C3內輸入公式“=MID(A3,1,FIND(".",A3)-1)”，然后向下拖拽復制公式。

● 截取符號“.”右側（方法一）：單元格D3內輸入公式“=RIGHT(A3,LEN(A3)-FIND(".",A3))”，然后向下拖拽復制公式。

● 截取符號“.”右側（方法二）：單元格E3內輸入公式“=MID(A3,FIND(".",A3)+1,100)”，然后向下拖拽復制公式，結果如圖2-47所示。

提示：

● 符號“.”左側字符串的長度等于符號“.”位置的值減去數值1。

● 符號“.”右側字符串的長度等于字符串的字符個數減去符號“.”位置的值。

14.文本處理函數案例二

本示例要從一組雜亂的文本字符串中提取中文信息。城市代碼“S上海4”中包含了中文、大寫英文和數字，需要從中提取城市“上海”，數據如表2-3所示。

公式與步驟：

● 方法一：單元格E2內輸入公式“=LEFT (SUBSTITUTE(A2,"S",""),LENB(A2)-LEN(A2))”，然后向下拖拽復制公式。

● 方法二：單元格F2內輸入公式“=LEFT(MID(A2,2,100),LENB(A2)-LEN(A2))”，然后向下拖拽復制公式。

● 方法三：單元格G2里面輸入中文字符串“上海”，然后選中區域G2:G5，同時按組合鍵〈Ctrl+E〉，完成快速填充功能，結果如圖2-48所示。

提示：

● 方法一用SUBSTITUTE函數將字符串里面的字符“S”替換成空，然后通過LEFT函數截取左邊中文字符（中文字符的長度根據LENB和LEN函數的組合運算得出）。

● 方法二用MID函數截取字符“S”右側的所有字符串，后面的嵌套處理與方法一相同。

● 方法三的快速填充功能（Ctrl+E）是根據提供的截取樣式進行快速填充，用這個方法從字符串里截取中文效果非常好。

15.文本處理函數案例三

本示例：對一組字符串進行相關統計，字符串數據及對字符串的處理需求如表2-4所示。

公式與步驟：

1）計算包含字母a的字符串個數（不區分大小寫）：

● 方法一：單元格D2內輸入公式“=COUNTIFS(A:A,"*A*")”。

● 方法二：單元格E2內輸入數組公式“{=SUM(IFERROR(SEARCH("*a*",A2:A6), 0))}”。

2）計算包含字母A的字符串個數（區分大小寫）：單元格D3內輸入數組公式“{=SUM (IF(IFERROR(FIND("A",A2:A6),0)＞=1,1,0))}”。

3）計算首字母是a的字符串個數（不區分大小寫）：

● 方法一：單元格D4內輸入數組公式“{=SUM((LEFT(A2:A6,1)="a")+0)}”。

● 方法二：單元格E4內輸入數組公式“{=SUM(IFERROR(SEARCH("a",LEFT(A2:A6,1)),0))}”。

4）計算首字母是A的字符串個數（區分大小寫）：

● 方法一：單元格D5內輸入數組公式“{=SUM(EXACT(LEFT(A2:A6,1),"A")+0)}”。

● 方法二：單元格E5內輸入數組公式“{=SUM(IFERROR(FIND("A",LEFT(A2:A6,1)), 0))}”，結果如圖2-49所示。

提示：

● FIND函數與EXACT函數區分英文字母大小寫，如果統計大寫或小寫字母的個數，可以用這兩個函數來進行統計。

● SEARCH函數不區分字母大小寫，COUNTIFS函數也不區分字母大小寫。

● 符號“=”不區分字母大小寫。例如，公式“=("A"="a")”，結果返回TRUE。