【Excel VBA】複数文字列の一括置換ツール(コピペですぐ使える)

日常業務を行うにあたって、エクセル内のある大量の文字列を置換する必要があるけど、どうしよう。と困ったことはないでしょうか。

 

そんなときに、ファイル内の複数の文字列などをクリック一回で置換することができたら業務効率化が進むと思いツールの作成を思い立ちました。

 

エクセルの表に検索する文字列と置換する文字列をいくつも指定して実行すると、指定した全ての文字列を全て一括で置換してくれるツールをマクロで作成しましたので、是非ご活用頂けたらと思います。

 

また、このツールの上位互換として範囲を限定して一括置換を実行できるツールも作成しましたので、よろしければご参照ください。

 

<更新>

(2022年3月27日:更新) 検索するワードの条件に、オプションとして『完全一致のみを対象とする』『大文字と小文字を区別する』『半角と全角を区別する』を設定できるように追加しました。

(2022年5月21日:更新) 指定したファイルに対して必要に応じてシートを指定できるように変更しました。

 

『複数文字列の一括置換ツール』の概要

エクセルの表内に検索する文字列置換する文字列を記載した状態で、対象のファイルをダイアログで指定すると、そのファイル内の全てのシートを対象に検索する文字列を置換する文字列に変換してくれます。

 

ExcelにてVBAコードよりマクロを作成し、ボタン(図形にて作成)をクリックすることでマクロを実行させて一括置換処理を実施します。

 

当ツールの使用方法

基本的な機能、操作方法の説明

1.一括置換ツールのVBAコードを含んだExcelファイル(***.xlsm)を作成して、そのExcelファイルを開きます。

 

2.検索する文字列とそれぞれの置換する文字列を入力します。

 

3.一括置換ボタンを押した後に、対象のファイルを選択します。

 

指定した全ての文字列が置換する文字列に置換されています。

 

留意事項

当ツールを使用するにあたって、使用可能なルールがありますので、以下を確認してください。

 

ワイルドカードは使用可能だが、正規表現は使用不可

ワイルドカード(*,?)等を使用した前方一致、中間一致、後方一致などは利用可能ですが、正規表現である([ ],^,$,|,+)等は使用できません。

 

ワイルドカードは指定したとおりに置換されていますが、正規表現を使用した場合は、正規表現文字はそのままの文字として完全に一致する文字に対して置換がされます。

 

Option

Option1:対象シートを指定

対象をのファイル内のシートを指定する場合は、シート名を記載します。

※指定がない場合は、空欄にすると全てのシートを対象にします。

 

Option2:条件の適用

それぞれの検索条件に対して、「完全一致のみを対象とする」、「大文字と小文字を区別する」、「半角と全角を区別する」を指定することができます。

 

また、実行した結果として、指定した文字列の置換が全て正常に作成されたかファイル内に指定した文字列に存在しないものがあったか、どちらかのメッセージが表示されますので確認してください。

 

使用する際の事前準備

このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。

 

以下にその手順を説明していきます。

 

準備の手順

1.サンプルのダウンロードはこちら」からサンプル(Excelファイル)をダウンロードし、ファイルを開きます。

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています

 

2.ダウンロードしたExcelファイルの上部にあるリボンの「開発」タブから「visual basic」を選択して、VBE(visual basic for application)を起動させます。

 

リボンに「開発」タブが表示されていない場合は、以下を参照ください。

(参考サイト:記事「Excel VBAを始める前に、最初にやっておくべき初期設定内容はこれ」の『開発』タブを表示させる

 

3.「プロジェクト」に「標準モジュール」を追加して、追加された「Module1」にVBAコードを記載します。

 

※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます

Option Explicit

'-----(設定値)------------------------
Private Const DATA_START_ROW = 12               '1.検索置換表のデータが開始する行番号
Private Const SHEET_NAME_POINT = "C6"           '2.対象シート名のセル位置

'-----(メッセージ)-------------------
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
                
                    '完全一致あり/なし、大文字区別あり/なし、半角区別あり/なしによって8通りに検索条件を変更する
                    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

 

4.ファイルを保存する際は「Excelマクロ有効ブック」を選択して、保存します。

 

5.右端にある「一括置換実行」と記載された図形にメイン処理「一括置換実行_Click」のマクロを設定します。

5でマクロ登録した図形にカーソルを当てて、指の形になっていたら正常に設定できています。

 

これで事前準備は完了です。

あとは「当ツールの使用方法」で記載された方法でツールを実行できます。

 

また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が初心者向けでとても参考になると思いますので、良かったらご参照ください。

 

なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365 バージョン2006″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。

 

正常に動作しない場合は、コメントいただければ幸いです。

 

サンプルのダウンロードはこちら

【Excel VBA】複数文字列の一括置換ツール 

上記よりダウンロードして、記事の途中にありましたVBAコードを組み込んでツールを使用してください。

※マクロをインターネットからダウンロードするのはリスクを伴うと思いますので、マクロ入りでなくエクセル形式でアップロードしています。

 

他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います

 

 

<このツールが『結構使える!』と思ったら、下のグッドボタンを押していただけたら幸いです>

【Excel VBA】複数文字列の一括置換ツール(コピペですぐ使える)” に対して10件のコメントがあります。

  1. GO より:

    1000以上の csv ファイルで、どうしても置換作業が必要になり、目の前が真っ暗になりかけていました。
    まさにこういう一括置換マクロが欲しかったのです。

    ご提供、ありがとうございます。マクロは csv ファイルでも問題なく作業してくれました。

    なお、「Excelマクロ有効ブック」は好きな名前を付けて保存してしまいました。
    マクロのタイトルとは別物でしたよね?

  2. RH より:

    管理人のRHです。遅くなり申し訳ありません。
    お役に立ちまして、良かったです。

    >なお、「Excelマクロ有効ブック」は好きな名前を付けて保存してしまいました。
    >マクロのタイトルとは別物でしたよね?

    上記は、マクロのファイル名のことかと思いますので、好きな名前を付けて頂いて問題ありません。

  3. 山本 より:

    もしよければ教えて下さい。商品コードで1123456と123456のコードがあり、123456のコードを
    123678に変更したいとき、1123456のコードが1123678になります、下6桁が同じです。7桁と6桁の違いを判別する事は可能ですか。

    1. RH より:

      管理人のRHです。遅くなり申し訳ありません。

      山本様が実現させたい6桁と7桁を判別するようにツールを変更しました。
      更新版のエクセルファイルを見ていただくと、C列に『完全一致のみを対象とする』ことを設定できるようにしました。ここに値を入れると完全一致のみを検索の対象にしますので、6桁と7桁を判別できるようになったかと思います。

      ご使用いただき、問題等がありましたらまたお知らせください。

  4. MM より:

    こういうのが欲しかった!
    とても重宝させていただいていたのですが、突如使えなくなってしまいました。
    ファイルを選択すると強制終了するようになってしまいました・・・。どうしてでしょうか・・・。

    1. RH より:

      管理人のRHです。
      MMさん、ツールのご使用とコメントありがとうございます。

      急に強制終了されて使えなくなったとのことですが、「予期せぬエラーが発生しました。」というエラーメッセージも表示されない状況とお見受けします。

      根本的な解決策にはならないかもしれませんが、いったんPCを再起動して再度ツールを実行してみたら解消されますでしょうか?

      それで解消されない場合は、可能でしたら、OSのバージョンとMicrosoft Officeのバージョンを教えていただければと思います。

  5. sank より:

    便利なツールありがとうございます。
    ちなみに
    A行のある文字を置き換える文字と
    B行の同じ文字を置き換える文字がA行で置き換えた文字とは違う文字に置き換えたい場合
    行ごとに指定することはできるのでしょうか?

    1. RH より:

      管理人のRHです。
      sankさん、ツールのご使用とコメントありがとうございます。

      申し訳ありません。
      当ツールの現状の機能としては、行数を指定して置換することはできません。

      sankさんがおっしゃった行数をそれぞれ指定して置換することができるツールは今後作成して公開しようと思いますので、その際はよろしければお使いください。

      1. 匿名 より:

        わかりました!
        ありがとうございました。

        1. RH より:

          管理人のRHです。

          sankさんへ、行や列、範囲を限定させて一括置換するツールを作成しましたので下記サイトをご確認ください。

          https://resthill.blog/excel-vba-tool14/

          必要に応じて、一括置換の検索/置換する範囲を限定できるようなツールとなっています。
          不明点等ありましたら、改めてコメントをください。

コメントを残す

CAPTCHA