【Excel・エクセル】文字列や数字を置換する2つの方法!関数とショートカットキー

2020/10/22

もりの

値を別の値に置き換えたいんだけど。

やまの

置換機能を使うか、関数でもできるよ!

今回のポイント

  • 置換は「Ctrlキー+Hキー」
  • 一括削除するには、消したい文字を何もない状態に置換する
  • 複数の文字列を一括置換するには、SUBSTITUTE関数を使う

 置換の練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

【Excel・エクセル】置換とは?

置き換えのイメージ

置換はある文字や数字を、別の文字や数字に置き換えることです。
ちなみに、「おきかえ」ではなく「ちかん」と読みます。

たとえば消費税が8%から10%に変わって、ファイル内の計算式「価格×1.08」をすべて「価格×1.1」に変えないといけない場合でも、置換を使えば一発で置き換えられます。

もりの

知っておくと便利そう!

やまの

さっそくやり方を見てみよう。

【Excel・エクセル】置換機能を使って置換する方法

表品名と価格の一覧があります。

このたび、わが社の製品名の「MAX」が「DX」に変わることになったので、テーブル内の「MAX」を「DX」に置き換えたいと思います。

MAXをDXに置換したい
MAXをDXに置換したい
やまの

わが社……?

ステップ1

簡単に置換するには置換機能のショートカットキー、「Ctrlキー+Hキー」を押してください。

置換のショートカットキー
置換のショートカットキー

「検索と置換ダイアログボックス」が出てきました。

文字を入力するボックスがありますね。
簡単に言うと、検索する文字列→「コレを」、置換後の文字列→「コレに変えてね」ってことです。

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

ステップ2

今回はMAXをDXに変えたいので、検索する文字列には「MAX」置換後の文字列には「DX」と入力して、「すべて置換」をクリックしてみてください。

MAXをDXに変えるときの画面
MAXをDXに変えるときはこうする

完成!

すると、「5件を置換しました。」と表示が出て、MAXがすべてDXに置き換えられました!

置換された画面
MAXがDXになった

置換機能で一括削除もできる

この置換を使えば、一括で特定の文字削除だってできちゃいます。

B列の価格にはそれぞれ円と単位が入っていますが、これを置換を使って削除してみましょう。

例題
B列の「円」を消したい

ステップ1

ショートカットキー「Ctrlキー+Hキー」を押して、検索と置換ダイアログボックスを呼び出します。

置換のショートカットキー
置換のショートカットキー

ステップ2

検索する文字列には「円」置換後の文字列には何も入れずにすべて置換をクリックしてください。

一括削除の設定画面
置換後の文字列は何も入れない

完成!

すると、「円」が一括で削除されました!
「円」を「何も書いてない状態」に置き換えたんですね。

円が一括削除された画像
円が一括削除された

これを使えば、空白スペースを一気に削除だってできちゃいますね。

文字を指定の列、行だけ置換するには

ちなみに、すべて置換してしまうと、置換してほしくないところも置換してしまうことがあります。

変えたい場所と変えたくない場所が混在している画像
変えたい場所と変えたくない場所

そんな時は、変えたい行、列など、範囲をドラッグして、あらかじめ選んでから置換すればOKです。

範囲選択した画像
あらかじめ範囲を指定できる

数式内の文字を置換する方法

数式内の文字列も置換できます。

例題の税込み価格は、消費税が8%の時の計算式になっています。

数式を見ると、1.08を掛けているようなので、この1.08を新消費税の1.1に置換してみたいと思います。

消費税8%の税込み計算式
よりによって直接計算しているなんて!

ステップ1

今回はC列を選択した状態で、ショートカットキー「Ctrlキー+Hキー」で置換機能を呼び出します。

置換したい範囲を選択しておく
置換したい範囲を選択しておく

ステップ2

検索する文字列には「1.08」、置換後の文字列には「1.1」と入力して、「すべて置換」をクリックしてください。

置換の設定画面
いつも通り置換

完成!

計算式が1.08から1.1に置換され、答えが消費税10%のものに変わりました!

置換を使えば、計算式や関数の式だって変更できちゃうんですね。

置換により答えが変わった式の画像
式が置換され、答えが変わった

ワイルドカードを使って置換する

ワイルドのイメージ

ワイルドカードってご存じですか?
あいまい検索をするときに使うもので、何かしら文字があれば、という指定ができます。

たとえば、「スープ」という文字を「汁」という文字に置換したい場合、味噌スープだけでなく、トマトスープだってスープスパだって、見境なく汁になります。

もりの

トマト汁……

そんな時ワイルドカードを使えば、「なんとかスープ」のようにスープで終わるものだけを置換したり、スープの前が2文字のものだけ置換、なんて指定もできちゃいます。

やまの

やり方は、下記で詳しく紹介するね!

【Excel・エクセル】関数で置換するやり方

関数のイメージ

関数でも置換できます。
それぞれ特徴が違うので、状況に応じて使い分けてください。

エクセルで置換できる関数その1、REPLACE関数

REPLACE関数は、セルの左から何文字目を別の文字に変える関数です。

関数の書き方

=REPLACE(文字列,開始位置,文字数,置換文字)

REPLACE次の文字を置き換えてください
文字列この文字(セル)の
開始文字左から数えて何番目から
文字数何番目までを
置換文字この文字に

REPLACE関数の使い方

下図は、表品名と価格の一覧です。
しかしこの度、パクパクシリーズはモグモグシリーズとして生まれ変わることになりました!

例題
例題
もりの

どうでもいい……

そこで、セルA9以降には、パクパクをモグモグに変えた商品名を入れたいと思います。

やまの

=REPLACE(A2,1,4,E6)で、求められるよ。説明するね。

ステップ1

セルA9に、「=REPLACE(」と入力してください。

もりの

半角英数入力モードにしてから、入力するんだよね。

ステップ2

最初の引数は、「この文字(セル)の」という、対象になる文字(セル)を指定するものでした。
今回は、セルA2のパクパクグミDXを変えたいので、セルA2ですね。

もりの

引数は関数の括弧の中に入れる式だよね。

「=(」の続きに、「A2,」と入力してください。

ステップ3

次の引数は、「左から数えて何番目から」その次は「何番目までを」という、文字列(セル)の中のどこを変更させるのかを指定するものでした。
今回は、パクパクグミDXのパクパク部分を変えたいので、左から数えて1文字目から4文字目までですね。

A2,の続きに、「1,4,」と入力してください。

ステップ4

最後の引数は、「この文字に」という、置換後の文字(セル)を指定するものでした。
今回はモグモグに変えたいので"モグモグ"と文字を入力するか、モグモグと書かれたセルを指定します。

見ると、セルE6に「モグモグ」と書かれていますね。これを使いましょう。

4,の続きに、「E2)」と入力してください。

完成!

これで完成です!エンターキーで確定してください。無事、モグモググミDXに置き換えられましたね。

置換できた画像
パクパクがモグモグになった

エクセルで置換できる関数その2、SUBSTITUTE関数

SUBSTITUTE関数は置換機能と近く、セルの中からある文字を探して、それを別の文字に置き換える関数です。

関数の書き方

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

SUBSTITUTE次の文字を置き換えてください
文字列この文字(セル)の中の
検索文字列この文字を
置換文字列この文字に
置換文字何個目の検索文字列を(省略可能)

SUBSTITUTE関数の使い方

商品名が変わったので、お知らせの文章を作りたいと思います。
下図のセルA10にある文章を使って、旧商品名から新商品名に変わったという文章を作っていきましょう。

例題
例題

<旧商品>をパクパクグミDX、<新商品>をモグモググミDXに置換して、文章を完成させます。

※練習ファイルをダウンロードされた方は、SUBSTITUTEシートに移動してください。

やまの

=SUBSTITUTE(SUBSTITUTE(A8,"<旧商品>",A2),"<新商品>",B2)で、求められるよ。説明するね。

もりの

あれ、SUBSTITUTEが2つあるよ?

やまの

大丈夫、大丈夫。まずは旧商品を置換していくね。

ステップ1

セルA11に、「=SUBSTITUTE(」と入力してください。

もりの

半角英数入力モードにしてから、入力するんだよね。

ステップ2

最初の引数は、「この文字(セル)の」という、対象になる文字(セル)を指定するものでした。
今回は、セルA8の<旧商品>をパクパクグミDXに変えたいので、セルA8を選びます。

もりの

引数は関数の括弧の中に入れる式だよね。

「=(」の続きに、「A8,」と入力してください。

ステップ3

次の引数「この文字を」、「この文字に」は、置換機能で指定した「検索する文字列」「置換後の文字列」と同じです。
今回は、<旧商品>をセルA2のパクパクグミDXに置換したいので、検索文字列には「<旧商品>」を、置換文字列には「セルA2」を選びます。

A8,の続きに、「"<旧商品>",A2)」と入力してください。

最後の引数は、今回は使わないので省略します。※後で解説します。

完成!

では、エンターキーで確定してみてください。
セルA8の文章の<旧商品>が、セルA2のパクパクグミDXに置き換えられました!

置換された画像
置換された!
もりの

置換機能とおんなじだね。

やまの

ところが、決定的に違うところがあるんよ。

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

置換機能では、1つの文字列を別の文字列に置き換えました。
しかし今回は<旧商品>と<新商品>、2つの文字列を一度に変換しないといけません。

もりの

置換機能を2回使うって手もあるけどね。

SUBSTITUTE関数を使えば、複数の文字列を一括で置換できちゃいます
やり方は、SUBSTITUTE関数の中にSUBSTITUTE関数を入れる、いわゆるネスト化(入れ子)するのです!

もりの

よくわかんないけど、すごそう!

先ほど書いた数式に、<新商品>がセルB2に置換できる式を追加していきたいと思います。

書き方はズバリこんな感じ。

=SUBSTITUTE(SUBSTITUTE(置換する文字列,1つ目の検索文字,1つ目の置換文字)2つ目の検索文字,2つ目の置換文字)

もし3つの文字列を置換したい場合は、「SUBSTITUTE(」をもう1つ増やして、式の最後に「3つ目の検索文字,3つ目の置換文字)」を追加します。
これをつなげていけば、4つ5つと、複数の文字列を置換できます。

チャレンジ

では、これに当てはめて、<新商品>をセルB2のモグモググミDXに置換してみましょう!

もりの

答えは先に言っちゃってたけどね。

答え

=SUBSTITUTE(SUBSTITUTE(A8,"<旧商品>",A2),"<新商品>",B2)

これで、二つの文字を同時に置換できました!

二つの文字を一度に置換できた画像
二つの文字を一度に置換できた

これで突然の変更にも、余裕で対応できますね!
それでは、お疲れさまでした!

今回のおさらい

  • 置換は「Ctrlキー+Hキー」
  • 一括削除するには、消したい文字を何もない状態に置換する
  • 複数の文字列を一括置換するには、SUBSTITUTE関数を使う