EXCEL VBA InStr関数で文字列検索を行う方法

VBA InStr関数の使い方

InStr関数の構文

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

InStrの各引数には次のルールで引数を指定します。（最大４つの引数が指定可能です。）

引数[ start ]と[ compare ]の補足説明

[ start ]
省略可能です。string1の検索対象の文字列に対して何文字目から検索するかを指定することができます。
省略すると先頭から検索されます。
通常は、先頭文字から検索することが大半ですので、省略することが多いです。

[ compare ]
省略可能です。
引数 compare を省略すると、バイナリ モードで比較します。
どのようにstring1とstring2を比較するか、下記のモードで指定することができます。

InStr関数の戻り値

InStr関数を実行すると以下の戻り値が返却されます。
返却された値に応じてプログラムで制御してコーディングしましょう。
見つかった時は文字の位置、見つからなかった時は０　と理解しておきましょう。

VBA InStr関数のサンプルコード事例

サンプルソースコード事例①

もっともシンプルなソースコードです。
検索する文字列”EXCEL”が検索対象の文字列の中に存在するかをコーディングしてみます。

Sub sample_Instr_1()
    
    Dim String1 As String
    Dim String2 As String
    
    String1 = "Vba Excel Microsoft"
    String2 = "EXCEL"

    '大文字と小文字を区別するのでアンマッチとなる。
    MsgBox InStr(String1, String2)
  

    '検索対象文字を再設定
    String2 = "Excel"

    '今度は、検索文字が合致するケース
    MsgBox InStr(String1, String2)


End Sub

事例①のソースコードでは、MsgBoxでメッセージを2回表示しています。

それではVBAを実行してみます。
1回目のメッセージボックスでは、大文字と小文字で
“EXCEL”と”Excel”の文字列がアンマッチとなり、

メッセージボックスで0が返却されます。

2回目のメッセージボックスでは、文字列「Excel」が合致しますので、、

検索対象文字列の先頭から5文字目の5が返却されます。

サンプルソースコード事例②

次は、EXCELシートのA列にあるメールアドレスから

　＠の文字列を探し出し、
　＠の文字列よりも左側にあるアカウント名を取り出し、
　EXCELシートのB列に編集する

プログラムをコーディングしてみます。

Sub sample_Instr_2()

    Dim cnt As Long
    Dim MaxGyo As Long
    Dim Search As Long

    '最終行を取得
    MaxGyo = Cells(Rows.Count, 1).End(xlUp).Row

    '1行目から最終行までループ処理
    For cnt = 1 To MaxGyo

        'A列の1行目のセル ⇒ A列の2行目のセル…順に"@"の位置を取り出し変数Searchへ代入
        Search = InStr(Cells(cnt, 1), "@")
        
        '"@"の左側の名前部分をB列の1行目のセル ⇒ B列の2行目のセルへ順に編集
        Cells(cnt, 2) = Left(Cells(cnt, 1), Search - 1)

    Next

End Sub

それでは、実行してみましょう。

下記EXCELシートが、実行前のEXCELのデータとなります。

実行するとB列にアカウント名が編集されることになります。
↓

サンプルソースコード事例③

検索する文字列がアルファベットの場合、意図的に
大文字と小文字や全角半角を区別せずに
検索したい局面があると思います。

そんなときは、4番目の引数[ compare ]に、
定数vbTextCompare　を設定します。

コーディングを見てみましょう。

Sub sample_Instr_3()
    
    Dim String1 As String
    Dim String2 As String
    
    String1 = "Vba Excel Microsoft"
    String2 = "EXCEL"

    
    '大文字と小文字と全角半角を区別しない
    MsgBox InStr(1, String1, String2, vbTextCompare)


End Sub

それではVBAを実行してみます。
今度は、”EXCEL”と”Excel”の文字列が合致することになりますので

MsgBoxで検索対象の文字列の中から、文字位置の”5”が表示されました。

InStr関数の関連関数（文字列関数）

InStr関数と合わせて覚えると便利な関数を３つ紹介します。

 InStrRev 関数

文字列の後ろから前方に向かって 検索してくれます。

（注）ただし、検索結果や引数「開始位置」は前方からの文字位置が取得されます。

基本構文①

InStrRev(検索対象, 検索ワード)
検索ワードを検索対象の文字列の後ろから検索し、見つかった文字位置を取得します。

基本構文②

InStrRev(検索対象, 検索ワード, 開始位置, 比較モード)
指定した開始位置から検索することができます。比較モードで大文字・小文字を区別するか指定できます。

引数（比較モード）

使用例

Dim cnt As Long
cnt = InStrRev("XYZXYZ", "A")
Debug.Print(cnt) ' 0

cnt = InStrRev("XYZXYZ", "X")
Debug.Print(cnt) ' 4

 Mid関数

Mid関数は、文字列から指定した文字数分の文字列を抽出してくれます。

（注）引数「文字数」が 0 なら、空文字 “” を返します。
　　　引数「文字数」が検索対象の文字列の長さを超える場合、Mid関数は長さ0の文字列 (“”) を返します。

基本構文①

Mid(文字列, 開始位置)
開始位置から文字列を最後の文字まで抽出します。

基本構文②

Mid(文字列, 開始位置, 文字数)
文字列を開始位置から指定した文字数分、抽出します。

使用例

Dim moji As String
moji = Mid("ABCDEF", 3)
Debug.Print(moji) ' CDEF

moji = Mid("ABCDEF", 7)
Debug.Print(moji) ' ""

moji = Mid("ABCDEF", 2, 3) ' ２文字目から３文字取得する
Debug.Print(moji) ' BCD

 Left 関数

Left関数は、文字列の左端から指定した文字数分の文字列を抽出してくれます。

（注）引数「文字数」が 0 なら、空文字 “” を返します。
　　　引数「文字数」が引数「文字列」を超える場合、引数「文字列」をそのまま返します。

基本構文①

Left(文字列, 文字数)
文字列の左から指定した文字数を抽出します。

使用例

Dim moji As String
moji = Left("ABCDEF", 0)
Debug.Print(moji) ' ""

moji = Left("ABCDEF", 3)
Debug.Print(moji) ' ABC

moji = Left("ABCDEF", 10)
Debug.Print(moji) ' ABCDEF

まとめ

今回の記事では、文字列検索を行う時に使用する「InStr関数」を解説しました。

関連する文字列関数（ InStrRev 関数・ Mid関数・ Left 関数）も一緒に覚えておきましょう。

これらの文字列関数ですが、実はSQLなんかでも、使い方がよく似ています。

エンジニアを目指す方は、SQLのスキルも勉強しておくと便利ですので、こちらの記事も併せてご覧ください。

SQLが無料で環境構築することなく、実際にSQLを打ち込んで勉強することができます。

SQL初心者に有効な【無料】の勉強方法｜Oracle Live SQL

あわせて読みたい

【無料】で学べるSQL学習サイト｜Oracle Live SQL エンジニアを目指す方にとって、SQLやデータベースの知識は必須の知識です。 SQLをマスターするもっとも効果的な方法は、実際にSQLを手で打ち込むことです。 書籍を読ん…