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

  • URLをコピーしました!

ある文字列の中から「指定した文字列」が含まれているかどうかを調べるとき「InStr」というExcel vba の関数を使用すると便利です。

InStr関数は、検索対象の文字列の中から、自分が探したい「文字」が、最初に見つかった時の何番目の文字位置かを返却してくれる便利な関数です。

この記事では、「InStr」という文字列検索を行う関数について解説していきますのでマスターしてください。

目次

VBA InStr関数の使い方

VBA InStr関数の使い方の画像

InStr関数の構文

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

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

  • startは省略可能です。検索を始める位置を指定します。
  • string1は必須です。検索の対象となる文字列を指定します。
  • string2は必須です。string1から検索する文字列を指定します。
  • compareは省略可能です。文字列を比較するモードを指定します。

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

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

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

定数説明
vbUseCompareOption-1Option Compare ステートメントの設定を使用して比較を行います。
Option Compareの規定は、vbBinaryCompareになります。
vbBinaryCompare0バイナリモードで比較を行います。
バイナリモードでは全角半角、大文字・小文字が区別されます。
vbTextCompare1テキストモードで比較を行います。
テキストモードでは全角半角、大文字・小文字が区別されません。

InStr関数の戻り値

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

条件戻り値
引数string2が引数stiring1の中に見つかった時引数string2が最初に見つかった位置
引数string2が引数stiring1に見つからない時0
引数string1が空欄(“”)のとき0
引数string2が空欄(“”)のとき引数startに指定した値
引数startの値が引数string1の文字数を超えるとき0
引数startの値が引数string2の文字数より大きいとき0

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

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”の文字列がアンマッチとなり、

実行結果(MSBBOX)①

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

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

実行結果(MSBBOX)②

検索対象文字列の先頭から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のデータとなります。

VBA実行前のEXCELサンプルデータ


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

VBA実行後のEXCELサンプルデータ

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

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

そんなときは、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”の文字列が合致することになりますので

実行結果(MSBBOX)③

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

InStr関数の関連関数(文字列関数)

InStr関数の関連関数(3つ)

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

 InStrRev 関数

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

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

基本構文①

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

基本構文②

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

引数(比較モード)

定数説明
vbBinaryCompare (既定)
バイナリ モードで比較を行います。
全角半角、大文字小文字が区別されます。
vbTextCompareテキスト モードで比較を行います。
全角半角、大文字小文字が区別されません。

使用例

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) ' 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

この記事が気に入ったら
いいねしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!
目次