【エクセルの置換】SUBSTITUTE関数を使って特定の文字を別の文字にする方法!

3 min 1,236 views
エクセル初心者

エクセル初心者

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

それなら『エクセルの置換ができるSUBSTITUTE(サブスティテュート)関数』がオススメだよ!くわしく解説するね!

Dr.オフィス

Dr.オフィス

今回はエクセルの置換をSUBSTITUTE関数を使って解決します。

社員名簿の住所を全て-(ハイフン)に書き換える作業をひとつひとつを手入力でしていたら時間がもったいないです。

時短で効率よく置換しましょう。

SUBSTITUTE関数の簡単2ステップ
  1. 書き換える列を追加
  2. 追加した列にSUBSTITUTE関数を挿入

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

本文ではSUBSTITUTE関数を使い、特定の文字を置換する方法をくわしく解説します。

SUBSTITUTE関数は『エクセル関数のレベルアップ』におすすめです。

ほかにも脱初心者が知っとくべき関数を43個紹介した記事があるので、ぜひチェックしてみてください。

エクセルの置換について基本から応用までくわしく解説した記事は、こちら≫【エクセルの置換】使い方を基本から応用まで解説!になります。

こちらも、参考にしてみてください。

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

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

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

エクセルの置換を関数で解決するには

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

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

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

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

Dr.オフィス

Dr.オフィス

今回のような場合に使える関数は【SUBSTITUTE関数】です。

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

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

ポイント

SUBSTITUTE(サブスティテュート)関数とは

=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,”丁目”,”-“),”番”,”-“),”号”,””)

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

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

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

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

エクセルの置換は関数で解決

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

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

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

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

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

エクセルの置換について基本から応用までくわしく解説した記事は、こちら≫【エクセルの置換】使い方を基本から応用まで解説!になります。

こちらも、ぜひチェックしてみてください。

ExcelドクターがおすすめするExcel本はこちら

カテゴリー:

20万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に20万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事