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

 

 

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

 

 

 ●はじめに

今回説明するのは、文字列を置換する機能のReplaceメゾットの利用方法を説明いたします。ある指定の文字列を指定して、その文字列を別の文字列に置換(変換)する時にとても便利な機能です。置換する対象として、部分一致・完全一致・大文字・小文字の区別も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 ⇒ アウトルック

 

 

 

 

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

 

 

 

 

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

 

 

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

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

 

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

 

 

 

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

 

 

 

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

 

 

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

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

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

 

 

 

 

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

 

 

 

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

 

 

 

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

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

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

 

 

 

 

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

 

 

 

 

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

 

 

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

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

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

 

 

 

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

 

 

 

 

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

 

 

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

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

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

 

 

 

 

  ●実行前~実行後 ※プログラム実行後、別シート「コード一覧」にある置換リストを元に、コードと一致した文字列に置換しました。
(画面クリックして拡大)

 

 

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