函數功能

SUBSTITUTE函數用于在文本字符串中用new_text替代old_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被替換；否則會将text中出現的每一處old_text都更改為

new_text。

實例1　去除文本中多餘的空格

如果表格中的文本輸入的不規範或者是複制的文本，有時候會存在很多空格。使用SUBSTITUTE函數可以一次性删除其中的空格，得到結構緊湊的文本内容顯示。

➊ 選中B2單元格，在公式編輯欄中輸入公式：

=SUBSTITUTE(A2," ","")

按“Enter”鍵即可返回無空格文本顯示。

➋ 将光标移到B2單元格的右下角，待光标變成十字形狀後，按住鼠标左鍵向下拖動進行公式填充，即可完成所有空格的删除，并得到正确格式顯示的文本，如圖1所示。

圖1

提示

注意第一個參數雙引号中有一個空格，第二個參數雙引号中無内容。

實例2　格式化公司名稱

在A列中顯示的是複合公司名稱，包括公司地區、名稱和代表人員。這裡可以使用SUBSTITUTE函數實現将第二個“-”連接符更改為“:”，并删除第一個連接符。

➊ 選中B2單元格，在公式編輯欄中輸入公式：

=SUBSTITUTE(REPLACE(A2,3,1,""),"-",":")

按“Enter”鍵即可替換A2單元格中的第二個連接符為“:”。

➋ 将光标移到B2單元格的右下角，待光标變成十字形狀後，按住鼠标左鍵向下拖動進行公式填充，即可完成所有連接符的替換和删除，如圖2所示。

圖2

公式解析

①使用REPLACE函數将A2單元格中的第一個“-”符号替換為空。

②使用SUBSTITUTE函數将剩下的“-”符号替換為“:”。

實例2　計算各項課程的實際參加人數

表格B列中顯示了最終報名的人員合計，C列中顯示的是原先預定的人數，要求統計出預定人數和實際人數，以便進行比較。

➊ 選中D2單元格，在公式編輯欄中輸入公式：

=LEN(B2)-LEN(SUBSTITUTE(B2,",","")) 1

按“Enter”鍵即可統計出B2單元格中最終報名人員的數量。

➋ 将光标移到D2單元格的右下角，待光标變成十字形狀後，按住鼠标左鍵向下拖動進行公式填充，即可得到所有課程的實際人數，如圖2所示。

圖2

公式解析

①用LEN函數統計出B2單元格中字符串的長度。

②将B2單元格中的逗号替換為空。

③ 統計取消了逗号後B2單元格中字符串的長度。

④ 步驟①結果與步驟③結果相減為逗号數量，逗号數量加1為姓名的數量。

提示

本例中巧妙運用了統計逗号數量的方法來變向統計人數，人數為逗号數量加1。

實例3　SUBSTITUTE函數的嵌套使用

SUBSTITUTE函數可以進行嵌套使用，例如現在需要對公司名稱進行替換，并将公司名稱中以“安徽省”、“安徽”開頭的名稱省略掉，以其他内容開頭的則保留。不論前面如何開頭，隻要最後以“有限公司”結尾的話，将“有限公司”替換成“（有）”。

➊ 選中B2單元格，在公式編輯欄中輸入公式：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"安徽省",""),"安徽",""), "有限公司","有")（按“Enter”鍵即可根據設定的條件返回替換後的名稱。

➋ 将光标移到B2單元格的右下角，待光标變成十字形狀後，按住鼠标左鍵向下拖動進行公式填充，即可返回其他公司替換後的名稱，如圖3所示。

圖3

公式解析

①用空白替換“安徽省”。

②用空白替換“安徽”。

③ 用“(有)”替換“有限公司”。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!