【Excel VBA】複数文字列の一括置換ツール（コピペですぐ使える）
日常業務を行うにあたって、エクセル内のある大量の文字列を置換する必要があるけど、どうしよう。と困ったことはないでしょうか。
そんなときに、ファイル内の複数の文字列などをクリック一回で置換することができたら業務効率化が進むと思いツールの作成を思い立ちました。
エクセルの表に検索する文字列と置換する文字列をいくつも指定して実行すると、指定した全ての文字列を全て一括で置換してくれるツールをマクロで作成しましたので、是非ご活用頂けたらと思います。
また、このツールの上位互換として範囲を限定して一括置換を実行できるツールも作成しましたので、よろしければご参照ください。
＜更新＞
（2022年3月27日：更新） 検索するワードの条件に、オプションとして『完全一致のみを対象とする』『大文字と小文字を区別する』『半角と全角を区別する』を設定できるように追加しました。
（2022年5月21日：更新） 指定したファイルに対して必要に応じてシートを指定できるように変更しました。
記事の目次
『複数文字列の一括置換ツール』の概要
エクセルの表内に検索する文字列と置換する文字列を記載した状態で、対象のファイルをダイアログで指定すると、そのファイル内の全てのシートを対象に検索する文字列を置換する文字列に変換してくれます。
ExcelにてVBAコードよりマクロを作成し、ボタン（図形にて作成）をクリックすることでマクロを実行させて一括置換処理を実施します。
当ツールの使用方法
基本的な機能、操作方法の説明
１．一括置換ツールのVBAコードを含んだExcelファイル（***.xlsm）を作成して、そのExcelファイルを開きます。
２．検索する文字列とそれぞれの置換する文字列を入力します。
３．一括置換ボタンを押した後に、対象のファイルを選択します。
指定した全ての文字列が置換する文字列に置換されています。
留意事項
当ツールを使用するにあたって、使用可能なルールがありますので、以下を確認してください。
ワイルドカードは使用可能だが、正規表現は使用不可
ワイルドカード（*,?）等を使用した前方一致、中間一致、後方一致などは利用可能ですが、正規表現である（[ ],^,$,|,+）等は使用できません。
ワイルドカードは指定したとおりに置換されていますが、正規表現を使用した場合は、正規表現文字はそのままの文字として完全に一致する文字に対して置換がされます。
＜Option＞
Option1：対象シートを指定
対象をのファイル内のシートを指定する場合は、シート名を記載します。
※指定がない場合は、空欄にすると全てのシートを対象にします。
Option2：条件の適用
それぞれの検索条件に対して、「完全一致のみを対象とする」、「大文字と小文字を区別する」、「半角と全角を区別する」を指定することができます。
また、実行した結果として、指定した文字列の置換が全て正常に作成されたか、ファイル内に指定した文字列に存在しないものがあったか、どちらかのメッセージが表示されますので確認してください。
使用する際の事前準備
このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。
以下にその手順を説明していきます。
準備の手順
１．「サンプルのダウンロードはこちら」からサンプル（Excelファイル）をダウンロードし、ファイルを開きます。
※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています
２．ダウンロードしたExcelファイルの上部にあるリボンの「開発」タブから「visual basic」を選択して、VBE（visual basic for application）を起動させます。
リボンに「開発」タブが表示されていない場合は、以下を参照ください。
（参考サイト：記事「Excel VBAを始める前に、最初にやっておくべき初期設定内容はこれ」の『開発』タブを表示させる）
３．「プロジェクト」に「標準モジュール」を追加して、追加された「Module1」にVBAコードを記載します。
※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます
Option Explicit
'-----（設定値）------------------------
Private Const DATA_START_ROW = 12 '１．検索置換表のデータが開始する行番号
Private Const SHEET_NAME_POINT = "C6" '２．対象シート名のセル位置
'-----（メッセージ）-------------------
Private Const Msg1 = "一括置換する対象のファイルを選択してください。"
Private Const Msg2 = "一括置換処理が正常に終了しました。"
Private Const Msg3 = "指定したシートが存在しません。"
Private Const WMsg1 = "検索する文字列を設定してください。"
Private Const WMsg2 = "がファイル内に見つかりませんでした｡ "
Private Const EMsg1 = "予期せぬエラーが発生しました"
'---------------------------------------
'==========================================================
'一括置換実行ボタンを押した時に実行される一括置換メイン処理
'==========================================================
Sub 一括置換実行_Click()
On Error GoTo err
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim filePath As String
Dim fileName As String
Dim tgtSheetNm As String
Dim baseSheet As Worksheet
Set baseSheet = ActiveSheet
Dim tmpSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Dim msg As String
Dim Rng As Range
Dim searchWord As Variant '検索ワード
Dim replaceWord As Variant '置換ワード
Dim allMatchFlg As Boolean '完全一致のみ対象にするフラグ
Dim caseSensitiveFlg As Boolean '大文字と小文字を区別するフラグ
Dim byteFlg As Boolean '全角と半角を区別するフラグ
Dim existFlg As Boolean: existFlg = False '検索ワードが存在するか判断するフラグ
'対象シート名を取得する
tgtSheetNm = baseSheet.Range(SHEET_NAME_POINT).Value
'リストの最終行を検索
lastRow = baseSheet.Cells(Rows.Count, "A").End(xlUp).Row
If lastRow < DATA_START_ROW Then
MsgBox WMsg1
End
End If
'ダイアログの表示処理
filePath = Application.GetOpenFilename(Filefilter:="Microsoft Excelブック,*.xls?,csvファイル,*.csv", Title:=Msg1)
If filePath <> "False" Then
'ファイル名のみを取得する
fileName = Dir(filePath)
Workbooks.Open (filePath), UpdateLinks:=1
'「対象シート名」が空白じゃない場合、対象ファイル内に当該シートが存在するか確認する
If tgtSheetNm <> "" Then
For Each tmpSheet In ActiveWorkbook.Worksheets
If tmpSheet.Name = tgtSheetNm Then existFlg = True
Next tmpSheet
If existFlg = False Then
MsgBox "「" & fileName & "」には" & Msg3, vbInformation
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If
End If
'リストの分だけ置換を繰り返す
For i = DATA_START_ROW To lastRow
searchWord = baseSheet.Cells(i, 1).Value
replaceWord = baseSheet.Cells(i, 2).Value
If baseSheet.Cells(i, 3).Value <> "" Then allMatchFlg = True
If baseSheet.Cells(i, 4).Value <> "" Then caseSensitiveFlg = True
If baseSheet.Cells(i, 5).Value <> "" Then byteFlg = True
existFlg = False
Select Case searchWord
Case "^", "$", "?", "*", "+", ".", "|", "{", "}", "\", "[", "]", "(", ")"
searchWord = "~" & searchWord
End Select
'対象ファイルの全シートを1つずつループして処理する
For Each tmpSheet In ActiveWorkbook.Worksheets
If tgtSheetNm = "" Or (tgtSheetNm <> "" And tgtSheetNm = tmpSheet.Name) Then
'完全一致あり/なし、大文字区別あり/なし、半角区別あり/なしによって８通りに検索条件を変更する
If allMatchFlg And Not caseSensitiveFlg And Not byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlWhole, MatchCase:=False, matchbyte:=False)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlWhole, MatchCase:=False, matchbyte:=False
ElseIf Not allMatchFlg And caseSensitiveFlg And Not byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlPart, MatchCase:=True, matchbyte:=False)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlPart, MatchCase:=True, matchbyte:=False
ElseIf Not allMatchFlg And Not caseSensitiveFlg And byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlPart, MatchCase:=False, matchbyte:=True)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlPart, MatchCase:=False, matchbyte:=True
ElseIf allMatchFlg And caseSensitiveFlg And Not byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlWhole, MatchCase:=True, matchbyte:=False)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlWhole, MatchCase:=True, matchbyte:=False
ElseIf Not allMatchFlg And caseSensitiveFlg And byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlPart, MatchCase:=True, matchbyte:=True)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlPart, MatchCase:=True, matchbyte:=True
ElseIf allMatchFlg And Not caseSensitiveFlg And byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlWhole, MatchCase:=False, matchbyte:=True)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlWhole, MatchCase:=False, matchbyte:=True
ElseIf allMatchFlg And caseSensitiveFlg And byteFlg Then
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlWhole, MatchCase:=True, matchbyte:=True)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlWhole, MatchCase:=True, matchbyte:=True
Else
Set Rng = tmpSheet.Cells.Find(What:=searchWord, _
LookAt:=xlPart, MatchCase:=False, matchbyte:=False)
tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
LookAt:=xlPart, MatchCase:=False, matchbyte:=False
End If
If Not (Rng Is Nothing) Then
existFlg = True
End If
End If
Next
If Not existFlg And InStr(msg, searchWord) = 0 Then
msg = msg & "「" & searchWord & "」" & WMsg2 & vbCr
End If
Next
Workbooks(fileName).Close SaveChanges:=True
If msg = "" Then
MsgBox Msg2
Else
MsgBox msg
End If
'キャンセルが選択された場合はダイアログを閉じる
Else
End
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
err:
MsgBox EMsg1
End Sub
４．ファイルを保存する際は「Excelマクロ有効ブック」を選択して、保存します。
５．右端にある「一括置換実行」と記載された図形にメイン処理「一括置換実行_Click」のマクロを設定します。
５でマクロ登録した図形にカーソルを当てて、指の形になっていたら正常に設定できています。
これで事前準備は完了です。
あとは「当ツールの使用方法」で記載された方法でツールを実行できます。
また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が初心者向けでとても参考になると思いますので、良かったらご参照ください。
なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365 バージョン2006″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。
正常に動作しない場合は、コメントいただければ幸いです。
サンプルのダウンロードはこちら
【Excel VBA】複数文字列の一括置換ツール
上記よりダウンロードして、記事の途中にありましたVBAコードを組み込んでツールを使用してください。
※マクロをインターネットからダウンロードするのはリスクを伴うと思いますので、マクロ入りでなくエクセル形式でアップロードしています。
他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います。
＜このツールが『結構使える！』と思ったら、下のグッドボタンを押していただけたら幸いです＞
1000以上の csv ファイルで、どうしても置換作業が必要になり、目の前が真っ暗になりかけていました。
まさにこういう一括置換マクロが欲しかったのです。
ご提供、ありがとうございます。マクロは csv ファイルでも問題なく作業してくれました。
なお、「Excelマクロ有効ブック」は好きな名前を付けて保存してしまいました。
マクロのタイトルとは別物でしたよね？
管理人のRHです。遅くなり申し訳ありません。
お役に立ちまして、良かったです。
＞なお、「Excelマクロ有効ブック」は好きな名前を付けて保存してしまいました。
＞マクロのタイトルとは別物でしたよね？
上記は、マクロのファイル名のことかと思いますので、好きな名前を付けて頂いて問題ありません。
もしよければ教えて下さい。商品コードで1123456と123456のコードがあり、123456のコードを
123678に変更したいとき、1123456のコードが1123678になります、下6桁が同じです。7桁と6桁の違いを判別する事は可能ですか。
管理人のRHです。遅くなり申し訳ありません。
山本様が実現させたい6桁と7桁を判別するようにツールを変更しました。
更新版のエクセルファイルを見ていただくと、C列に『完全一致のみを対象とする』ことを設定できるようにしました。ここに値を入れると完全一致のみを検索の対象にしますので、6桁と7桁を判別できるようになったかと思います。
ご使用いただき、問題等がありましたらまたお知らせください。
こういうのが欲しかった！
とても重宝させていただいていたのですが、突如使えなくなってしまいました。
ファイルを選択すると強制終了するようになってしまいました・・・。どうしてでしょうか・・・。
管理人のRHです。
MMさん、ツールのご使用とコメントありがとうございます。
急に強制終了されて使えなくなったとのことですが、「予期せぬエラーが発生しました。」というエラーメッセージも表示されない状況とお見受けします。
根本的な解決策にはならないかもしれませんが、いったんPCを再起動して再度ツールを実行してみたら解消されますでしょうか？
それで解消されない場合は、可能でしたら、OSのバージョンとMicrosoft Officeのバージョンを教えていただければと思います。
便利なツールありがとうございます。
ちなみに
A行のある文字を置き換える文字と
B行の同じ文字を置き換える文字がA行で置き換えた文字とは違う文字に置き換えたい場合
行ごとに指定することはできるのでしょうか？
管理人のRHです。
sankさん、ツールのご使用とコメントありがとうございます。
申し訳ありません。
当ツールの現状の機能としては、行数を指定して置換することはできません。
sankさんがおっしゃった行数をそれぞれ指定して置換することができるツールは今後作成して公開しようと思いますので、その際はよろしければお使いください。
わかりました！
ありがとうございました。
管理人のRHです。
sankさんへ、行や列、範囲を限定させて一括置換するツールを作成しましたので下記サイトをご確認ください。
https://resthill.blog/excel-vba-tool14/
必要に応じて、一括置換の検索/置換する範囲を限定できるようなツールとなっています。
不明点等ありましたら、改めてコメントをください。