関数

エクセルの置換で特定の文字を別の文字にする!SUBSTITUTE関数で簡単解決!

タイトル
エクセル初心者
エクセル初心者
社員名簿を作成して提出すると『”丁目”や”番地”などを”-(ハイフン)”に置き換えて表示して欲しい』と上司から言われた。1000人を超える社員名簿だから一人一人を書き換えていたら完成はいつになるだろう・・・。簡単に書き換えられる方法があればいいのになぁ。

このようにエクセルでは膨大なリストを作成することが多いですね。

そして、その膨大なリストの書き換え作業も、様々なシチュエーションで発生します。

例えば、膨大な商品管理簿の商品ナンバーのアルファベットだけを『H-00000』から『M-00000』に書き換える。なども考えられます。

冒頭の社員名簿の住所を全て-(ハイフン)に書き換える作業も、商品ナンバーのアルファベットを書き換える作業も、ひとつひとつを手入力でしていたら時間がもったいないです。

せっかくのエクセルですので、関数を使いパッと解決したいですね!

今回のような場合に使える関数は【SUBSTITUTE(サブスティテュート)関数】です。

SUBSTITUTE関数なら、特定の文字を別の文字に置き換えることが出来ます。

文字列を操作する大変便利な関数です。

SUBSTITUTE関数の簡単2ステップ

①書き換える列を追加
②追加した列にSUBSTITUTE関数を挿入

ステップはとても簡単ですね。

SUBSTITUTE関数を使い、特定の文字を置換してみましょう。

関数を使わずに置換することが出来るホームタブの置換も解説します。

◇記事内のエクセル用語◇

置換(ちかん)文字列を別の文字列に置き換えること。

※本記事は『OS:Windows10』画像は『Excelのバージョン:2019』を使用しています。

エクセルの置換で特定の文字を別の文字にする【SUBSTITUTE関数】

SUBSTITUTE関数は、対象のリストの中ら特定の文字を探し出し、指定した文字に書き換えることが出来る関数です。

対象となる文字が複数ある場合には、ネスト(入れ子)で2つ以上の文字列も置換できます。

SUBSTITUTE関数とは

=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)

意味:文字列の中から検索文字列を探し、置換対象の指示に従い置換文字列と置き換えて表示します。

Dr.オフィス
Dr.オフィス
引数の『置換対象』は省略も可能だよ!省略するとすべての『検索文字列』が置換されるよ!

SUBSTITUTE関数で文字を置き換えてみよう

社員名簿を使って解説します。

社員名簿社員名簿

↑この社員名簿の住所の”丁目”や”番地”などを”-(ハイフン)”に置き換えて表示しましょう。

D列を挿入D列を挿入する

↑住所の列の右側に1列挿入します。

この空白のD列にSUBSTITUTE関数で、”丁目”や”番地”などを”-(ハイフン)”に置き換えた文字を表示してみましょう。

”丁目”を”-(ハイフン)”に置換”丁目”を”-(ハイフン)”に置換

↑まずは”丁目”を”-(ハイフン)”に置換する式は

=SUBSTITUTE(C3,”丁目”,”-“)

になります。

C3の住所から検索文字列の”丁目”を探して、置換文字列の”-(ハイフン)”に書き換えて表示しています。

”丁目”が”-(ハイフン)”に置換された”丁目”が”-(ハイフン)”に置換された

↑=SUBSTITUTE(C3,”丁目”,”-“)を確定させてみると『〇〇市高崎町3-4番2号』と、チョウ目がハイフンに変わっていますね。

”番”と”号”も、それぞれにSUBSTITUTE関数を使って置換しましょう。

”番”を”-(ハイフン)”に置換する”番”を”-(ハイフン)”に置換する

↑次に”番”を”-(ハイフン)”に置換する式は

=SUBSTITUTE(C4,”番”,”-“)

C4の住所から”番”を探して”-(ハイフン)”に置換します。

”号”を”空白”に置換する”号”を”空白”に置換する

↑”号”は”空白”に置換します。

=SUBSTITUTE(C5,”号”,””)

C5の”住所”から”号”を探して””空白で表示します。

番も号も置換完了番も号も置換完了

↑”番”が”-(ハイフン)”に、”号”は”空白”になりました。

SUBSTITUTE関数は対象となる文字が複数ある場合、ネスト(入れ子)で置換できます。

ですので、”丁目”と”番”と”号”を置換する上記のSUBSTITUTE関数を組み合わせます。

ネストのSUBSTITUTE関数ネストのSUBSTITUTE関数

↑D列に再度、式を入れなおします。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,”丁目”,”-“),”番”,”-“),”号”,””)

とても長い式になりましたが、この式で”丁目”と”番”は”-(ハイフン)”になり、”号”は空白へ置換されます。

オートフィルでコピーオートフィルでコピー

↑あとはオートフィルでコピーすれば完成です。

とても簡単に、いっぺんに、すばやく、置換できました。

関数を使わない置換の方法

置換は、関数を使わずにホームタブの『検索と選択』からも行えます。

エクセルの機能『検索と選択』エクセルの機能『検索と選択』

↑住所のC列を選択し『検索と選択』をクリックします。

項目に『置換』がありますのでクリックします。

検索と置換ダイアログボックス検索と置換ダイアログボックス

↑検索と置換のダイアログボックスが表示されました。

丁目を-(ハイフン)に置換丁目を-(ハイフン)に置換

↑検索する文字列に『丁目』、置換後の文字列に『-』を入力し『すべて置換』を選択します。

置換した数を把握置換した数を把握

↑何件、置換してくれたのかを教えてくれますのでOKを押しましょう。

番と号も同じように番と号も同じように置換

↑”番”と”号”も同じように置換できます。

検索と置換ダイアログボックスはショートカットキーで素早く表示することも出来ます。

こちらの≫【エクセルのショートカットキー】置換のダイアログボックスを表示する「Ctrl+H」を参考にしてみてくださいね。

タイトル
【エクセルのショートカットキー】置換のダイアログボックスを表示する「Ctrl+H」エクセルで『置換』のダイアログボックスを素早く表示するショートカットキーを解説します。Ctrl(コントロール)とH(エイチ)のショートカットキーを使い、エクセルでの膨大なデータ処理をこなしましょう。例えば文字の訂正などがあった場合、とても役立つ便利な技になりますので、ぜひ参考にされてみてください。...

まとめ

特定の文字を別の文字に置き換える場合、SUBSTITUTE関数で簡単に置換することが出来ます。

対象となる文字が複数ある場合にも、ネストさせることで複数の文字列が置換できます。

上記のようにSUBSTITUTE関数なら、いっぺんに置換が可能です。

SUBSTITUTE関数は文字列を操作する大変便利な関数です。

そして、エクセルの機能の『検索と置換』も紹介しました。

どちらも膨大なデータの特定の文字を置換する場合に、ぜひ使ってみてください。

ABOUT ME
Dr.オフィス
Dr.オフィス
エクセルの使い方や応用法を日々研究しています。 初心者から上級者までわかりやすく解説していきます。 エクセルについてわからないことや気になることなどございましたら是非、コメントやお問い合わせからご質問ください。