EXCEL VBA Replace メソッド 複数の文字列の置換（セル範囲・一括変換・置換リスト）
EXCEL VBA Replace メソッド 複数の文字列の置換（セル範囲・一括変換・置換リスト）
今回説明するのは、文字列を置換する機能のReplaceメゾットの利用方法を説明いたします。ある指定の文字列を指定して、その文字列を別の文字列に置換（変換）する時にとても便利な機能です。置換する対象として、部分一致・完全一致・大文字・小文字の区別もReplaceメゾットで指定する事で、置換目的に合わせて使い分ける事が出来ます。それでは、サンプルプログラムを交えて順番に説明いたします。
●【EXCEL VBA Replace関数 複数の文字列の置換（セルの値・指定文字列位置・置換リスト・数式の置換）については、下記の参照して下さい】
●【EXCEL VBA Substitute メソッド 複数の文字列の置換（大量・一括変換・置換リスト）については、下記の参照して下さい】
●【EXCEL VBA データの検索・置き換え方法（テクニック）名字の旧字・新字については、下記の参照して下さい】
● 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 ⇒ アウトルック
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
' ''******** 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 ' |
（画面クリックして拡大）
Replaceメゾットを利用して、指定した文字列を除いて削除（置換）します。（一括置換）
下記のサンプルプログラムは、A列に東京都の住所一覧が表示されています。この住所一覧に表示されている【東京都】部分のみ一括削除（置換)します。
【プログラムの流れ】
①A列の文字列として登録されている住所に対して、【東京都】のみ削除（置換）します。
・東京都 ⇒ ”” （削除）
|
1 2 3 4 5 6 7 8 9 10 |
' ''******** AKIRA55.COM ******* https://akira55.com/replace_m/ ' Sub Replace02A() '指定した文字列を削除します。 With Columns("A") .Replace what:="東京都", replacement:="", LookAt:=xlPart '東京都を削除：部分一致（ LookAt:=xlPart） End With End Sub ' |
（画面クリックして拡大）
Replaceメゾットを利用して、文字列内の空白文字（全角・半角）を削除（置換）します。（一括置換）
下記のサンプルプログラムは、B列に登録されている【氏名】とC列に登録されている【住所】に空白文字がバラバラに挿入されています。この空白文字（空白全角・空白半角）を削除（取り除く）するサンプルプログラムです。
【プログラムの流れ】
①B列の文字列として登録されている【氏名】に対して、空白文字のみ削除（置換）します。
②C列の文字列として登録されている【住所】に対して、空白文字のみ削除（置換）します。
・空白文字 ⇒ ”” （削除）
|
1 2 3 4 5 6 7 8 9 10 11 12 |
' ''******** 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 ' |
（画面クリックして拡大）
Replaceメゾットを利用して、置換リストより指定（コード）に一致した文字列へ置換します。
下記のサンプルプログラムは、Replaceメゾットを利用して、置換リストより指定（コード）に一致した文字列へ置換するサンプルプログラムです。下記のサンプルプログラムは、シート内に勘定科目の置換リスト（対照表）があります。この置換リストを利用して登録されているコードに対して一致する勘定科目へ置換します。
【プログラムの流れ】
①A列の最終行を取得します。【勘定科目・コード】
②E列の最終行を取得します。【勘定科目】・対照表
③置換対象をA列に指定します。
④A列にE列と一致するコードがあれは、F列の文字列をA列に置き換えます。
⑤A列の最終行まで繰り返す。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' ''******** 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 】は、改行 を ”” は、削除を意味します。
|
1 2 3 4 5 6 7 8 9 10 11 |
' ''******** AKIRA55.COM ******* https://akira55.com/replace_m/ ' Sub Replace04() '文字列内の改行を一括削除します。 With Columns("C") .Replace what:=vbLf, replacement:="", LookAt:=xlPart '改行削除：部分一致（ LookAt:=xlPart） End With End Sub ’ |
（画面クリックして拡大）
Replaceメゾットを利用して、別シートに置換リストを作成して、置換リストに該当するコードを元に複数データの置換処理を行います。
下記のサンプルプログラムは、別シートに置換リストを作成して、データ参照元シートと置換リストに該当するコードと文字列変換を行い、複数のデータに対して置換処理を行います。２つのワークシート「社員情報」と「コード一覧」があります。「社員情報」には、「社員番号」・「性別」・「所属コード」・「郵便番号」がコード（数値）が登録されています。この登録さている数値は、ワークシート「コード一覧」にて、コードに対する置換リストが作成されています。コードに一致するデータを文字列として、ワークシート「社員情報」に文字列として一括置換を行います。
【プログラムの流れ】
①シート【社員情報】１行目の最終列を取得
②シート【社員情報】A列～D列まで繰り返します。
③シート【コード一覧】列ごとの最終行を取得します。（A列⇒C列⇒E列⇒G列と順番に取得））
④対象の置換列の最終行まで繰り返す。
⑤シート「社員情報」置換列を指定（１列目～４列目まで）
⑥指定列のコードから文字列へ置換
⑦置換の対象列を変更（⇒性別コード⇒所属コード⇒郵便番号）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
' ''******** 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 ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。