スプレッドシートの文字列置換のSUBSTITUTE関数を解説～セルを書き換えずに置き換え

2021年2月20日

スプレッドシート内のセルデータを書き換えず、文字列置換して置換結果を出力するSUBSTITUTE関数があります。

SUBSTITUTE関数の機能と通常のスプレッドシートの置換機能との違い、使い分け方法について解説します。

スプレッドシートには置換機能が用意

スプレッドシートには置換機能が備わっています。

メニューバー「編集」から「検索と置換」を選択する、またはショートカットキー「Ctrl + h」で置換機能を立ち上げることができます。

置換したい文字列と置換後の文字列を入力し、置換するシート・セル範囲を指定すると、設定した条件でスプレッドシートの置換を行うことができます。

スプレッドシート標準の置換機能を実行すると、シート内のセルの文字列が書き換えられます。

データ自体に変更が生じているため、置換完了後には、置換前の文章は存在しなくなってしまいます。

データを有効活用するためには、加工前のデータも残しておきたいニーズがあります。

置換などのデータ加工・整形を行うことで、より見やすく、分析しやすいデータになるものの、データの情報量は減少します。

別の切り口からデータを分析したい場合に、加工前のデータが残っていないと、使いたいデータが欠落されている可能性もあります。

そのため、スプレッドシートでセルを置換する際には、セル内のデータを書き換えず、別途一部を置換したデータを別シートに出力することが求められます。

スプレッドシート上のデータを残したまま、文字列を置換したデータを出力する関数として、SUBSTITUTE関数が用意されています。

=SUBSTITUTE(検索対象のテキスト, 検索文字列, 置換文字列, [出現回数])

関数の実行例として紹介されているサンプルは以下のようになります。

SUBSTITUTE(“abcdefg", “cde", “xyz")

SUBSTITUTE関数のサンプルの実行例では、「abxyzfg」という結果が出力されます。

4番目の「出現回数」は省略可能なオプションの引数で、省略すると、検索文字列に合致する箇所がすべて置換文字列に変換されます。

出現回数を指定した場合、1なら検索文字列を探索し、1番目に見つかった部分を置換し、2なら2番目を置換します。

SUBSTITUTE関数で出現回数を指定すると、検索文字列内で1度しか置換されません。

実際にGoogleスプレッドシートでSUBSTITUTE関数を使ったサンプル事例で使い方を解説します。

今回スクレイピングで取得したドトールコーヒーショップのデータを利用します。

店舗名が「ドトールコーヒーショップ　○○店」と共通の店名＋店舗名で、表示領域を大きくなっています。

そこで、必要な店舗名だけ、SUBSTITUTE関数で置換した結果を出力してみます。

=SUBSTITUTE(A2,"ドトールコーヒーショップ", “")

置換文字列の引数に何も入力しないことで、SUBSTITUTE関数で不要な文字列を取り除くことができました。

さらに、営業時間もhh:mm形式からhh時mm分という日本語表示に変換してみます。

まず、SUBSTITUTE関数で「:」を「時」に置換した上で、再度「-」を「分」に置換し、末尾に「分」を結合します。

SUBSTITUTE関数を1回実行するだけでは変換が難しいですが、複数回組み合わせることで置換ができました。

このようにSUBSTITUTE関数を利用することで、セルのデータは保持したまま文字列置換した結果を得ることができます。

今回、スプレッドシートのシート内のデータは保持したまま、文字列置換した結果を出力するSUBSTITUTE関数を紹介しました。

スプレッドシートには標準で置換機能がありますが、この置換機能だとデータ自体が書き換えられてしまい、元データがなくなってしまう恐れがあります。

元データを残したまま、文字列を置換したデータを得るためにSUBSTITUTE関数が便利です。

検索文字列を工夫したり、複数回組み合わせることで複雑な置換もできるようになります。

スプレッドシートのシートのデータを残したまま、置換したい場合にSUBSTITUTE関数を活用しましょう。

Posted by yamamoto