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

 

 

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

 

 

 ●はじめに

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

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

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

 

 

 

 ●書式の説明

● 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列に文字列を入力します。

 

 

 

 

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

 

 

 

 

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

 

 

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

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

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

 

 

 

 

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

 

 

 

 

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

 

 

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

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

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

 

 

 

 

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

 

 

 

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

 

 

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

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

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

 

 

 

 

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

 

 

 

 

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

 

 

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

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

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

 

 

 

 

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

 

 

 

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

 

 

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

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

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

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

 

 

 

 

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

 

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