ExcelのSUBSTITUTE関数で一致する文字列を置き換える

Excelで特定の単語や記号を探して置き換えるにはSUBSTITUTEを使います。第4引数を省略するとすべての一致箇所を、2を指定すると2番目の一致箇所だけを置き換えます。大文字と小文字は区別され、結果は元のセルとは別に返されます。

元の文字列、検索文字列、置換後の文字列を指定する

=SUBSTITUTE(text,old_text,new_text,[instance_num])

第1引数は元の文字列かセル参照、第2引数は検索文字列、第3引数は置換後の文字列です。省略可能な第4引数は左から数えた一致の順番です。2は文字の位置にかかわらず2番目の一致箇所を指します。

MicrosoftのSUBSTITUTEの書式と引数

すべて置換する場合と2番目だけの場合を比べる

A2にAB-CD-cd-CDを入力し、表の数式をそれぞれ別の空白セルに入力します。大文字のCDは2回出てきます。中央の小文字のcdは一致しません。

数式 結果 置換箇所
=SUBSTITUTE(A2,"CD","ZZ") AB-ZZ-cd-ZZ 大文字のCDを両方置き換える
=SUBSTITUTE(A2,"CD","ZZ",2) AB-CD-cd-ZZ 2番目の大文字のCDだけを置き換える
=SUBSTITUTE(A2,"CD","ZZ",3) AB-CD-cd-CD 3番目の一致がないため元の値を返す

先頭の0を残してハイフンとスペースを削除する

A6に00-27 04を文字列として入力します。自動変換される場合は先頭にアポストロフィを付けて入力します。最初の数式はハイフンを取り除いて0027 04を返します。2番目は半角スペースも取り除いて002704を返します。

=SUBSTITUTE(A6,"-","")
=SUBSTITUTE(SUBSTITUTE(A6,"-","")," ","")

内側のSUBSTITUTEの結果を外側のSUBSTITUTEに渡すと、種類の違う文字を順に除去できます。先頭の0が重要な識別子は文字列のままにします。入力時に失われた0を、この数式で復元することはできません。

Microsoftの先頭の0を文字列として保持する方法

置換されない場合に確認すること

大文字と小文字、ハイフンの種類、スペースの種類を確認します。通常の半角スペースを指定しても改行しないスペースは残ります。前後の半角スペースを取り除き、語の間に一つだけ残すにはTRIMを使います。

置換する文字が異なっても位置が一定ならREPLACEを使います。全コードで4文字目から2文字を変更する場合は、検索文字列の代わりに開始位置と文字数を指定します。