Excel Substitute函數使用方法,含嵌套一次替換多個不同字符實例

2021-01-10 電腦技術角

在 Excel 中,一共有兩個替換字符的函數,一個是Substitute函數,另一個是Replace函數;前者是用新字符替換舊字符,後者是用新字符替換指定字符數。Substitute函數既可以替換源文本中所有指定字符,又可以替換某個指定字符,並且還能嵌套使用以實現一次替換多個不同字符。以下是 Excel Substitute函數的使用方法,共包含5個實例,實例操作所用版本均為 Excel 2016。

一、Substitute函數語法

1、表達式:SUBSTITUTE(Text, Old_Text, New_Text, [Instance_Num])

中文表達式:Substitute(文本, 被替換文本,替換文本,[被替換文本序號])

2、說明:

Instance_Num 為可選參數,用於指定替換第幾個 Old_Text,如果省略,則替換所有 Old_Text。另外,Substitute函數不支持通配符,例如星號 "*" 不是代表任意個字符,只代表星號本身。

二、Substitute函數的使用方法及實例

(一)省略 Instance_Num 參數一次替換多個相同字符

1、假如要把「excel替換字母,數字,漢字」中的半角逗號(,)替換為全形逗號(,)。選中 B1 單元格,輸入公式 =substitute(a1,",",","),按回車,則「excel替換字母,數字,漢字」中所有半角逗號被替換為全形逗號;操作過程步驟,如圖1所示:

圖1

2、公式說明:公式 =substitute(a1,",",",") 省略了 Instance_Num 參數,則會用 New_Text(即全形逗號) 替換文本中所有 Old_Text(即半角逗號)。

(二)指定 Instance_Num 參數一次僅替換一個字符

1、假如要替換 ExcEl 2016 中第二個大寫 E 為小寫。選中 B1 單元格,把公式 =SUBSTITUTE(A1,"E","e",2) 複製到 B1,按回車,返回 Excel 2016;操作過程步驟,如圖2所示:

圖2

2、公式把 Instance_Num 參數設置為 2,即用小寫 e 替換 ExcEl 2016 中的第二個大寫 E。

(三)只替換單個數字

1、假如有一串數字 25,10,5,16,51,要把裡面的單個 5 替換掉,不能替換其它數字中的 5。選中 B1 單元格,把公式 =SUBSTITUTE(A1,",5,",",") 複製到 B1,按回車,則 5 被替換掉,操作過程步驟,如圖3所示:

圖3

2、公式說明:公式 =SUBSTITUTE(A1,",5,",",") 把 Old_Text 參數設置為 ",5,",主要是為了區別其它數字(如 25)中的 5,以避免把 25 中的 5 也替換掉;把 New_Text 設置為 ",",用於替換掉 5 後把 5 的前後數字分隔。

(四)Substitute函數嵌套使用,同時替換多個不同字符

1、替換通配符星號 * 和井號 #

A、假如要替換 25,*,10,#,51 中的 * 和 #。 選中 B1 單元格,把公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 複製到 B1,如圖4所示:

B、按回車,則 25,*,10,#,51 中 * 和 # 被替換掉,如圖5所示:

圖5

C、公式說明:公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 由兩個 Substitute函數嵌套組成;裡面的 SUBSTITUTE(A1,"~*", ",") 用於替換 *;外面的 Substitute函數用於替換 #,它的 Text 參數是替換 * 後的結果,即 SUBSTITUTE(A1,"*,", "") 返回的結果。

2、替換雙引號

A、假如要把數字串 「25」,「10」,「51」 中的雙引號去掉。選中 B1 單元格,把公式 =SUBSTITUTE(SUBSTITUTE(A1,"「", ""),"」","") 複製到 B1,按回車,則數字串中的左右雙引號全被去掉,操作過程步驟,如圖6所示:

圖6

B、公式說明:公式 =SUBSTITUTE(SUBSTITUTE(A1,"「", ""),"」","") 也由兩個 Substitute函數嵌套組成;裡面的 SUBSTITUTE(A1,"「", "") 用於去掉左雙引號,外面的 Substitute 用於去掉右邊的雙引號。

三、SubStitute函數與Replace函數的區別

SubStitute函數是用一個(或一串)字符替換另一個字符(或一串),而Replace函數是用一個(或一串)字符替換指定的字符數。如果要替換括號中的一串不確定有多少個的字符,由於SubStitute函數不支持通配符,因此無法實現替換,但用Replace函數卻能替換,方法如下:

1、假如要替換文本「excel2016(kh@ei8#9kdfk8*ksdkjd)」中的括號及其中的字符串為「函數」。把公式 =REPLACE(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1,"函數") 複製到 B1 單元格,如圖7所示:

圖7

2、按回車,則源文本中的括號及其中的字符串被替換為「函數」,返回 excel2016函數,如圖8所示:

圖8

3、公式說明:

1、公式 =REPLACE(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1,"函數") 由Replace函數和Find函數組成,Find函數用於找出左右括號在文本中的位置,Replace函數用於替換。

2、第一個 FIND("(",A1) 用於找出左括號在文本中的位置作為 Replace 的「開始替換位置」;FIND(")",A1) 用於找出右括號在文本中的位置,然後用右括號的位置減左括號的位置再加 1 作為 Replace 的「要查找的字符數」。

相關焦點

  • excel中的替代函數——replace和substitute函數的應用實例
    在excel中,常用的替換函數有replace和substitute函數,這兩個函數都可以替換單元格中的部分內容,功能和ctrl+H的功能類似,但是使用函數的目的一方面不會破壞原數據,另一方面與其他函數結合可以實現更多功能,對於substitute的參數=substitute(單元格,被替換的字符串,新字符串,指定替換第幾個),第四個參數可以省略,表示全部替換。
  • Excel Replace函數與ReplaceB函數的使用方法,含7個替換實例
    在 Excel 中,Replace函數與ReplaceB函數用於替換指定的字符數和字節數,它們都有四個參數,其中三個參數相同,只有一個參數不同,也就是指定字符數和字節數的參數不同。以下是 Excel Replace函數與ReplaceB函數的使用方法,總共有7個實例,分別為把單槓替換為雙槓,替換姓名中間一個字,把部分數字替換為星號*,替換某個字後的所有字符,替換一段字符中間指定個字符、替換數字、字母和特殊字符和替換單字節與雙字節,實例操作所用版本均為 Excel 2016。
  • Excel Find函數與FindB函數使用方法,含用數組一次查找多個值
    在 Excel 中,查找指定字符在源字符串中的位置,既可以用 Find函數,也可以用 FindB函數,它們都有三個參數,所不同的是,前者把漢字、字母和數字都算一個字符,後者把漢字算兩個字節,數字和字母算一個字節。
  • Excel Trim函數使用方法,含用三個去空格函數都刪不了的空格實例
    在 Excel 中,Trim函數用於去掉字符前後和字符之間的空格;當用於去掉字符前後空格時,只要是正常的空格,不管有多少個都能去掉,但用於去掉字符之間的空格時,會留下一個空格,不管字符是漢字還是英文。Trim函數只能去掉正常的空格,一些特殊的空格它無法去除,遇到這種情況,可以嘗試用Clean函數或Substitute函數,如果還不能去掉,可以嘗試查找替換。
  • Excel if函數多個條件嵌套與用And/*和Or/+組合條件的使用方法
    if函數是 Excel 中的條件判斷函數,它由條件與兩個返回結果組成,當條件成立時,返回真,否則返回假。if函數中的條件既可以單條件,也可以是多條件;多條件組合有三種方式,一種為多個 if 嵌套,第二種為用 And(或 *)組合多個條件,第三種為用 Or(或 +)組合多個條件。
  • Excel Choose函數的使用方法,含與Match與VLookUp配合使用的實例
    除可以用單個數字作索引號外,還可以用數組;用數組作索引號常常在和Match函數或VLookUp函數配合使用時出現,以下列舉了 Excel Choose函數使用方法的6大實例,其中就包含有和Match函數或VLookUp函數配合使用的實例,實例操作所用版本均為 Excel 2016。
  • EXCEL函數公式大全之利用SUBSTITUTE函數REPLACE函數刪除特定文本
    EXCEL函數公式大全之利用SUBSTITUTE函數和REPLACE函數的組合刪除特定字符串中的字符。EXCEL函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數SUBSTITUTE函數和REPLACE函數。
  • Excel Column函數和Columns函數的使用方法,含Column($1:$5)實例
    在 Excel 中,Column函數用於返回單元格的列號,一次既可以返回一列的列號,也可以返回多列的列號;而Columns函數作用是返回數組或引用單元格的列數。Column函數通常與Indirect函數、If函數、Index函數、VlookUp函數、SumProduct函數、Mod函數組合使用。
  • 幾個常用的Excel字符串函數,職場人精英必備,直接複製使用
    【用途】將兩個或多個文本字符串連接成一個字符串【實例】如下圖將B3、C3單元格文字合併到E3單元格中,在E3單元格中輸入公式:=CONCATENATE(B3,C3),演示如下圖:二、REPLACE函數【語法】REPLACE(old_text, start_num, num_chars, new_text)【用途】將特定位置的字符串替換為不同的文本字符【實例】把B11單元格中「帶給」替換為「分享」,在B13單元格中輸入公式:=REPLACE(B11,11,4,"分享"),演示如下圖:
  • Excel Mid函數與Midb函數的使用方法,含反向取值
    在 Excel 中,提取指定長度的字符有兩個函數,分別為Mid函數和Midb函數,前者用於提取指定長度的字符個數,後者用於提取指定長度的字節個數。用Mid函數提取時,無論是漢字、字母還是數字都算一個字符;用Midb函數提取時,漢字算兩個字節,數字和字母算一個字節。一般情況下,它們從左向右提取;但也可以反向提取,即從右向左提取。
  • Excel Round函數的使用方法,含批量給公式添加Round保留小數實例
    以下是 Excel Round函數的使用方法,共有五個實例,包含批量給已有公式添加Round保留小數實例,實例操作所用版本均為 Excel 2016。二、Round函數的使用方法及實例(一)保留兩位小數的實例1、選中 B1 單元格,輸入公式 =round(a1,2),按回車
  • 最全批量去除EXCEL/WPS單元格中隱形符號和空格的方法
    在使用EXCEL的VLOOKUP、IF等函數過程中,有時因為單元格中存在看不見的空格或符號,導致函數應用出錯。為了解決出錯問題,我們需要去除看不見的空格和符號。下面是本人根據工作實踐總結的去除看不見的空格和符號的幾種方法,及各種方法的適用場合。
  • Excel用AverageIfs函數多條件求平均值,含同列雙條件的實例
    在 Excel 中,如果要多條件求平均值,可以用AverageIfs函數,它最多可以有 127 個條件,每個條件對應一個區域,即可以組合 127 個條件範圍/條件對,並且一個條件範圍即同列可以組合多個條件。
  • excel函數公式大全之利用SUM函數IF函數的嵌套把成績劃為三個等級
    excel函數公式大全之利用SUM函數和IF函數的嵌套把學生成績劃為三個等級。excel函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數SUM函數和IF函數。
  • excel中if函數的功能是什麼,參數含義是什麼,具體該怎麼使用?
    excel函數的具體作用:判斷是否滿足條件,當條件滿足時就返回一個值,不滿足條件時又返回另外的一個值。 IF函數中的各個參數介紹,第一個任何可以計算為TRUE或者FALSE的數字或者表達式。
  • 「Excel技巧」Replace替換函數的介紹和實例應用總結
    學Excel,肯定要學函數,函數學得好,在Excel應用中就會如魚得水。今天我們一起來學習一下,Excel中的函數之有:replace函數。Replace函數,顧名思義,就是「替換」,即功能就是執行替換操作,替換字符中的指定字符。
  • excel函數應用技巧:如何按不同要求,改變數字格式
    遊戲 2:替換某個數43854轉換成43054,改變一個數值中某一位的值。【ANSWER 1】【函數解析】使用REPLACE函數,將字符串中指定的第幾位開始的連續幾位的值,替換為一個新的字符串。LEN(A2)等於5,減2等於3,第4參數是1,即從第3位開始的1位字符串,替換成0,得到43054。
  • Excel SubTotal函數的使用方法,含隱藏篩選和分類匯總實例
    SubTotal函數是 Excel 中的分類匯總函數,它共支持 11 個函數,分別為 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp,這些函數有兩組編號,一組為 1 到 11,另一組為 101 到 111,其中前一組包含隱藏值,後一組不包含隱藏值。
  • 利用VBA代碼進行替換單元格內字符串的操作方法實例
    在VBA代碼中,如果需要替換單元格內指定的字符串,那麼可以使用Range對象的Replace方法。REPLACE也是EXCEL函數中的一員,在講EXCEL函數時我也詳細的講解過,今日給大家講的是此函數在VBA中的利用。在VBA中此函數的應用非常的廣泛,是應用於Range對象的一種方法。那麼什麼是方法呢?
  • 數據分析必備基礎技能Excel常用函數公式及使用技巧
    :A5),求最小值:=MIN(A1:A5),計算數量:=COUNT(A1:A5) ,計算餘數:=MOD(5,4)使用方法:以上幾個函數公式是最基礎常見的,可以直接在單元格中輸入計算,當然也可以先選中需要計算的單元格(A1:A5),然後點擊Excel表格上方的公式,自動求和的下拉三角形,選擇計算方式即可。