Excelで文字列や数値を簡単に置換したい時に便利な「SUBSTITUTE関数」の使い方

SUBSTITUTE関数の使い方がよく分からない、という方も多いのではないだろうか。SUBSTITUTE関数は文字列や数値の置換を行えるExcelの関数で、書き間違えを一括で直したい場合などに便利だ。

本記事ではSUBSTITUTE関数について解説する。SUBSTITUTE関数で文字列・数値を置換する方法やスペース・改行を削除する方法についてまとめた。

SUBSTITUTE関数とは？

SUBSTITUTE関数とは文字列や数値の置換を行えるExcelの関数だ。たとえば、Excelで資料を作成したときに、固有名詞や人の名前を間違えてしまった場合、SUBSTITUTE関数を使えば一括で置換することができる。

SUBSTITUTE関数の書式は次のとおり。

=SUBSTITUTE({文字列}, {検索文字列},{置換文字列})

1つ目の引数に置換対象の文章を指定する。2つ目に置換前のワード、3つ目に置換後のワードを指定することで、まとめて置換することができる。

なお、場合によっては4つ目の引数を指定することもある。4つ目の引数では「置換対象」を番号で指定する。たとえば、「2」と指定することで、文章内に2回目に現れたワードだけを置換できる。1回目に現れたワードは置換せずにスルーできる。

SUBSTITUTE関数で文字列・数値を置換する

SUBSTITUTE関数で文字列・数値を置換する方法について解説する。以下の6つを行うやり方について順番にみていこう。

1. 文字列の置き換え
2. 数値の置き換え
3. 最初の数値の置き換え
4. 2番目の数値の置き換え
5. スペースの削除
6. 改行の削除

■1.文字列の置き換え

まず、SUBSTITUTE関数で文字列を置き換える方法を解説する。

ここでは文章内の「渡辺」を「渡邉」に書き換える方法をみていこう。

以下では「=SUBSTITUTE(A2, “渡辺”,”渡邉”)」としている。SUBSTITUTE関数は第2引数に書き換えたいワード、第3引数に書き換え後のワードを指定する。

実行すると「渡辺」が「渡邉」に変わっていることがわかる。

■2.数値の置き換え

続いて、数値の置き換え方法について解説する。

ここでは、文章内の「1」を「2」に書き換えたい場合をみていこう。

以下では「=SUBSTITUTE(A2,”1”,”2”)」としている。数値を書き換えたい場合も文字列の場合と同じように「””」でくくる必要がある。くくらないと置換できないので気をつけよう。

実行すると「1年生」と「1組」が「2年生」「2組」に変わっている。

■3.最初の数値の置き換え

前項では最初の「1」と2番目の「1」が両方とも「2」に変わっていた。では、最初の「1」だけを「2」に変えたい場合はどうしたら良いだろうか。

この場合は、SUBSTITUTEに4つめの引数を指定することになる。

以下では「=SUBSTITUTE(A2,”1”,”2”,1)」としている。4つ目の引数に「1」を指定することで、1番目（最初）だけ置換することができる。

実行すると最初だけが「2」に変わっている。なお、4つ目の引数の値は「””」でくくってはいけない。4つ目は数値として指定する。

■4.2番目の数値の置き換え

続いて、2番目の「1」だけを「2」に置き換える方法をみていこう。

以下では「=SUBSTITUTE(A2,”1”,”2”,2)」としている。今度は4つ目の引数に「2」を指定している。

実行すると、2つ目の「1」のみが「2」に変更されている。

■5.スペースの削除

続いて、SUBSTITUTE関数でスペースを削除する方法をみていく。スペースを削除する場合も基本的なやり方は変わらない。

以下では「=SUBSTITUTE(A2,”　”,””)」としている。2つ目の引数には全角スペースを入れて、3つ目には何も入れない。こうすることで、全角スペースを削除できる。

実行すると、全角スペースが削除されていることがわかる。

■6.改行の削除

最後に改行を削除する方法をみていこう。

以下では「=SUBSTITUTE(A2,CHAR(10),””)」としている。「CHAR(10)」とすることで、改行を表現することができる。

実行すると、改行が削除されていることがわかる。

SUBSTITUTE関数を使う際の注意点

SUBSTITUTE関数を使う際の注意点について解説する。注意点は次の2つ。

1. カンマが抜けていると置換できない
2. 引数の順番に気をつける

上手く置換できない場合、これら2つに引っかかっていないか確認しよう。ひとつひとつの注意点について詳しくみていく。

■1.カンマが抜けていると置換できない

以下のように、2つ目と3つ目の引数の間のカンマが抜けていると、置換することができず、エラーが発生してしまう。

SUBSTITUTE関数は「,」や「”」を多く使うため、このようなミスが起きやすい。以下のようなエラーが出たら、カンマが抜けていないか確認してみよう。

■2.引数の順番に気をつける

SUBSTITUTE関数は2つ目の引数に「変更前の値」、3つ目の引数に「変更後の値」を指定する決まりになっている。2つ目と3つ目が逆になっていると、正しく変換できない。

以下では「=SUBSTITUTE(A2,”2″,”1″)」としており、「2」を「1」に置換することになってしまっている。そのため、何も変換することができていない。

SUBSTITUTE関数の実行結果が変わらない場合は、変数の順番を確認してみよう。

まとめ

本記事ではSUBSTITUTE関数について解説した。最後に、SUBSTITUTE関数で文字列・数値を置換する方法についておさらいしよう。

1. 文字列の置き換え
   例：=SUBSTITUTE(A2, “渡辺”,”渡邉”)
2. 数値の置き換え
   例：=SUBSTITUTE(A2,”1”,”2”)

3. 最初の数値の置き換え
   例：=SUBSTITUTE(A2,”1”,”2”,1)

4. 2番目の数値の置き換え
   例：=SUBSTITUTE(A2,”1”,”2”,2)

5. スペースの削除
   例：=SUBSTITUTE(A2,”　”,””)
6. 改行の削除
   例：=SUBSTITUTE(A2,CHAR(10),””)

SUBSTITUTE関数で文字列や数値を置換する機会は多いので、やり方を覚えておこう。

構成／編集部