Excel使いこなしの基礎と応用

SUBSTITUTE関数で複数の文字列を一括で置き換える

使用関数:SUBSTITUTE

「株式会社」「有限会社」等の会社形態表記や空白スーペスなど、複数の不要な文字列をまとめて一括で削除したいなら、SUBSTITUTE関数をネストして実装します。

目次


SUBSTITUTE関数の構成

この関数の構成を見るために、まずはダイアログを開いてみましょう。

スポンサーリンク

数式」タブの「関数ライブラリ」→「文字列操作」から「SUBSTITUTE」をクリックします。
関数ライブラリの文字列操作にあるSUBSTITUTEをクリック
ダイアログが開きました。
SUBSTITUTE関数のダイアログで構成を見る
文字列」には、置換を実行する文字列の範囲を指定します。「会社名」という列の入力値から(株)を取り除きたいのであれば、その最初のセルをクリックします。
検索文字列」には、置換前の文字列―上記例なら「(株)」を指定します。
置換文字列」には、置換後の文字列―上記例なら空欄を意味する「""」を指定します。
置換対象」は検索文字列が範囲内に複数ある時、何番目を対象にするのか位置情報を数値で指定します。ここは省略すれば全ての検索文字列が対象になります。


関数の概要がイメージできたところで、ここからが本題です。
下図の「会社名」フィールドに属する社名から、「(株)」及び「(有)」を一括で削除します。
削除する文字列
そのためにはSUBSTITUTE関数を更に入れ子するわけですが、ポイントは、どの引数にいれればいいかということ。その構成が理解できれば、色々に応用していけるはずです。

入れ子する引数は「文字列」

表題が結論になってますねw。
SUBSTITUTE関数をネストするのは第1引数の「文字列」です。

SUBSTITUTE関数で「(有)」を削除した後の「文字列」を検索して、「(株)」があれば「""」空欄と置き換える」という数式を組み立てればいいわけですね。(株)と(有)は逆でもOK。

「(有)」を削除する数式をダイアログで組み立てると下図のようになります。
※「会社名」フィールドの右横に「会社名(略称)」というフィールドを新設し、数式はそこに入れています。
(有)を削除する数式を組み立てる
テーブル化したデータ表なので「文字列」の指定が「[@会社名]」と構造化参照になっています。テーブルでなければ、ここは「D2」と入ります。
この数式の「=」を除いた「SUBSTITUTE([@会社名],"(有)","")」をコピーし、「(株)」を削除する数式の引数「文字列」にペーストすれば、この課題は完了します。
引数「文字列」に数式を入れ子
最終的な数式は「=SUBSTITUTE(SUBSTITUTE([@会社名],"(有)",""),"(株)","")」となります。

ネストを果てしなく繰り返すことも可能

(株)の前後に半角スペースがあって、それも削除したければ、入れ子した数式の引数「文字列」に更に数式を入れていくこともできます。

前章の最終式から、入れ子した部分の「(SUBSTITUTE([@会社名],"(有)","")」をコピーして、「=SUBSTITUTE(SUBSTITUTE([@会社名],"(有)",""),"(株)","")」の、([@会社名]にペースト。冒頭のカッコ「(」も含めてドラッグするのがコツ。
数式を入れ子する場所
「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@会社名],"(有)",""),"(有)",""),"(株)","")」。(有)を半角スペースに入れ替えればOK。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@会社名]," ",""),"(有)",""),"(株)","")

(株)(有)半角スペースが取り除かれたフィールドです。
(株)(有)半角スペースが削除された

▶「株式会社」を検索・置換で削除する


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。