日常業務を行うにあたって、エクセル内のある大量の文字列を置換する必要があるけど、どうしよう。と困ったことはないでしょうか。
たくさんのエクセルファイル内にある大量の文字列をたくさん置換する場合、以下の操作を繰り返していないでしょうか。
- 対象のエクセルファイルを開く
- エクセル機能の「検索と置換」を開く
- 検索文字列と置換文字列を入力して「検索」ボタンを押す
- 他の文字列を入力した後に、再度「検索」ボタンを押す
- 一つのエクセルファイルに対して①～④が終わったら、エクセルファイルを閉じる
※①～⑤を対象のエクセルファイル数分だけ繰り返す
―― たくさん置換する文字列があるのに、一つずつ入力して検索なんてしてられない！
―― しかもそれを実行するエクセルファイル分の回数をいちいち開いてられない！
このように思ったのではないでしょうか。
そんなとき、ファイル内の複数の文字列などをクリック一回で置換することができたら業務効率化が進むと思いツールの作成を思い立ちました。
このような場面で、無駄な作業が効率化できる、エクセルの表に検索する文字列と置換する文字列を複数指定して対象のExcelファイルを複数選んで実行すると、『指定した全ての文字列を複数ファイル内で全て一括で置換してくれるマクロツール』をExcel VBAで作成しましたので、是非ご活用頂けたらと思います。
また、このツールの上位互換になる『範囲を限定して複数文字列を一括置換できるマクロツール』も作成しましたので、よろしければご参照ください。
（2022年3月27日：更新）
・検索するワードの条件に、オプションとして『完全一致のみを対象とする』『大文字と小文字を区別する』『半角と全角を区別する』を設定できるように追加しました。
（2022年5月21日：更新）
・指定したファイルに対して必要に応じてシートを指定できるように変更しました。
（2022年8月9日：更新）
・置換対象のファイルを複数選択できるように変更しました。
（2022年11月13日：更新）
・不具合を修正しました。ツールのデザインを変更しました。
『複数文字列の一括置換ツール』の概要
エクセルの表内に検索する文字列と置換する文字列を記載した状態で、対象のファイルをダイアログで指定すると、そのファイル内の全てのシートを対象に検索する文字列を置換する文字列に変換してくれます。
ExcelにてVBAコードよりマクロを作成し、ボタン（図形にて作成）をクリックすることでマクロを実行させて一括置換処理を実施します。
基本的な機能、操作方法の説明
- 一括置換ツールのVBAコードを含んだExcelファイル（***.xlsm）を作成して、そのExcelファイルを開きます。
- 検索する文字列とそれぞれの置換する文字列を入力します。
- 「一括置換を実行」ボタンをクリックします。
- 置換する対象のExcelファイルを選択します。
- 「開く」をクリックします。
指定した全ての文字列が置換する文字列に置換されています。
留意事項
当ツールを使用するにあたって、使用可能なルールがありますので、以下を確認してください。
ワイルドカードは使用OK、正規表現は使用NG
ワイルドカード（*,?）等を使用した前方一致、中間一致、後方一致などは利用可能ですが、正規表現である（[ ],^,$,|,+）等は使用できません。
ワイルドカードは指定したとおりに置換されていますが、正規表現を使用した場合は、正規表現文字はそのままの文字として完全に一致する文字に対して置換がされます。
Option1：対象シートを指定
対象をのファイル内のシートを指定する場合は、シート名を記載します。
※指定がない場合は、空欄にすると全てのシートを対象にします。
Option2：条件の適用
それぞれの検索条件に対して、「完全一致のみを対象とする」、「大文字と小文字を区別する」、「半角と全角を区別する」を指定することができます。
また、実行した結果として、指定した文字列の置換が全て正常に作成されたか、ファイル内に指定した文字列に存在しないものがあったか、どちらかのメッセージが表示されますので確認してください。
使用する際の事前準備
このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得して、Excelファイル内に記載すれば、すぐに当ツールを使用できます。
以下にその手順を説明していきます。
準備の手順
＜手順１＞
- 「サンプルのダウンロードはこちら」からサンプル（Excelファイル）をダウンロードし、ファイルを開きます。
※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています
＜手順２＞
ダウンロードしたExcelファイルを開いて、VBE（Visual Basic Editor）を起動させます。
- Excelファイルの上部にあるリボンの「開発」タブを選択します。
- 「Visual Basic」をクリックして、VBE（Visual Basic Editor）を起動させます。
リボンに「開発」タブが表示されていない場合は、「Excel VBAを始める前に、最初にやっておくべき初期設定内容の『開発』タブを表示させる」を参照ください。
＜手順３＞
- 「Microsoft Excel Object」を右クリックします。
- 「挿入」を選択します。
- 「標準モジュール」をクリックします。
＜手順４＞
- 「Module1」をダブルクリックします。
- 表示されている右側の欄（エディター）に以下のVBAコードを記載します。
VBAのソースコードはこちら
以下のVBAコードをコピーして、「Module1」内のエディターに貼り付けます。
※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます
Option Explicit
'-----（設定値）------------------------
Private Const DATA_START_ROW = 11 '１．検索置換表のデータが開始する行番号
Private Const DATA_START_COL_NUM = 2 '２．検索置換表のデータが開始する列番号
Private Const SHEET_NAME_POINT = "C5" '３．対象シート名のセル位置
'-----（メッセージ）-------------------
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 = True
Application.EnableEvents = True
Dim shapeNm As String: shapeNm = Application.Caller
ActiveSheet.Shapes(shapeNm).Visible = False 'ボタンを非表示にして押し込む動作をつける
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 startDataCol As String
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
startDataCol = Split(Columns(DATA_START_COL_NUM).Address, "$")(2)
'リストの最終行を検索
lastRow = baseSheet.Cells(Rows.Count, startDataCol).End(xlUp).Row
If lastRow < DATA_START_ROW Then
MsgBox WMsg1
baseSheet.Shapes(shapeNm).Visible = True '押し込んだボタンを戻す
End
End If
Dim filePathArr As Variant
Dim filePath As Variant
'ダイアログの表示処理
filePathArr = Application.GetOpenFilename(Filefilter:="Microsoft Excelブック,*.xls?,csvファイル,*.csv", Title:=Msg1, MultiSelect:=True)
Application.ScreenUpdating = False
Application.EnableEvents = False
If IsArray(filePathArr) Then
'取得したファイル数分だけ処理を繰り返す
For Each filePath In filePathArr
'ファイル名のみを取得する
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
baseSheet.Shapes(shapeNm).Visible = True '押し込んだボタンを戻す
Exit Sub
End If
End If
'リストの分だけ置換を繰り返す
For i = DATA_START_ROW To lastRow
searchWord = baseSheet.Cells(i, DATA_START_COL_NUM).Value '検索ワード
replaceWord = baseSheet.Cells(i, DATA_START_COL_NUM + 1).Value '置換ワード
If baseSheet.Cells(i, DATA_START_COL_NUM + 2).Value <> "" Then allMatchFlg = True _
Else allMatchFlg = False '完全一致のみ対象にするフラグ
If baseSheet.Cells(i, DATA_START_COL_NUM + 3).Value <> "" Then caseSensitiveFlg = True _
Else caseSensitiveFlg = False '大文字と小文字を区別するフラグ
If baseSheet.Cells(i, DATA_START_COL_NUM + 4).Value <> "" Then byteFlg = True _
Else byteFlg = False '全角と半角を区別するフラグ
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
Next
If msg = "" Then
MsgBox Msg2
Else
MsgBox msg
End If
End If
baseSheet.Shapes(shapeNm).Visible = True '押し込んだボタンを戻す
Exit Sub
err:
MsgBox EMsg1
baseSheet.Shapes(shapeNm).Visible = True '押し込んだボタンを戻す
End Sub
＜手順５＞
マクロを含んだExcelとして、ファイルを保存します。
- 左上にある「保存」アイコンをクリックします。
- 「ファイルの種類」から「Excelマクロ有効ブック」を選択します。
- 「保存」をクリックします。
＜手順６＞
「一括置換実行」ボタンにメイン処理「一括置換実行_Click」のマクロを設定します。
- 「一括置換実行」ボタンを右クリックします。
- 「マクロの登録」をクリックします。
- 「一括置換実行_Click」を選択します。
- 「OK」をクリックします。
※「一括置換実行」にカーソルを当てて、指の形になっているのを確認します。
これで事前準備は完了です。
あとは「当ツールの使用方法」で記載された方法でツールを実行できます。
また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が実際のVBAコードを書く際に参考になると思いますので、良かったらご参照ください。
なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365 バージョン2006″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。
正常に動作しない場合は、コメントいただければ幸いです。
また、このような業務効率化できるツールを以下に一覧でまとめてありますので、ご興味のある方はご覧ください。
業務効率化ツールの一覧（まとめ）
操作ごとにまとめて一覧で表示してあります。
サンプルのダウンロードはこちら
下記よりExcelファイルをダウンロードして、記事の途中にありました VBAのソースコードをツール内に組み込んで使用してください。
【Excel VBA】複数文字列の一括置換ツール
※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています
他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います。
＜このツールが『結構使える！』と思ったら、下のグッドボタンを押していただけたら幸いです＞
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/
必要に応じて、一括置換の検索/置換する範囲を限定できるようなツールとなっています。
不明点等ありましたら、改めてコメントをください。
初めまして。ツール活用させて頂いています。
1度に複数ファイルで対応できるようにならないかと思っています。
ソースコードの56行目を以下のように変更することで、
選択はできますが、エラーになってしまいます。
filePath = Application.GetOpenFilename(Filefilter:=”Microsoft Excelブック,*.xls?,csvファイル,*.csv”, Title:=Msg1,MultiSelect:=True)
ご対応方法について教えて頂けますでしょうか？
管理人のRHです。
KOMさん、ツールのご使用とコメントありがとうございます。
一度に複数ファイルを選択して一括置換するよう変更する場合は、修正する部分のコードが複数に及びましたので、本日複数ファイルを選択できる仕様として修正版をアップロードしましたので、ご使用していただけたら幸いです。
不具合等がありましたら改めてコメント等をお願いします。
RHさん
対応頂きありがとうございます。
早速使用させて頂いたのですが、ファイル選択時にフォルダ単位でしか選択できなくなり、フォルダを選択しても以下の内容が出てくる形となってしまいます。
↓
赤色で表示された箇所は、下記により正しくファイルを振り分けることができませんでした。
フォルダ名：指定されたフォルダ名は存在していません。
ファイル名：ダイアログで選択したフォルダ内にファイルが存在しないか、振り分け先に同一名所のファイルが存在しています。
管理人のRHです。
KOMさん、ツールのご使用とコメントありがとうございます。
もしかしたらですが、「ファイル自動振り分けツール」のVBAコードを誤ってコピペしてないでしょうか？
このページのVBAコード（以下のURL）をコピーしていただければ動作する可能性がありますので、一度お試しください。
（https://resthill.blog/excel-vba-tool4/#VBA）
RHさん
ご確認ありがとうございます。
ご指摘の通り、別のVBAコードを誤ってコピペしていたようです。
最初に作成したファイルのVBAコードと入れ替えてもうまく動きませんでしたが、エクセルからDLしなおしたところ、正常に作動するようになりました。ありがとうございます！
お世話になります。大変便利なツールでぜひ利用させていただきたいと思っております。
一点質問なのですが、空白セルを特定文字に変換したいのですがどのようにすればよいでしょうか。
いろいろ試してみたのですが、どうしてもできずご相談させていただきました。
大変お手数をおかけ致しますが、ご返信いただければ幸いです。よろしくお願い致します。
管理人のRHです。
YOHさん、ツールのご使用とコメントありがとうございます。
空白セルを特定文字に変換したいということですが、例えばA12セルに「 」（半角空白）を入力し、B12セルに「@」（特定文字）を入力します。
※必要に応じて半角/全角や大文字/小文字などを指定する場合はC12,D12,E12に「〇」を入力します。
その状態で「一括置換実行」をクリックすればYOHさんが思っていることが実現できないでしょうか。
もし実現できない場合は、もう少し詳細な情報を頂ければ幸いです。
早速のご返答ありがとうございます。
せっかくお答えいただきましたが、説明不足で大変申し訳ございませんでした。
空白セル＝なにも入っていない状態のセルということでした。
文字列を変換するマクロなので、変換するためには半角空白などなにか入力する必要がありますでしょうか。
何度も申し訳ございませんが、よろしくお願いします。
管理人のRHです。
YOHさん、コメントありがとうございます。
申し訳ありません、勘違いしていました。空白セル（空文字列）のことですね。
現状のマクロツールの動作は、空白セル（値０の文字列）を「検索する文字列」に入力した状態で実行させると、空白セルが存在する範囲内(空白セルも含めた文字列が入っている行と列内）で、空白セルと何も入っていないセルが全て「置換後の文字列」に置き換えられてしまいます。
おそらくYOHさんが実現したい挙動と違うと思いますので、空白セルのみを対象にするよう改修し、完了しましたら改めてこちらにコメントいたします。
何度も返信いただき、ありがとうございました。
また大変丁寧にご説明いただき、感謝致します。
私もまだまだ勉強不足なので、これからも勉強させて頂きます。
RH様の作成されたツールは利便性もさることながら、デザインも美しいなあと思っております。
これからも何卒よろしくお願い致します。
管理人のRHです。
YOHさん、コメントありがとうございます。
色々とお褒めの言葉を頂きまして感謝いたします。
しかし、YOHさんが実現したかった空白セル（空文字列）を特定文字に置換する処理についてですが、空文字列を見つけることのできる関数がVBAでは存在しないようです。様々な方法を考えてみたのですが、結果としてはVBAでは実装できませんでした。
お力になれず、大変申し訳ありません。
コードに不備があるようなので、報告させて頂きます
allMatchFlg
caseSensitiveFlg
byteFlg
上記フラグについてですが、一度TrueになるFalseにならないように思います。
管理人のRHです。
ゆうきさん、コメントありがとうございます。
おっしゃっていただいた通り不具合でしたので、修正したバージョンをアップロードいたします。
ご指摘いただき、感謝いたします。