EXCEL VBA Instr関数の使い方と活用例(対象文字検索・何番目・文字列を置き換える・該当データを転記)

 

 

EXCEL VBA Instr関数の使い方と活用例(対象文字検索・何番目・文字列を置き換える・該当データを転記)

 

 

 ●はじめに

今回説明するのは、inStr関数について説明を行いたいと思います。InStr関数は、文字列内に特定の文字列が現れる位置を検索するために使用されます。具体的な利用用途としては、データ分析や文字列操作において、指定した文字列が存在するかどうかを判断したり、特定の区切り文字で文字列を分割する際などに利用されます。

●【EXCEL VBA Replace関数については、下記を参照して下さい】

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

●【inStr関数 (Excel)の詳細につきましては、下記を参照して下さい】(Microsoft社 様)】
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/instr-function

 

 

 ●書式の説明

● inStr関数を利用するには、下記の通りに設定を行います。

【構文説明】InStr関数の構文は以下の通りです。

InStr([start,] string1, string2[, compare])

  • start(省略可能):検索を開始する位置。省略した場合は、1から検索が開始されます。
  • string1:検索対象の文字列。
  • string2:検索する文字列。
  • compare(省略可能):比較方法。省略した場合は、バイナリ比較(0)が適用されます。テキスト比較(1)も選択可能です。

【使用例】
InStr関数は業務上、様々なシチュエーションで利用できます。具体的な使用例をいくつか挙げます。

①データの検証:データの品質を確保するために、特定の文字列が含まれているかどうかを確認する際にInStr関数が役立ちます。例えば、メールアドレスの入力値に”@”が含まれているかどうかをチェックする場合などです。

②データの分割:区切り文字を使ってデータを分割する場面でInStr関数が有用です。例えば、CSVファイルのデータを読み込み、カンマで区切られたデータを個々のセルに振り分ける処理などに使用できます。

③検索機能の実装:検索機能を実装する際に、InStr関数を使用して検索対象のデータ内に特定のキーワードが存在するかどうかを判断することができます。

④条件分岐:特定の条件に基づいて処理を分岐させたい場合、InStr関数を使用して文字列内に特定のパターンが存在するかどうかを確認し、処理を制御できます。例えば、ファイル名に特定の接頭辞や接尾辞が付いている場合に特別な処理を行う場合などです。

⑤テキストデータの整形:テキストデータに含まれる特定の文字列を削除、置換、または抽出する場合に、InStr関数を利用して特定の文字列の位置を特定し、操作を行います。

 

 

 

 

EXCEL VBA Instr関数の使い方と活用例 (指定した対象文字列から特定の文字列を探す・何番目)

 

 

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

このVBAコードは、指定された検索対象の文字列(targetString)内に特定の文字列(searchString)が存在するかどうかを確認し、その位置(position)を返すコードです。文字列が見つかった場合、メッセージボックスにその位置を表示し、見つからなかった場合は見つからなかった旨をメッセージボックスに表示します。

【プログラムの流れ】
① 検索対象の文字列(targetString)と検索文字列(searchString)を定義する。
② InStr関数を使って、検索文字列が検索対象の文字列内に存在する位置を取得する。
③ 取得した位置が0より大きい場合(見つかった場合)、メッセージボックスに位置情報を表示する。
④ 位置が0の場合(見つからなかった場合)、メッセージボックスに見つからなかった旨を表示する。

 

 

 

 

 

EXCEL VBA Instr関数の使い方と活用例 (検索・該当したデータを別シートに転記します。)

 

 

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

このVBAコードは、Excelのワークシートから特定の文字列を検索し、検索結果を別のワークシートに転記するプログラムです。具体的には、”データシート”のA2:C最終行までの範囲にあるデータを検索し、”F2″セルに入力された検索文字列がB列の住所データに含まれる行を見つけます。該当する行が見つかった場合、その行のA列からC列までのデータを”結果シート”に転記します。

【プログラムの流れ】
① 入力データのシート(“データシート”)と出力先のシート(“結果シート”)を設定します。
② ワークシート「データシート」の”F2″セルから検索文字列を取得します。
③ ワークシート「結果シート」のデータをクリアします。
④ ワークシート「データシート」の最終行を取得します。
⑤ ”ワークシート「データシート」のA2:C最終行までのデータを配列に格納します。
⑥ 出力用の配列を初期化します。
⑦ 配列に出力データを格納します。
⑧ スクリーン更新をオフにします。
⑨ ”結果シート”に出力データを書き込みます(該当データが1行以上ある場合)。
⑩ スクリーン更新をオンに戻します。
⑪ ワークシート「結果シート」を表示します。
⑫ 検索結果のメッセージを表示します。

【プログラム実行条件・注意事項】
・ワークシート名が正しく設定されていることを確認してください(”データシート”および”結果シート”)。
・検索文字列を入力するセル(”F2″)が正しいことを確認してください。
・データの範囲がA2:C最終行であることを確認してください。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● inStr関数02(サンプルプログラム)

 

 

 

 ●実行前~実行後 ※プログラム実行後、”データシート”から検索文字列に一致するB列を探し、該当データをワークシート「結果シート」に転記します。検索結果に応じてメッセージが表示され、「結果シート」で一致するデータを確認できます。
(画面クリックして拡大)

 

 

EXCEL VBA Instr関数の使い方と活用例 (複数の文字列を置き換える・結果を別シートに転記する)

 

 

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

下記のサンプルプログラムは、Excelワークブック内のデータシートからデータを読み取り、指定した文字列を含むセルを置換後の文字列に置き換える処理を行います。具体的には、結果シートにデータをコピーした後、置換用シートから置換対象の文字列と置換後の文字列を取得し、結果シート内の全てのセルを順に走査して、文字列を置換します。最後に、処理が完了した旨をメッセージボックスで表示します。一度に大量のデータに対して文字列を置き換える場合などにはとても便利だと思います。それでは、順番に説明いたします。

【プログラムの流れ】
① ワークシート「データシート、結果シート、置換用シート」の3つのシートを取得する。
② ワークシート「データシート」のデータをワークシート「結果シート」にコピーする。
③ ワークシート「置換用シート」から、置換対象の文字列と置換後の文字列を取得する。
④ ワークシート「置換用シート」に設定された全ての文字列に対して、以下の処理を行う。
⑤ 結果シート内のセルを走査して、指定した文字列を含むセルを置換後の文字列に置き換える。
⑥ 結果シートをアクティブ(表示する)
⑦ 置換が完了したことを示すメッセージボックスを表示する。

【プログラム実行条件・注意事項】
・ワークブック内の各ワークシート名が正しく指定されているかを確認する必要がある。
・ワークシート「置換用シート」のデータが正しく設定されていることを確認する必要がある。(置換対象・置換後)
・データシートの内容が大きなデータ量の場合、処理に時間がかかる場合があるため注意が必要である。
・マクロを実行する前に、ワークブックを保存しておくことを推奨する。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● inStr関数03(サンプルプログラム)

 

 

 

 ●実行前~実行後 ※プログラム実行後、置換用シートに設定された全ての文字列に対して、結果シート内のセルを走査して、指定した文字列を含むセルを置換後の文字列に置き換えます。置換が完了したことを示すメッセージボックスが表示されます。大量のデータに対して複数の文字列を置き換えるにはとても便利だと思います。
(画面クリックして拡大)

 

 

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