EXCEL VBA Instr関数の使い方と活用例(対象文字検索・何番目・文字列を置き換える・該当データを転記)
EXCEL VBA Instr関数の使い方と活用例(対象文字検索・何番目・文字列を置き換える・該当データを転記)
今回説明するのは、inStr関数について説明を行いたいと思います。InStr関数は、文字列内に特定の文字列が現れる位置を検索するために使用されます。具体的な利用用途としては、データ分析や文字列操作において、指定した文字列が存在するかどうかを判断したり、特定の区切り文字で文字列を分割する際などに利用されます。
●【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の場合(見つからなかった場合)、メッセージボックスに見つからなかった旨を表示する。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
' ' Sub InstrExample1() ' 検索対象の文字列と検索文字列を定義 Dim targetString As String Dim searchString As String Dim position As Integer targetString = "ようこそAKIRA55へExcel VBA Instr関数を説明します。" searchString = "VBA" ' InStr関数を使って検索文字列の位置を取得 position = InStr(targetString, searchString) ' 取得した位置が0より大きい場合(見つかった場合) If position > 0 Then ' メッセージボックスに位置情報を表示 MsgBox searchString & " が見つかりました。位置: " & position Else ' メッセージボックスに見つからなかった旨を表示 MsgBox searchString & " が見つかりませんでした。" End If End Sub ' |
EXCEL VBA Instr関数の使い方と活用例 (検索・該当したデータを別シートに転記します。)
このVBAコードは、Excelのワークシートから特定の文字列を検索し、検索結果を別のワークシートに転記するプログラムです。具体的には、”データシート”のA2:C最終行までの範囲にあるデータを検索し、”F2″セルに入力された検索文字列がB列の住所データに含まれる行を見つけます。該当する行が見つかった場合、その行のA列からC列までのデータを”結果シート”に転記します。
【プログラムの流れ】
① 入力データのシート(“データシート”)と出力先のシート(“結果シート”)を設定します。
② ワークシート「データシート」の”F2″セルから検索文字列を取得します。
③ ワークシート「結果シート」のデータをクリアします。
④ ワークシート「データシート」の最終行を取得します。
⑤ ”ワークシート「データシート」のA2:C最終行までのデータを配列に格納します。
⑥ 出力用の配列を初期化します。
⑦ 配列に出力データを格納します。
⑧ スクリーン更新をオフにします。
⑨ ”結果シート”に出力データを書き込みます(該当データが1行以上ある場合)。
⑩ スクリーン更新をオンに戻します。
⑪ ワークシート「結果シート」を表示します。
⑫ 検索結果のメッセージを表示します。
【プログラム実行条件・注意事項】
・ワークシート名が正しく設定されていることを確認してください(”データシート”および”結果シート”)。
・検索文字列を入力するセル(”F2″)が正しいことを確認してください。
・データの範囲がA2:C最終行であることを確認してください。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● inStr関数02(サンプルプログラム)
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
' ' Sub SearchAndCopy01() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim searchStr As String Dim lastRow As Long Dim srcRow As Long Dim dataArr As Variant Dim outputArr() As Variant Dim outputIndex As Long ' 入力データのシートと出力先のシートを設定 Set wsSource = ThisWorkbook.Worksheets("データシート") Set wsTarget = ThisWorkbook.Worksheets("結果シート") ' 検索したい部分文字列を設定 searchStr = wsSource.Range("F2") '検索文字を指定 ' 結果シートの最終行を取得 lastRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row ' 結果シートをクリアします。 wsTarget.Range("A2:C" & lastRow + 1).ClearContents ' データシートの最終行を取得 lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row ' セルの値を一度に取得し、配列に格納 dataArr = wsSource.Range("A2:C" & lastRow).Value ' 出力用の配列を初期化 ReDim outputArr(1 To lastRow - 1, 1 To 3) ' 配列に出力データを格納 outputIndex = 1 For srcRow = 1 To lastRow - 1 If InStr(dataArr(srcRow, 2), searchStr) > 0 Then 'B列の住所データの中から該当する文字列を探します。 outputArr(outputIndex, 1) = dataArr(srcRow, 1) '該当したデータを行のA列~C列データを配列に保管します。 outputArr(outputIndex, 2) = dataArr(srcRow, 2) outputArr(outputIndex, 3) = dataArr(srcRow, 3) outputIndex = outputIndex + 1 End If Next srcRow ' スクリーン更新をオフにする Application.ScreenUpdating = False ' 結果シートに一度に出力データを書き込む(見つかった行が1行以上ある場合) If outputIndex > 1 Then wsTarget.Range("A2:C" & outputIndex).Value = outputArr End If ' スクリーン更新をオンに戻す Application.ScreenUpdating = True ' 結果シートを表示する wsTarget.Activate ' 結果シートにメッセージを表示 If outputIndex > 1 Then MsgBox "検索が完了しました。" & outputIndex - 1 & "件データが転記されました。" Else MsgBox "検索結果、該当データがありませんでした。" End If End Sub ' |
(画面クリックして拡大)
EXCEL VBA Instr関数の使い方と活用例 (複数の文字列を置き換える・結果を別シートに転記する)
下記のサンプルプログラムは、Excelワークブック内のデータシートからデータを読み取り、指定した文字列を含むセルを置換後の文字列に置き換える処理を行います。具体的には、結果シートにデータをコピーした後、置換用シートから置換対象の文字列と置換後の文字列を取得し、結果シート内の全てのセルを順に走査して、文字列を置換します。最後に、処理が完了した旨をメッセージボックスで表示します。一度に大量のデータに対して文字列を置き換える場合などにはとても便利だと思います。それでは、順番に説明いたします。
【プログラムの流れ】
① ワークシート「データシート、結果シート、置換用シート」の3つのシートを取得する。
② ワークシート「データシート」のデータをワークシート「結果シート」にコピーする。
③ ワークシート「置換用シート」から、置換対象の文字列と置換後の文字列を取得する。
④ ワークシート「置換用シート」に設定された全ての文字列に対して、以下の処理を行う。
⑤ 結果シート内のセルを走査して、指定した文字列を含むセルを置換後の文字列に置き換える。
⑥ 結果シートをアクティブ(表示する)
⑦ 置換が完了したことを示すメッセージボックスを表示する。
【プログラム実行条件・注意事項】
・ワークブック内の各ワークシート名が正しく指定されているかを確認する必要がある。
・ワークシート「置換用シート」のデータが正しく設定されていることを確認する必要がある。(置換対象・置換後)
・データシートの内容が大きなデータ量の場合、処理に時間がかかる場合があるため注意が必要である。
・マクロを実行する前に、ワークブックを保存しておくことを推奨する。
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● inStr関数03(サンプルプログラム)
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
Option Explicit ' ' Sub ReplaceCellsContainingString01() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim wsReplace As Worksheet Dim lastRow As Long Dim lastCol As Long Dim replaceLastRow As Long Dim row As Long Dim col As Long Dim replaceRow As Long Dim searchStr As String Dim replaceStr As String ' 入力データのシート、出力先のシート、置換用シートを設定 Set wsSource = ThisWorkbook.Worksheets("データシート") Set wsTarget = ThisWorkbook.Worksheets("結果シート") Set wsReplace = ThisWorkbook.Worksheets("置換用シート") ' データシートのデータを結果シートにコピー wsSource.UsedRange.Copy wsTarget.Cells(1, 1) ' データシートの最終行と最終列を取得 lastRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).row lastCol = wsTarget.Cells(1, wsTarget.Columns.Count).End(xlToLeft).Column ' 置換用シートの最終行を取得 replaceLastRow = wsReplace.Cells(wsReplace.Rows.Count, "A").End(xlUp).row ' スクリーン更新をオフにする Application.ScreenUpdating = False ' 置換用シートの検索文字と置換文字をループで処理 For replaceRow = 2 To replaceLastRow searchStr = wsReplace.Cells(replaceRow, 1).Value replaceStr = wsReplace.Cells(replaceRow, 2).Value ' 結果シート内の特定の文字列を含むセルを検索し、置換文字列で置き換える For row = 1 To lastRow For col = 1 To lastCol If InStr(wsTarget.Cells(row, col).Value, searchStr) > 0 Then wsTarget.Cells(row, col).Value = Replace(wsTarget.Cells(row, col).Value, searchStr, replaceStr) End If Next col Next row Next replaceRow ' スクリーン更新をオンに戻す Application.ScreenUpdating = True '置換用シートを表示 wsTarget.Activate ' 結果メッセージを表示 MsgBox "指定された文字列が置換されました。" End Sub ' ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。