ポイント最大43.5倍!楽天お買い物マラソン
終了まで147時間55分

Office Hack

大量のデータも簡単に処理可能!エクセルの置換機能について

  • Release

エクセルで大量のデータを入力した後に、タイプミスや変更があって修正を迫られる場合があると思います。そのような時に便利なのが置換機能です。置換機能とは文字通り置き換えをする機能です。

文字を置換するのはもちろん、文字色やセルの塗りつぶし色などの書式も置き換えの対象とすることができます。

この記事では、基本的な置換機能の使い方から、関数やマクロを使ったハイレベルな置換の方法まで詳しく紹介しています。知っていると非常に便利な機能です。ぜひ覚えましょう。

文字列を置換する

検索と置換で置換する

エクセルで「検索と置換」ダイアログボックスを表示し、文字列を置換する方法をご紹介します。

作業時間:2分

  1. 「検索と置換」ダイアログボックスを表示する
    置換を選択

    ①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

  2. 置換する文字列を入力し、置換する
    検索と置換ダイアログ

    「検索と置換」ダイアログボックスが表示されました。①【検索する文字列】に置換する対象の文字列、②【置換後の文字列】に置換後の文字列をそれぞれ入力し、③【置換】ボタンを押します。今回は「A」を「B」に置換するため、「検索する文字列」に「A」、「置換後の文字列」に「B」を入力しました。

  3. 置換の完了
    置換完了

    文字列が置換されました。

置換のショートカットキー(Win/Mac)

置換のショートカットキーについては下記の記事で詳しく説明しています。

関数で文字列を置換する

文字列を置換するときに使用できる2つの関数をご紹介します。

SUBSTITUTE関数

SUBSTITUTE関数については下記の記事で詳しく説明しています。

REPLACE関数

REPLACE関数では文字列中の指定した位置の文字を置き換えます。書式は、REPLACE(文字列, 開始位置, 文字数, 置換後の文字列)、引数は4つです。例とともにどのように使用するか見ていきましょう。

置換前

図で入力されている住所の「埼玉市」を「さいたま市」に置換します。

REPLACE関数

置換後の文字列を表示するセルを選択し、「=REPLACE(A1,4,2,"さいたま")」と入力します。この関数は「A1セル」の「4文字目」から「2文字」を「さいたま」と置き換えるという内容になっています。

置換完了

置換できました。

範囲指定をして置換する

上記で紹介した方法では、シート全体の文字列が置換の対象となりますが、シート内の範囲を指定して置換することも可能です。

置換を選択

①置換対象とする範囲を選択し、②【ホーム】タブ、③【検索と選択】、④【置換】の順に選択します。

検索と置換ダイアログ

「検索と置換」ダイアログボックスが表示されました。①【検索する文字列】に置換する対象の文字列、②【置換後の文字列】に置換後の文字列をそれぞれ入力し、③【置換】ボタンを押します。

今回は、「A」を「D」に置換するため、「検索する文字列」に「A」、「置換後の文字列」に「D」と入力しました。

置換完了

指定した範囲内の文字列のみが置換されました。

一括で置換する

複数の同じ文字列を一括で置換する方法を紹介します。

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

検索と置換ダイアログ

「検索と置換」ダイアログボックスが表示されました。①【検索する文字列】に置換する対象の文字列、②【置換後の文字列】に置換後の文字列をそれぞれ入力し、③【すべて置換】ボタンを押します。

今回は「D」を「A」に置換するため、「検索する文字列」に「D」、「置換後の文字列」に「A」を入力しました。

置換完了

複数の同じ文字列が一括で置換されました。

全角を半角に置換する

全角を半角に置換する方法ついては下記の記事で詳しく説明しています。

文字列の大文字と小文字を区別して置換する

通常は大文字と小文字の区別をせずに置換されますが、大文字と小文字を区別して置換することもできます。

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

オプションを選択

「検索と置換」ダイアログボックスが表示されました。①【検索する文字列】に置換する対象の文字列、②【置換後の文字列】に置換後の文字列をそれぞれ入力し、③【オプション】ボタンを押します。

今回は小文字の「a」を大文字の「B」に置換するため、「検索する文字列」に「a」、「置換後の文字列」に「B」を入力しました。

置換を押す

「オプション」が表示されました。①【大文字と小文字を区別する】にチェックを入れ、②【置換】ボタンを押します。

置換完了

大文字と小文字が区別して置換されました。小文字の「a」のみが「B」に置換され、大文字の「A」は置換されていません。

テキストボックス内の文字列を置換する

今まではセルに入力された文字列を置換しました。それではテキストボックスの文字列は置換できるのでしょうか?

テキストボックス置換できない

上記でご紹介した「検索と置換」ダイアログボックスを使用した方法だと置換することができません。

テキストボックス内の文字列を置換するためにはVBAが必要になります。

VBAの入力

Alt+F11を押すと「Microsoft Visual Basic for Applications」が開きます。

①【Sheet1(Sheet1)】をダブルクリックし、②『コード』を入力します。

下記のコードが実際に使用したものです。

Sub TextBoxReplace()

Dim shp As Shape
Dim xSearch As String
Dim xReplace As String

xSearch = Application.InputBox("検索する文字列:")
xReplace = Application.InputBox("置換する文字列:")

On Error Resume Next

For Each shp In ActiveSheet.Shapes
    xValue = shp.TextFrame.Characters.Text
    shp.TextFrame.Characters.Text = VBA.replace(xValue, xSearch, xReplace, 1)
Next

End Sub
検索する文字列

F5を押すと「入力」ダイアログボックスが表示されます。ブックを保存していない場合はマクロ有効ブックとして保存しておきましょう。

①『検索する文字列』を入力し、②【OK】ボタンを押します。

置換する文字列

再度「入力」ダイアログボックスが表示されます。

①『置換する文字列』を入力し、②【OK】ボタンを押します。

テキストボックスの文字列が置換された

テキストボックス内の文字列が置換されました。2016から2019に置換されたことが確認できます。

複数の文字列を置換する

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

関数で複数の文字列を置換する方法は、下記の記事の「入れ子(ネスト)で複数の文字列を同時に置換する」の項目で詳しく説明しています。

マクロ(VBA)で複数の文字列を置換する

マクロ(VBA)で複数の文字列を置換する方法をご紹介します。

複数を置換する

例えば上記の中央区を新宿区に、千代田区を江戸川区に置換したいとします。マクロを使って置換してみましょう。

VBAを入力

Alt+F11を押すと「Microsoft Visual Basic for Applications」が開きます。

①【Sheet1(Sheet1)】をダブルクリックし、②『コード』を入力します。

下記のコードが実際に使用したものです。

Sub MultiTextReplace()

With Range("B2:B3")
.replace What:="中央", Replacement:="新宿"
.replace What:="千代田", Replacement:="江戸川"
End With

End Sub

今回はB2からB3までの範囲を対象としています。範囲を指定してください。

Whatに検索する文字列、Replacementに置換する文字列を指定してください。

複数の文字列が置換された

F5を押すと置換が完了します。シートに戻ると複数の文字列が置換されていることが確認できます。

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

ワイルドカードとは、検索や置換などで指定する特殊文字でどんな文字にもなるものを指します。通常の検索や置換では、検索対象と全く同じ文字列のみがマッチしますが、ワイルドカードを使用することで、検索や置換したい文字列があいまいな時や複数の候補があるときなどに柔軟な検索や置換が可能です。

例えば、「山口県」、「山梨県」、「山形県」の3つの文字列を1つの文字列に置換したいとき、通常であればそれぞれ3つの文字列を置換することになります。ワイルドカードを用いることで、「山○県」(最初に「山」最後に「県」が付く文字列)を検索できるようになり、「山口県」、「山梨県」、「山形県」の3つを1度で検索し置換できるようになります。

今回は、ワイルドカードの中でも特に使用する頻度の高い「*(アスタリスク)」を使った置換の方法をご紹介します。

*(アスタリスク)を使用した置換の方法

「*(アスタリスク)」を入力することで、指定の文字列に加えてその前後や文字列中に指定以外を含む文字列を検索し、置換できます。今回は「山○県」のように最初に「山」、最後に「県」がつき、中間に指定外の文字列を含む文字列を置換する例で「*(アスタリスク)」の使い方を説明します。

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

検索と置換ダイアログ

「検索と置換」ダイアログボックスが表示されました。①【検索する文字列】に置換する対象の文字列、②【置換後の文字列】に置換後の文字列をそれぞれ入力し、③【すべて置換】ボタンを押します。

今回は検索する文字列に「山*県」と入力します。これで「山」、最後に「県」がつき、中間に指定外の文字列を含む文字列を置換することができます。

置換完了

「山口県」、「山梨県」、「山形県」3つの文字列が置換されました。

改行コードで改行を置換する

改行コードで改行を置換する方法は下記の記事で詳しく紹介しています。

数式を置換する

セルに「=A1+A2」のように数式を入力しているセルの数式を置換することもできます。

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

検索と置換ダイアログ

「検索と置換」ダイアログボックスが表示されました。①【検索する文字列】に置換する対象の文字列、②【置換後の文字列】に置換後の文字列をそれぞれ入力します。最後に③【置換】ボタンを押します。

今回は「A1+B1」の「A1」を「A2」に置換するので、「検索する文字列」に「A1」、「置換後の文字列」に「A2」を入力しました。

置換完了

数式中の文字列が置換されました。

空白の置換について

空白を置換する

空白(スペース)を置換機能を使って削除する方法をご紹介します。

置換を選択

「東京」と「都」の間にあるスペースを削除します。①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

検索と置換ダイアログ

①【検索する文字列】に「 」(スペース)を入力し、「置換後の文字列」には何も入力せず、②【置換】ボタンを押します。

置換完了

スペースが削除されました。

空白にする(文字を削除する)

文字を削除してセルを何も入力されていない空白の状態にすることもできます。

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

検索と置換ダイアログ

①【検索する文字列】に削除したい文字を入力し、「置換後の文字列」には何も入力せず、②【置換】ボタンを押します。今回は「検索する文字列」に「東京」を入力しました。

置換完了

「東京」が削除され空白になりました。

タブ(Tab)を置換する

メモ帳などから文字列をコピーした際に、Tabキーで挿入された空白があることがあります。これはスペースキーで挿入された空白とは区別されるため、置換機能で置換することはできません。SUBSTITUTE関数を使用することで置換できます。

Tab

A1セル「a」と「b」の間にTabが挿入されています。セル上では空白はありませんが、数式バーでは表示されています。この空白をSUBSTITUTE関数を使用して削除します。

SUBSTITUTE関数

関数を入力するセルを選択し、『=SUBSTITUTE(A1,CHAR(9),"")』と入力し、Enterを押します。「A1」は置換対象とするセル番号(今回はA1セルを対象とするため「A1」)、「CHAR(9)」はTabを示すコード、「""」には置換後の文字列(今回はTabを削除するため「""」)をそれぞれ入力します。

置換完了

Tabが削除されました。

ハイパーリンクを一括で置換する

シート内にあるハイパーリンクをVBAを使って一括で置換する方法をご紹介します。

ハイパーリンクの置換

上記ではデスクトップにあるファイルにハイパーリンクを設定しています。ドキュメントに変更してみましょう。

VBAの入力

Alt+F11を押すと「Microsoft Visual Basic for Applications」が開きます。

①【Sheet1(Sheet1)】をダブルクリックし、②『コード』を入力します。

下記のコードが実際に使用したものです。

Sub HyperlinkReplace()

 Dim h As Hyperlink
    For Each h In ActiveSheet.Hyperlinks
        h.Address = replace(h.Address, "\Desktop\Book1.xlsm", "\Documents\Book1.xlsm")
    Next h
    
End Sub
ハイパーリンクの置換

F5を押すとハイパーリンクの置換が完了します。ハイパーリンクのリンク先がデスクトップからドキュメントに変更されました。

色を置換する

置換機能では、文字や背景の色を置換することもできます。以下で詳しい手順を説明します。

文字の色を置換する

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

オプションを選択

「置換と検索」ダイアログボックスが表示されました。【オプション】ボタンを押します。

書式を選択

オプションが表示されました。まず、検索する文字色を設定します。「検索する文字列」の【書式】ボタンを押します。

セルから書式を選択を選択

「書式の検索」ダイアログボックスが表示されました。①【フォント】タブ、②【セルから書式を選択】ボタンを押します。

セルを選択

カーソルがスポイト型に変わりました。【検索する文字色のセル】を選択します。今回はA1セルを選択します。

書式を選択

次に、置換後の文字色を設定します。「置換後の文字列」の【書式】ボタンを押します。

色を選択

「書式の検索」ダイアログボックスが表示されました。①【フォント】タブ、②【色】、③【任意の色】の順に選択し、最後に④【OK】ボタンを押します。今回は青を選択しました。

置換を選択

「置換と検索」ダイアログボックスに戻りました。【置換】ボタンを押します。

置換完了

文字色が置換されました。

セルの塗りつぶしの色を置換する

置換を選択

①【ホーム】タブ、②【検索と選択】、③【置換】の順に選択します。

オプションを選択

「置換と検索」ダイアログボックスが表示されました。【オプション】ボタンを押します。

書式を選択

オプションが表示されました。まず、検索する文字色を設定します。「検索する文字列」の【書式】ボタンを押します。

セルから書式を選択を選択

「書式の検索」ダイアログボックスが表示されました。①【塗りつぶし】タブ、②【セルから書式を選択】ボタンを押します。

セルを選択

カーソルがスポイト型に変わりました。【検索する塗りつぶし色のセル】を選択します。A1セルの塗りつぶし色を置換するためA1セルを選択しました。

書式を選択

次に、置換後の文字色を設定します。「置換後の文字列」の【書式】ボタンを押します。

塗りつぶし色を選択

「書式の検索」ダイアログボックスが表示されました。①【塗りつぶし】タブ、②【背景色】から任意の色の順に選択し、③【OK】ボタンを押します。今回は水色を選択しました。

置換を選択

「置換と検索」ダイアログボックスに戻りました。【置換】ボタンを押します。

置換完了

セルの塗りつぶし色が置換されました。

置換ができないときの対処法

置換対象の文字があるにもかかわらず、置換ボタンを押してもエラーが出て置換できないことがあります。その場合は、下記の2つのことを確認してみてください。

範囲指定を確認する

エラー

セルを選択した状態で置換ボタンを押すと、選択中のセル内にある文字のみが検索対象となり、同じシート内であっても選択範囲外にある文字が置換されません。図では選択範囲内に指定の文字列「大阪」がないためエラーが出ています。

置換完了

この場合は選択範囲を解除してから【置換】ボタンを押すと置換ができるようになります。

チェックボックスにチェックが入っていないことを確認する

チェックボックス

詳細な検索条件を指定する3つのチェックボックスがあります。これらのチェックボックスにチェックが入っていることで、条件の指定がない場合はチェックを外して置換するとよいでしょう。図では「大文字と小文字を区別する」チェックが入っているため置換ができない状態になっています。

置換完了

①チェックを外し、②【置換】ボタンを押すと、置換されます。

このページを見ている人におすすめの商品

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top