公開日：2021/01/15
更新日：2023/03/03

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

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

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

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

文字列を置換する

検索と置換で置換する

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

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

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

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

置換のショートカットキー（Win/Mac）

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

エクセルで置換（置き換え）するショートカット

関数で文字列を置換する

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

SUBSTITUTE関数

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

ExcelのSUBSTITUTE関数の使い方｜文字列を置換する

REPLACE関数

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

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

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

置換できました。

範囲指定をして置換する

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

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

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

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

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

一括で置換する

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

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

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

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

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

全角を半角に置換する

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

エクセルで全角を半角に変換する方法

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

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

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

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

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

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

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

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

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

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

テキストボックス内の文字列を置換するためには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に置換されたことが確認できます。

複数の文字列を置換する

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

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

ExcelのSUBSTITUTE関数の使い方｜文字列を置換する

マクロ（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つの文字列が置換されました。

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

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

Excelで改行コードを置換する方法

数式を置換する

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

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

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

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

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

空白の置換について

空白を置換する

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

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

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

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

空白にする（文字を削除する）

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

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

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

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

タブ（Tab）を置換する

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

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

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

Tabが削除されました。

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

シート内にあるハイパーリンクを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