Categories: VBA基礎

EXCEL VBA Substitute メソッド　複数の文字列の置換（大量・一括変換・置換リスト）

●はじめに

今回説明するのは、WorksheetFunction.Substitute メソッドの利用方法を説明いたします。指定した文字列に対して文字を置換えを行う時に使用します。Replaceメゾットでも文字列を置換する事は出来ますが、今回は、Substitute メソッドを利用した置換・一括変換・置換リストを元にした置換方法をサンプルプログラムを交えて説明いたします。

●【Replaceを利用したデータの置き換え方法は、下記を参照して下さい】

●書式の説明

●　WorksheetFunction.Substitute メソッドを利用するには、下記の通りに設定を行います。

【使用例】

Range(“A1”) = WorksheetFunction.Substitute(Range(“A1”), “エクセル”, “EXCEL“,2)

（文字列, 検索文字列, 置換文字列, 置換対象）
※文字列・・・置換対象文字列
※検索文字列・・・検索文字列を指定
※置換文字列・・・検索文字列⇒置換文字列を指定
※置換対象・・・置換対象を指定（検索文字列が出現した時の何番目を置換えるか指定（数値）します）
（省略可能：省略した場合は、文字列の全てが対象となります）

【実行後】
Range(“A1”) = WorksheetFunction.Substitute(Range(“A1”), “エクセル”, “EXCEL“,2)
※セル「A1」の結果が下記の通りになります。

※実行後：検索文字列として2回目の「エクセル」が「EXCEL」に置換されました。

Substitute メソッドを使って文字列を一括変換します。

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、Substitute メソッドを使って文字列を一括変換します。下図に、書籍一覧があります。この書籍一覧にある書籍名の「エクセル」⇒「EXCEL」へ文字列の一括変換を行います。

【プログラムの流れ】
①A列の最終行を取得します。
②A列の2行目から最終行まで繰り返します。
③A列の文字列内の「エクセル」⇒「EXCEL」へ変換します。

【プログラム実行条件】
・A列に文字列を入力します。

'
''******** AKIRA55.COM ******* https://akira55.com/substitute/
'

Sub SUBSTITUTE01() '文字列の置き換えします。一括変換

    
    Dim I, lRow As Long
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得
    
    For I = 2 To lRow  'A列の最終行まで繰り返します。
        
        Cells(I, "A") = WorksheetFunction.Substitute(Cells(I, "A"), "エクセル", "EXCEL")  'A列の文字列を置換します。（エクセル⇒EXCEL）
        
    Next I

End Sub
'

●実行前～実行後　※プログラム実行後、A列に登録されている書籍一覧にある文字列「エクセル」が⇒「EXCEL」へ全て置換されました。
（画面クリックして拡大）

Substitute メソッドを使って文字列内の指定番目に見つかる検索文字を置換します。

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、Substitute メソッドを使って文字列を一括変換しますが、今回は、文字列内の複数置換対象が有る場合、左から何番目を数値にて指定する事で、指定した何番目のみ置換を行います。

【プログラムの流れ】
①A列の最終行を取得します。
②2行目から最終行まで繰り返します。
③A列の文字列内の左側から2番目の「a」を「A」に置換します。

'
''******** AKIRA55.COM ******* https://akira55.com/substitute/
'
Sub SUBSTITUTE02()  '何番目に見つかった文字列を置き換えます。

    
    Dim I, lRow As Long
    Dim Moji As String
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得する。
    
    For I = 2 To lRow 'A列の最終行まで繰り返します。
        
        Moji = WorksheetFunction.Substitute(Cells(I, "A"), "a", "A", 2) '2番名に見つかった文字を”A”（大文字）にします。
        
        Cells(I, "B") = Moji  '同行のB列に転記します。
        
    Next I
    
End Sub
'

●実行前～実行後　※プログラム実行後、A列にある文字列の２番目の「a」を「A」（大文字）に置換しました。
（画面クリックして拡大）

Substitute メソッドを使って文字列内の空白を一括削除します。（空白半角・空白全角）

●プログラム説明　（サンプル③）

下記のサンプルプログラムは、Substitute メソッドを使って文字列内の空白文字を一括に削除するサンプルプログラムです。氏名（B列）・住所（C列）に空白文字（全角・半角）が部分的に挿入されています。この空白文字を一括で削除するサンプルプログラムです。

【プログラムの流れ】
①A列の最終行を取得します。
②2行目から最終行まで繰り返します。
③B列とC列の文字列内の空白文字（全角・半角）を削除します。

'
''******** AKIRA55.COM ******* https://akira55.com/substitute/
'
Sub SUBSTITUTE03()   '文字列内の空白（全角・半角）削除
    
    Dim I, L, lRow As Long
    Dim Moji As String
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。
    
    For I = 2 To lRow  'A列の最終行まで繰り返します。
        
        For L = 2 To 3  '２～３は、セルのB列～C列を表しています。
        
            Moji = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(I, L), " ", ""), "　", "")
            '文字列内の空白文字を削除します。
            Cells(I, L) = Moji
        
        Next L
    Next I
End Sub
'

●実行前～実行後　※プログラム実行後、B列（名前）・C列（住所）内に有る空白文字を削除されました。
（画面クリックして拡大）

Substitute メソッドを使って文字列内の改行を一括削除します。（複数改行を一括削除）

●プログラム説明　（サンプル④）

下記のサンプルプログラムは、Substitute メソッドを使って文字列内の改行を一括に削除するサンプルプログラムです。メールアドレス（C列）に改行表示されているので、このC列（メールアドレス）の改行を全て削除するサンプルプログラムです。

【プログラムの流れ】
①A列の最終行を取得します。
②2行目から最終行まで繰り返します。
③C列（メールアドレス）の文字列内の改行を全て削除します。

'
''******** AKIRA55.COM ******* https://akira55.com/substitute/
'
Sub SUBSTITUTE04()    '文字列の改行を削除（置換）
    
    Dim I, lRow As Long
    Dim Moji As String
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得
    
    For I = 2 To lRow  'A列の最終行まで繰り返します。

        Moji = WorksheetFunction.Substitute(Cells(I, "C"), vbLf, "") '改行を削除（置換）

        Cells(I, "C") = Moji  'C列に改行を削除したメールアドレス転記（戻す）
        
    Next I

End Sub
'

●実行前～実行後　※プログラム実行後、C列（メールアドレス）の改行全てが削除されました。
（画面クリックして拡大）

Substitute メソッドを使って複数の検索一致した文字列（2種類）ごとに判定して置換処理します。

●プログラム説明　（サンプル⑤）

下記のサンプルプログラムは、Substitute メソッドを使って複数の検索一致した文字列（２種類）ごとに判定して置換処理を行うサンプルプログラムです。C列（性別）に【Woman・Man】のいずれかが入力されています。この2種類（Woman⇒女性・Man⇒男性）の文字列ごとに置換処理を行います。

【プログラムの流れ】
①A列の最終行を取得します。
②2行目から最終行まで繰り返します。
③C列（性別）「Woman・Man」の2種類の文字列ごとに「女性・男性」を同時判定します。

'
''******** AKIRA55.COM ******* https://akira55.com/substitute/
'
Sub SUBSTITUTE05()  '複数の検索一致した文字列（2種類）ごとに置換処理します。

    Dim I, lRow  As Long

    lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得
    
    For I = 2 To lRow  'A列の最終行まで繰り返します。
    
        Cells(I, "C") = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(I, "C"), "Woman", "女性"), "Man", "男性")
        'C列の文字列に対して複数（2種類）の検索一致した文字列ごとに置換処理（Woman⇒女性・Man⇒男性）
        
    Next I

End Sub
'

●実行前～実行後　※プログラム実行後、C列に「Woman・Man」いずれかの文字列が登録されています。この文字列に対して複数（2種類）の検索一致した文字列ごとに置換処理されました。
・【　Woman　⇒　女性　】
・【　Man　⇒　男性　】
（画面クリックして拡大）

Substitute メソッドを使って置換リストを作成して複数の文字列を置換します。置換結果を別ワークシートに転記

●プログラム説明　（サンプル⑥）

下記のサンプルプログラムは、Substitute メソッドを使って置換リストを作成して複数の文字列を置換処理を連続に実行します。置換結果は、置換リストとは別シートに転記するサンプルプログラムです。

【プログラムの流れ】
①ワークシート「郵便番号」A列の最終行を取得します。
②ワークシート「社員情報」A列の最終行を取得します。
③ワークシート「社員情報」2行目から最終行まで繰り返します。（※社員全員分）
④ワークシート「社員情報」のE列「郵便番号」とワークシート「郵便番号」のA列を検索します。
⑤「郵便番号」が一致した場合、一致したワークシート「郵便番号」のB列「都道府県」・C列「区」・D列「地名」を取得します。
⑥ワークシート「社員情報」のF列「住所」へ一致して取得した住所情報をF列に転記します。

【プログラム実行条件】
・ワークシート「郵便番号」・・・郵便番号情報を作成記載します。
・ワークシート「社員情報」・・・社員情報情報を作成記載します。

'
''******** AKIRA55.COM ******* https://akira55.com/substitute/
'
Sub SUBSTITUTE06()  '置換リストを作成して複数の文字列を置換します。置換結果を別ワークシートに転記

    Dim ws01, ws02 As Worksheet
    Dim I, L, lRow, mRow As Long
    
    
    Set ws01 = Worksheets("郵便番号")   'シート「郵便番号」にセット
    Set ws02 = Worksheets("社員情報")   'シート「社員情報」にセット
    
    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row 'シート「郵便番号」A列の最終行を取得
    mRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row  'シート「社員番号」A列の最終行を取得
    
    With ws01
        For I = 2 To lRow '社員情報分繰り返す。
        
            For L = 2 To lRow  '郵便番号の最終行まで繰り返します。
                ws02.Cells(I, "F") = WorksheetFunction.Substitute(ws02.Cells(I, "E"), .Cells(L, "A"), .Cells(L, "B") & .Cells(L, "C") & .Cells(L, "D"))
                '該当する郵便番号に応じて住所をシート「人事情報」F列に転記します。
            
            If ws02.Cells(I, "E") <> ws02.Cells(I, "F") Then Exit For '
            
            Next L
        Next I
    End With

End Sub
'

●実行前～実行後　※プログラム実行後、ワークシート「社員情報」のE列の「郵便番号」を元に、ワークシート「郵便番号」から一致する郵便番号から住所情報を取得して、ワークシート「社員情報」の「住所」へ転記します。
（画面クリックして拡大）

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

AKIRA