【Excel】「大量のデータを修正したい」 そんなときはSUBSTITUTE関数とREPLACE関数で時短しよう

今回は、文字列の一部だけを別の文字列に置き換えられるSUBSTITUTE関数とREPLACE関数をご紹介します。「大量のデータを修正したい」「文字列のここだけを○○に置換したい」というときに便利な関数です。

【Excel】「大量のデータを修正したい」 そんなときはSUBSTITUTE関数とREPLACE関数で時短しよう

今回は、商品リストや顧客データ、住所録など、データベースなどを修正したい時に使えるMicrosoft Excel(以下、Excel)のテクニックをご紹介します。たとえば、「商品コードの一部だけを変えたい」「電話番号のハイフンを削除したい」という時、一つひとつ手作業で修正するのは大変ですよね。

そこで今回は、文字列の一部だけを別の文字列に置き換えられるSUBSTITUTE関数REPLACE関数をご紹介します。「大量のデータを修正したい」「文字列のここだけを○○に置換したい」というときに便利な関数です。

 

Excelのデータは数値と文字列に分類される



Excelに入力されるデータは、数字、日付、通貨、時間など、いろいろな種類があります。ですが、どんなデータも大きく「数値」と「文字列」に分類されます。

数値:何かしらの計算ができるデータ。日付や時間、通貨も計算対象になりうるので数値に分類される。

文字列:読んで字のごとく「文字」として認識されるデータ。文字列は、デフォルトでは左詰めで表示される。見た目は数字であっても、左寄せになっている場合は文字列として認識されている。

数値と文字列の違いは、Excelを扱う上で知っておくべき最重要事項です。違いを理解したうえで、次の関数紹介に進みましょう。

 

特定の文字列を検索し、それを別の文字列に置換するSUBSTITUTE関数



SUBSTITUTE
関数は、文字列の中の一部だけをほかの文字列に置き換える関数

式は「SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)」で定義されます。引数が多いですが、落ち着いて見ていけば使い方自体は難しくないので安心してください。

上記の例では「‐(ハイフン)→★」に置換していますが、置換対象にあたる部分は省略しています。置換対象は「文字列のなかの○番目に登場するものだけを置換したい」という時に使います。

たとえば、置換対象に「2」を入れたら、「文字列に含まれる検索文字列で2回目に出てくるもののみを置換文字列に置き換える」という意味になります。

つまり、置換対象を省略すれば検索対象がすべて置換され、値を入れれば○回目に登場する検索対象のみが置換されるということです。

 

SUBSTITUTE関数を応用して、同時に複数の箇所を修正する


SUBSTITUTE
関数をネスト(入れ子、多重構造)にすることで、1つの文字列に対して同時に複数箇所を置換できます。以下の例ではA2セル「株式会社ABC 様」をSUBSTITUTE関数のネストで「(株)ABC 御中」に変換しています。

このようにSUBSTITUTE関数を2重にすることで、1つの文字列に対して同時に複数の修正を入れることができます。この方法を応用すれば何カ所でも同時に置換することができます。

○文字目から○文字目までを置換するREPLACE関数



SUBSTITUTE関数は「文字の中身」を置換の対象とするのに対し、REPLACE関数「文字の位置」を基準に別の文字列に置き換える関数です。式は「=REPLACE(文字列,開始位置,文字数,置換文字)」で定義されます。

 

REPLACE関数の置換文字を指定しなければ、「○文字目から○文字目まで削除」もできる


上記の例では文字列を置換しましたが、引数の「置換文字」に何も代入しなければ、「○文字目から○文字目まで削除」という結果になります。

ちなみに、SUBSTITUTE関数も同様で、置換文字列にあたる部分を「“”」とすれば、検索文字列を削除することができます。文字列の余計なところだけを取り除きたいという時に便利なので、こちらのテクニックも覚えておきましょう。

 

文字列の関数を使えば、無駄な時間を減らすことができる



今回はSUBSTITUTE関数とREPLACE関数をご紹介しました。再度となりますが、「大量のデータを修正したい」「文字列のここだけを○○に置換したい」という時に大変便利です。

手作業でやっていたら数時間かかるものが、この関数を使えば数秒で終わってしまいます。できるだけ関数を駆使して、仕事をサクッと終わらせましょう!

【参考記事】
Excel(エクセル)記事まとめ
覚えておけば残業知らず!Excel時短記事まとめ
見づらい資料からサヨナラ!Excel資料作成記事まとめ
覚えて仕事を効率化!Excel関数記事まとめ
PowerPoint(パワーポイント)記事まとめ

page top