関数

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

エクセル初心者
エクセル初心者
社員名簿を作成して提出すると『”丁目”や”番地”などを”-(ハイフン)”に置き換えて表示して欲しい』と上司から言われたよ。1000人を超える社員名簿だから一人一人を書き換えていたら完成はいつになるだろう・・・。簡単に書き換えられる方法があればいいのになぁ。
Dr.オフィス
Dr.オフィス
それなら『エクセルの置換ができるSUBSTITUTE(サブスティテュート)関数』がオススメだよ!くわしく解説するね!

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

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

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

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

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

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

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

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

タイトル
【初心者必見】エクセルの関数で必要なのは6個だけ!他は覚えなくてもOK!エクセルの関数を紹介します。まだ関数をあまり使ったことのない初心者さんは、今回紹介する6個の関数を使ってみませんか?とても簡単で、計算式もスマートになり、日ごろの仕事に大変役立つ関数です。ホームタブの『ΣオートSUM』に用意されたSUM関数や、『もし○○ならば・・・』のIF関数などをマスターしましょう。...

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

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

タイトル
【エクセルの置換】使い方を基本から応用まで解説!エクセルの『置換機能』の使い方を基本から応用までくわしく解説します。エクセルの置換は、すでに入力済みの文字列を別の文字列に置き換えることができる便利な機能です。さまざまな使い方があり、仕事効率アップに欠かせません。ぜひ参考にしてみてください。...

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

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

※本記事は『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関数は文字列を操作する大変便利な関数です。

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

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

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

タイトル
【エクセルの置換】使い方を基本から応用まで解説!エクセルの『置換機能』の使い方を基本から応用までくわしく解説します。エクセルの置換は、すでに入力済みの文字列を別の文字列に置き換えることができる便利な機能です。さまざまな使い方があり、仕事効率アップに欠かせません。ぜひ参考にしてみてください。...
転職を考えている人におすすめ!

あなたがもし転職を考えていたら、次のような悩みはありませんか?

  • 自分に向いている仕事はなんだろう?
  • 自分の強みはなんだろう?
  • 転職したいけど、自分に合った仕事の探し方がわからない。

このような悩みをお持ちなら、「ミイダス」の転職アプリがおすすめです。

ミイダスは次の3種類の診断を完全無料でおこなうことができます。

ミイダスにできる3つの診断コンテンツ
  • ミイダス市場価値診断
  • コンピテンシー診断
  • パーソナリティー診断

上記の診断結果を見て自分に適した転職先を見いだすことができます。

ミイダスの提携企業

さらにミイダスで市場価値を測定すると、上記のミイダスに登録されている企業170,000社から直接オファーが届く場合もあります。

中には面接確約オファーなどもあり、転職活動をしたいという方にぴったりのサービスです。

転職を少しでも検討しているかたは、まずは無料で登録して、自分の価値をしっかりと見つめなしてみませんか!

すでに44万人が登録していて、毎月2万人が登録中のミイダスがあなたの転職をサポートしてくれます。
無料で自分の市場価値を調べてみる≫