Office Hack

ExcelのSUBSTITUTE関数の使い方|文字列を置換する

  • Release
  • Update

Excelで文字列の一部を置換したい場合、「検索と置換」機能を使うと元のデータも変更されてしまいます。元のデータを残したい、置換したい文字が複数ある場合、SUBSTITUTE関数が便利です。

SUBSTITUTE関数の意味から使い方、応用まで網羅的にご紹介しています。

SUBSTITUTE関数とは?

意味

SUBSTITUTE関数は、文字列中の指定した文字を新しい文字で置き換えます。

読み方

サブスティチュート

SUBSTITUTE関数の書式

まずは、SUBSTITUTE関数の書式を確認していきましょう。

SUBSTITUTE関数の書式

書式は「=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])」のように記述します。SUBSTITUTE関数では引数を最大4つ使用します。下記で1つずつ説明していきます。

※引数(ひきすう)とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしましょう。関数名の後の括弧「()」内に入力します。

第1引数(文字列)

文字列

1番目の引数は「文字列」です。この引数は必須です。テキストまたは置換するテキストが含まれるセル参照を指定します。

文字列を指定するときは「"文字列"」のようにダブルクォーテーションで囲います。

第2引数(検索文字列)

検索文字列

2番目の引数は「検索文字列」です。この引数は必須です。「文字列」から検索する文字列を指定します。

第3引数(置換文字列)

置換文字列

3番目の引数は「置換文字列」です。この引数は必須です。「文字列」から検索した「検索文字列」と置き換える文字列を指定します。

第4引数(置換対象)

置換対象

4番目の引数は「置換対象」です。この引数は任意です。「文字列」の中に複数の「検索文字列」が見つかったとき、何番目の文字列を置き換えるかを数字で指定します。

指定しない場合、すべての「検索文字列」が「置換文字列」に置き換えられます。

置換対象の使用例を下記でご紹介します。

2番目の文字列を置換する

例えば上図で入力されている住所の2番目の「中野」を「本町」に置換したいとします。第4引数「置換対象」を指定しないと「東京都本町区本町」のようになってしまいます。

関数の入力

C3セルを選択して『=SUBSTITUTE(B3,"中野","本町",2)』と入力し、Enterを押します。4つ目の引数に2番目の「2」を指定しました。

関数の結果

第4引数「置換対象」を指定することで対象の文字列から検索文字列として見つかった2番目の「中野」が「本町」に置換されました。

対象の文字列に検索文字列が複数含まれる場合は置換対象を指定するようにしましょう。

SUBSTITUTE関数の使い方(実践)

実際にSUBSTITUTE関数を使用する例をご紹介します。引数をどのように使用するか見てみましょう。

作業時間:3分

  1. 関数名を入力する

    関数名を入力する

    上図で住所の中央区を新宿区に置換したい場合の例をご紹介します。SUBSTITUTE関数の結果を表示したい【セル(例ではC3セル)】を選択し、『=SUBSTITUTE(』と入力します。

  2. 文字列を指定する

    文字列を指定する

    第1引数の文字列を指定します。「=SUBSTITUTE(」に続いて『B3,』と入力します。マウスでB3セルを選択することでも指定が可能です。

  3. 検索文字列を指定する

    検索文字列を指定する

    第2引数の検索文字列を指定します。「=SUBSTITUTE(B3,」に続いて『"中央",』と入力します。文字列なのでダブルクォーテーション("")で囲います。

  4. 置換文字列を指定する

    置換文字列を指定する

    第3引数の置換文字列を指定します。「=SUBSTITUTE(B3,"中央",」に続いて『"新宿")』と入力します。文字列なのでダブルクォーテーション("")で囲います。最後に数式を確定させるために【Enter】キーを押します。

  5. SUBSTITUTE関数の結果

    SUBSTITUTE関数の結果

    C3セルにSUBSTITUTE関数の結果である「東京都新宿区」が表示されました。B3セルの「中央」というテキストが「新宿」というテキストに置き換わっています。今回は対象となる文字列の中に検索文字列が1つでしたので第4引数「置換対象」は指定していません。

SUBSTITUTE関数の応用

入れ子(ネスト)で複数の文字列を同時に置換する

複数の文字列を別の文字に置換するにはSUBSTITUTE関数の中にSUBSTITUTE関数を入れ子(ネスト)にすることで可能です。入れ子(ネスト)の仕方を確認してみましょう。

表を準備する

上図には住所に中央区と千代田区が入力されています。それぞれ新宿区と江戸川区に修正したいとします。SUBSTITUTE関数を使って1つの数式にしてみましょう。

関数の入力

C3セルに『=SUBSTITUTE(SUBSTITUTE(B3,"千代田","江戸川"),"中央","新宿")』と入力し、数式を確定するためにEnterを押します。

まず入れ子にしたSUBSTITUTE関数を使って「千代田」を「江戸川」に置換します。その置換した文字列をさらにSUBSTITUTE関数の引数にして「中央」を「新宿」に置き換えています。

B3セルには「中央」があるので「新宿」に置換され、C3セルには「千代田」があるので「江戸川」に置換されます。

オートフィル

数式が入力できましたので他のセルにも反映させます。C3セルの右下を【ダブルクリック】します。

関数の結果

オートフィルでC4セルにも数式が反映されました。C3、C4セルにB列の文字列を置換した内容が表示されています。

このようにSUBSTITUTE関数を入れ子にすることで1つの数式で複数の文字列を同時に置換することができます。

空白(スペース)を削除する

表データの中に混じっているスペースを削除したい場合があります。スペースを検索文字列に指定することで一括でスペースを削除することができます。

空白を削除

上図のデータには全角スペースが入った住所が入力されています。隣の列にスペースを削除した住所を表示させます。

関数の入力

C3セルを選択して『=SUBSTITUTE(B3," ","")』と入力し、数式を確定するためにEnterを押します。

空白を削除した結果

C3セルにSUBSTITUTE関数でスペースを削除した結果である「東京都中央区」が表示されました。

全角スペースは「" "」のように指定しましたが、半角スペースは「" "」と指定します。

半角スペースと全角スペースがバラバラになっている場合、SUBSTITUTE関数を使って単語間のスペースの広さを統一することができます。

以下の記事でやり方をご紹介していますのでご参考ください。

改行を削除する

改行を削除するためにはSUBSTITUTE関数の検索文字列として改行を指定します。

表の準備

上図のデータには改行のある住所が入力されています。隣の列に改行を削除した住所を表示させます。

関数の入力

C3セルを選択して『=SUBSTITUTE(B3,CHAR(10),"")』と入力し、数式を確定するためにEnterを押します。

「CHAR(10)」は改行コードを指定しています。

改行を削除した結果

C3セルにSUBSTITUTE関数で改行を削除した結果である「東京都中央区」が表示されました。

セル内の改行の数をカウントしたい場合、LEN関数とSUBSTITUTE関数を組み合わせて求めます。

以下の記事でやり方をご紹介していますのでご参考ください。

SUBSTITUTE関数のよくある質問

SUBSTITUTE関数でワイルドカード、正規表現は使えますか?

SUBSTITUTE関数ではワイルドカード、正規表現を使用することはできません。「検索と置換」機能を使いましょう。

おすすめの商品をご紹介

その他、様々な文字列の編集方法










よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top