EXCEL VBA Replace メソッド　複数の文字列の置換（セル範囲・一括変換・置換リスト）

2020年11月1日

EXCEL VBA Replace メソッド　複数の文字列の置換（セル範囲・一括変換・置換リスト）

●はじめに

今回説明するのは、文字列を置換する機能のReplaceメゾットの利用方法を説明いたします。ある指定の文字列を指定して、その文字列を別の文字列に置換（変換）する時にとても便利な機能です。置換する対象として、部分一致・完全一致・大文字・小文字の区別もReplaceメゾットで指定する事で、置換目的に合わせて使い分ける事が出来ます。それでは、サンプルプログラムを交えて順番に説明いたします。

●【EXCEL VBA Replace関数　複数の文字列の置換（セルの値・指定文字列位置・置換リスト・数式の置換）については、下記の参照して下さい】

EXCEL VBA Replace関数　複数の文字列の置換（セルの値・指定文字列位置・置換リスト・数式の置換）

●【EXCEL VBA Substitute メソッド　複数の文字列の置換（大量・一括変換・置換リスト）については、下記の参照して下さい】

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

●【EXCEL VBA データの検索・置き換え方法（テクニック）名字の旧字・新字については、下記の参照して下さい】

EXCEL VBA データの検索・置き換え方法（テクニック）名字の旧字・新字

●書式の説明　（Replaceメゾット）

●　Replaceメゾットを利用するには、下記の通りに設定を行います。

①Replace(What,Replacement)
(検索文字列,置換文字列)

②Replace(What,Replacement,LookAt,SearchOrde,MatchCase,MatchByte
,SearchFormat,ReplaceFormat)
(検索文字列,置換文字列,部分完全一致,検索方向,大小文字区別,半角全角,
書式条件,書式置換）

引数	説明	設定値
What	検索する文字列を指定する。
Replacement	検索一致した文字列と置き換える。
LookAt	部分一致か完全一致を指定する。	xlPart：一部分が一致するセルの検索 xlWhole：完全に一致するセルを検索
SearchOrder	検索方向を指定する。	xlByRows：行方向 xlByColumns：列方向
MatchCase	文字列の大文字と小文字を区別する。	True：区別する False：区別しない
MatchByte	文字列の半角と全角を区別する。	True：区別する False：区別しない
SearchFormat	書式を検索条件に区別する。	True：検索条件にする False：検索条件にしない
ReplaceFormat	書式を置き換えするか指定する。	True：置換する False：置換しない

【使用例①】

Columns(“A”).Replace What:=”EXCEL”, Replacement:=”エクセル”

※A列に”EXCEL”の文字列が有る場合、”エクセル”に置換します。

【使用例②】
Range(“A1:A8″).Replace What:=”日本”, Replacement:=”Japan”

※セルA1～A8の範囲に”日本”の文字列が有る場合、”Japan”に置換します。

【注意点】

※Replaceメゾットは、Replace(What,Replacement)は、必ず設定します。それ以外の設定は、省略する事が出来ます。

Replaceメゾットを利用して、文字列を置換します。（複数置換・一括置換）

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

下記のサンプルプログラムは、複数文字列を一括に置換します。A列に文字列としてExcel・Word・Outlookが複数登録されています。この文字列に対して、エクセル・ワード・アウトルックへ一括変換します。

【プログラムの流れ】
①A列の文字列に対して、置換対象の文字列へ置換します。
・EXCEL　⇒　エクセル
・Word　⇒　ワード
・OutLook　⇒　アウトルック

'
''******** AKIRA55.COM ******* https://akira55.com/replace_m/
'
Sub Replace01() '複数文字列を置換します。

    With Columns("A")
        .Replace what:="EXCEL", replacement:="エクセル"
        .Replace what:="Word", replacement:="ワード"
        .Replace what:="OutLook", replacement:="アウトルック"
    End With

End Sub 
'

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

Sub Replace01() '複数文字列を置換します。

With Columns("A")

.Replace what:="EXCEL", replacement:="エクセル"

.Replace what:="Word", replacement:="ワード"

.Replace what:="OutLook", replacement:="アウトルック"

End With

End Sub

●実行前～実行後　※プログラム実行後、A列の文字列が指定した文字列へ置換されました。
（画面クリックして拡大）

Replaceメゾットを利用して、指定した文字列を除いて削除（置換）します。（一括置換）

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

下記のサンプルプログラムは、A列に東京都の住所一覧が表示されています。この住所一覧に表示されている【東京都】部分のみ一括削除（置換)します。

【プログラムの流れ】
①A列の文字列として登録されている住所に対して、【東京都】のみ削除（置換）します。
・東京都　⇒　””　　（削除）

'
''******** AKIRA55.COM ******* https://akira55.com/replace_m/
'
Sub Replace02A() '指定した文字列を削除します。

    With Columns("A")
        .Replace what:="東京都", replacement:="", LookAt:=xlPart '東京都を削除：部分一致（ LookAt:=xlPart）
    End With
End Sub
'

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

Sub Replace02A() '指定した文字列を削除します。

With Columns("A")

.Replace what:="東京都", replacement:="", LookAt:=xlPart '東京都を削除：部分一致（ LookAt:=xlPart）

End With

End Sub

●実行前～実行後　※プログラム実行後、A列に登録されている住所内の【東京都】部分のみ削除されました。
（画面クリックして拡大）

Replaceメゾットを利用して、文字列内の空白文字（全角・半角）を削除（置換）します。（一括置換）

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

下記のサンプルプログラムは、B列に登録されている【氏名】とC列に登録されている【住所】に空白文字がバラバラに挿入されています。この空白文字（空白全角・空白半角）を削除（取り除く）するサンプルプログラムです。

【プログラムの流れ】
①B列の文字列として登録されている【氏名】に対して、空白文字のみ削除（置換）します。
②C列の文字列として登録されている【住所】に対して、空白文字のみ削除（置換）します。
・空白文字　⇒　””　　（削除）

'
''******** AKIRA55.COM ******* https://akira55.com/replace_m/
'
Sub Replace02B()  '空白文字（全角・半角）を削除します。

    With Columns("B:C")
        .Replace what:=" ", replacement:="", LookAt:=xlPart '空白半角削除：部分一致（ LookAt:=xlPart）
        .Replace what:="　", replacement:=""  '空白全角削除
    End With

End Sub
'

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

Sub Replace02B() '空白文字（全角・半角）を削除します。

With Columns("B:C")

.Replace what:=" ", replacement:="", LookAt:=xlPart '空白半角削除：部分一致（ LookAt:=xlPart）

.Replace what:="　", replacement:="" '空白全角削除

End With

End Sub

●実行前～実行後　※プログラム実行後、B列の「氏名」とC列の「住所」部分に挿入されている空白文字を一括で削除されました。
（画面クリックして拡大）

Replaceメゾットを利用して、置換リストより指定（コード）に一致した文字列へ置換します。

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

下記のサンプルプログラムは、Replaceメゾットを利用して、置換リストより指定（コード）に一致した文字列へ置換するサンプルプログラムです。下記のサンプルプログラムは、シート内に勘定科目の置換リスト（対照表）があります。この置換リストを利用して登録されているコードに対して一致する勘定科目へ置換します。

【プログラムの流れ】
①A列の最終行を取得します。【勘定科目・コード】
②E列の最終行を取得します。【勘定科目】・対照表
③置換対象をA列に指定します。
④A列にE列と一致するコードがあれは、F列の文字列をA列に置き換えます。
⑤A列の最終行まで繰り返す。

'
''******** AKIRA55.COM ******* https://akira55.com/replace_m/
'
Sub Replace03() '置換リストを作成して指定コードに一致した文字列に置換します。

    Dim I, L, lRow, mRow As Long
    Dim Moji As String

    lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得
    mRow = Cells(Rows.Count, "E").End(xlUp).Row 'E列の最終行を取得

    For I = 2 To lRow  'A列の最終行まで繰り返す。
    
        For L = 2 To mRow  'E列の最終行まで繰り返す（勘定科目コード表）
            With Columns("A")  'A列を指定（置換対象）
                .Replace what:=Cells(L, "E"), replacement:=Cells(L, "F") '勘定科目コード⇒勘定科目へ置換
            End With
        Next L
    Next I
End Sub
’

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

Sub Replace03() '置換リストを作成して指定コードに一致した文字列に置換します。

Dim I, L, lRow, mRow As Long

Dim Moji As String

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得

mRow = Cells(Rows.Count, "E").End(xlUp).Row 'E列の最終行を取得

For I = 2 To lRow 'A列の最終行まで繰り返す。

For L = 2 To mRow 'E列の最終行まで繰り返す（勘定科目コード表）

With Columns("A") 'A列を指定（置換対象）

.Replace what:=Cells(L, "E"), replacement:=Cells(L, "F") '勘定科目コード⇒勘定科目へ置換

End With

Next L

Next I

End Sub

’

●実行前～実行後　※プログラム実行後、勘定科目の置換リスト（対照表）のコード参照に、一致する勘定科目名へ一括変換されました。
（画面クリックして拡大）

Replaceメゾットを利用して、文字列内の改行を一括削除します。

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

下記のサンプルプログラムは、指定した列の文字列内の改行を一括削除を行うサンプルプログラムです。C列に【メールアドレス】が登録されています。このC列に登録されているメールアドレスは、部分的に改行されています。このC列の改行を全て取り除くサンプルプログラムです。

【プログラムの流れ】
①C列（メールアドレス）の文字列内の改行を一括削除します。
.Replace what:=vbLf, replacement:=””
※【　vbLf　】は、改行　を　””　は、削除を意味します。

'
''******** AKIRA55.COM ******* https://akira55.com/replace_m/
'
Sub Replace04() '文字列内の改行を一括削除します。

    With Columns("C")
        .Replace what:=vbLf, replacement:="", LookAt:=xlPart  '改行削除：部分一致（ LookAt:=xlPart）
    End With

End Sub
’

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

Sub Replace04() '文字列内の改行を一括削除します。

With Columns("C")

.Replace what:=vbLf, replacement:="", LookAt:=xlPart '改行削除：部分一致（ LookAt:=xlPart）

End With

End Sub

’

●実行前～実行後　※プログラム実行後、指定したC列「メールアドレス」内の改行が全て削除する事が出来ました。
（画面クリックして拡大）

Replaceメゾットを利用して、別シートに置換リストを作成して、置換リストに該当するコードを元に複数データの置換処理を行います。

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

下記のサンプルプログラムは、別シートに置換リストを作成して、データ参照元シートと置換リストに該当するコードと文字列変換を行い、複数のデータに対して置換処理を行います。２つのワークシート「社員情報」と「コード一覧」があります。「社員情報」には、「社員番号」・「性別」・「所属コード」・「郵便番号」がコード（数値）が登録されています。この登録さている数値は、ワークシート「コード一覧」にて、コードに対する置換リストが作成されています。コードに一致するデータを文字列として、ワークシート「社員情報」に文字列として一括置換を行います。

【プログラムの流れ】
①シート【社員情報】１行目の最終列を取得
②シート【社員情報】A列～D列まで繰り返します。
③シート【コード一覧】列ごとの最終行を取得します。（A列⇒C列⇒E列⇒G列と順番に取得））
④対象の置換列の最終行まで繰り返す。
⑤シート「社員情報」置換列を指定（１列目～４列目まで）
⑥指定列のコードから文字列へ置換
⑦置換の対象列を変更（⇒性別コード⇒所属コード⇒郵便番号）

'
''******** AKIRA55.COM ******* https://akira55.com/replace_m/
'
Sub Replace05() '別シートに置換リストを作成して、コードと一致した文字列を複数データに対して置換処理を行います。


    Dim ws01, ws02 As Worksheet
    Dim L, M, N, lCol, mRow As Long
    
    Set ws01 = Worksheets("社員情報")
    Set ws02 = Worksheets("コード一覧")

    lCol = ws01.Cells(1, Columns.Count).End(xlToLeft).Column 'シート「社員情報」1行目の最終列を取得
    
    M = 1 'シート「コード一覧」1列目を指定（A列：スタート列）

    For N = 1 To lCol  'lRow  'シート「社員情報」の最終列まで繰り返す。（A列⇒B列⇒C列⇒D列）
        
        mRow = ws02.Cells(Rows.Count, M).End(xlUp).Row     'シート「コード一覧」列ごとの最終行を取得（A列⇒C列⇒E列⇒G列と順番に取得）
        
        For L = 2 To mRow  '対象の置換列の最終行まで繰り返す。
            With ws01.Columns(N)  'シート「社員情報」置換列を（N）指定
                .Replace what:=ws02.Cells(L, M), replacement:=ws02.Cells(L, M + 1) '指定列対して文字列の置換を行います。
            End With
        Next L
    
    M = M + 2 'シート「コード一覧」＋２列を指定（⇒C列⇒E列⇒G列）

    Next N

End Sub
'