－ちょっと使えるネタを紹介－
Excel VBAで特定の文字列を含むかを検索して判定する方法についてご紹介します。VBA関数のInStrを使えば文字列を含むかの判定ができます。さらにInStrを応用すると、文字列の位置を取得して氏名の分割や、カッコ内の文字列を切り出したりすることができます。
この記事では、文字列を検索するInStrについてご紹介します。
文字列が含むかを判定するには、InStrの返り値が0より大きいかどうかで、判定することができます。
文字列を含む位置を使って氏名を分けたり、カッコ内の文字列を切り出したり、検索値を含む文字列を抽出するなど、実務でかなり使えます。
文字列を検索する方法について、マスターしていきましょう。
では、文字列を検索する方法について解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
文字列を検索したい場合は、VBA関数のInStrを使うとできます。
元の文字列の中から一致した文字列の位置を返すのがInStrです。
文字列の中から1文字を検索する場合、InStrは次のような検索イメージになります。
文字列「あいうえお」から「う」の位置を検索しています。
結果は、「3」文字目となります。
ポイントは、一致した文字列の位置を返すということです。
文字列「あいうえお」で「う」は左から3文字目なので「3」を返します。
VBAコードで記載すると次のようになります。
Sub TEST1()
MsgBox InStr("あいうえお", "う") '「う」の位置を取得
End Sub
変数の入力は、「位置 = InStr(文字列, 検索する文字列)」となります。
では実行してみます。
結果は「3」文字目となりました。
検索文字がない場合、InStrは「0」を返します。
検索文字がない場合のイメージです。
やってみます。
Sub TEST2()
MsgBox InStr("あいうえお", "か") '「か」の位置を取得 ⇒ 0になります。
End Sub
文字列「あいうえお」から「か」を検索しています。
文字列「あいうえお」に「か」は含まれないので結果は「0」となります。
InStrで特定の文字列を含むかを判定する方法についてご紹介します。
判定に使うのは文字列をInStrで検索したときに取得できる位置を使います。
文字列を検索して、文字列を含む場合は、0より大きい値を返します。
反対に、文字列を含まない場合は、0を返します。
これを利用します。
特定の文字列を含む場合、InStrで取得できる値は必ず0より大きい値となります。
なので、特定の文字列を含む場合という条件のVBAコードは次のようになります。
Sub TEST3()
'文字列に「う」が含まれているか
If InStr("あいうえお", "う") > 0 Then
MsgBox "文字列に含まれています"
End If
End Sub
文字列「あいうえお」に「う」が含まれている場合は、メッセージを表示する。というVBAコードとなっています。
では実行してみます。
メッセージが表示されました。
期待していた通り、文字列「あいうえお」は「う」の文字列を含むという結果となります。
特定の文字列を含まない場合、InStrで取得する値は必ず0となります。
なので、特定の文字列を含まない場合という条件のVBAコードは次のようになります。
Sub TEST4()
'文字列に「か」が含まれていないか
If InStr("あいうえお", "か") = 0 Then
MsgBox "文字列に含まれていません"
End If
End Sub
文字列「あいうえお」に「か」が含まれていない場合は、メッセージを表示する。というVBAコードとなっています。
では実行してみます。
メッセージが表示されました。
期待していた通り、文字列「あいうえお」は「か」の文字列を含まないという結果になります。
特定の文字列を含むかの判定はInStrの返り値が0より大きいか、0かで判定することができます。
結構使うVBAコードです。
特定の文字列をInStrで含むかを判定する場合、大文字と小文字は区別されます。
「A」と「a」は別ものということですね。
大文字を小文字で検索してみます。
文字列「ABC」を「a」で検索してみる。
Sub TEST5()
'文字列に「a」が含まれているか
If InStr("ABC", "a") > 0 Then
MsgBox "文字列に含まれています"
Else
MsgBox "文字列に含まれていません"
End If
End Sub
実行してみます。
含まれないのメッセージが表示されました。
文字列「ABC」の中に「a」は、存在しないということになります。
解決策です。
検索される文字列と検索する文字列を「大文字」に統一して検索すると、大文字と小文字を区別しないで、検索することができます。
大文字と小文字を区別したくないという場合に使えます。
イメージとしては次のようになります。
すべて大文字にすれば、大文字と小文字の区別なく文字列を検索できます。
VBAコードを記載してみます。
文字列「ABC」から「a」を検索してみます。
Sub TEST6()
'文字列に「a」が含まれているか（大文字と小文字の区別なし）
If InStr(StrConv("ABC", vbUpperCase), StrConv("a", vbUpperCase)) > 0 Then
MsgBox "文字列に含まれています"
Else
MsgBox "文字列に含まれていません"
End If
End Sub
文字列と検索文字を「StrConv(文字列, vbUpperCase)」を使ってすべて大文字に統一しています。
実行してみます。
文字列を含むというメッセージとなりました。
大文字と小文字の区別しないで、検索できていることがわかります。
全角と半角を区別しないで検索する場合も、同じ要領でできます。
検索対象の文字列と検索値を、全角に統一して検索をするという感じです。
Sub TEST7()
'文字列に「A」が含まれているか（全角と半角の区別なし）
If InStr(StrConv("ＡＢＣ", vbWide), StrConv("A", vbWide)) > 0 Then
MsgBox "文字列に含まれています"
Else
MsgBox "文字列に含まれていません"
End If
End Sub
実行してみます。
文字列を含みますとなって、全角と半角を区別しないで検索できました。
ひらがなとカタカナを区別しないで、検索してみます。
検索対象の文字列と検索値を、「ひらがな」に統一して検索をします。
Sub TEST8()
'文字列に「あ」が含まれているか（ひらがなとカタカナの区別なし）
If InStr(StrConv("アイウ", vbHiragana), StrConv("あ", vbHiragana)) > 0 Then
MsgBox "文字列に含まれています"
Else
MsgBox "文字列に含まれていません"
End If
End Sub
では、実行してみます。
ひらがなとカタカナを区別しないで、検索できました。
大文字と小文字、全角と半角、ひらがなとカタカナを、すべて区別しないで検索したい場合は、InStrの引数に「vbTextCompare」を使うとできます。
Sub TEST9()
'文字種を区別しないで、検索する
If InStr(1, "ｱｲｳ", "あ", vbTextCompare) > 0 Then
MsgBox "文字列に含まれています"
Else
MsgBox "文字列に含まれていません"
End If
End Sub
実行してみます。
文字種を区別しないで、検索することができました。
InStrを使って、
をやってみます。
氏と名を分けたい場合に「InStr」は使えます。
例えば「鈴木 一郎」を「鈴木」と「一郎」に分けたい場合です。
手順は次のようになります。
というような流れになります。
Sub TEST10()
Dim A
'全角スペースの位置を取得
A = InStr(Cells(1, 1), " ")
'全角スペースの前を切り出し
Cells(1, 2) = Left(Cells(1, 1), A - 1)
'全角スペースの後を切り出し
Cells(1, 3) = Mid(Cells(1, 1), A + 1)
End Sub
全角スペースの位置を探すのが、InStrです。
全角スペースの前を切り出すのが、Leftです。
全角スペースの後を切り出すのが、Midです。
では実行してみます。
「鈴木 一郎」を「鈴木」と「一郎」に分けることができました。
カッコ内の文字列を切り出す場合にもInStrが使えます。
例えば、「123（456）789」のカッコ内の「456」を切り出したいといった場合です。
このような手順になります。
というような流れになります。
文字列「123（456）789」からカッコ内の「456」を切り出してみます。
Sub TEST11()
Dim A
A = "123（456）789"
Dim B, C
B = InStr(A, "（") '「（」の位置を取得
C = InStr(A, "）") '「）」の位置を取得
'カッコの中を切り出し
MsgBox Mid(A, B + 1, C - B - 1)
End Sub
では実行してみます。
結果は「456」となりました。
文字列「123（456）789」のカッコ内の文字列を取得することができました。
指定した文字列を含む値を抽出する、というのをやってみます。
InStrを使って値を抽出するのは、実務ではかなり使いますので、マスターしていきましょう。
InStrで文字列を検索して、値を抽出してみます。
セルをFor～Nextでループして、指定した値を含む値をInStrで抽出していきます。
Sub TEST12()
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
'検索値を含む文字列を探す
If InStr(Cells(i, "A"), Range("C2")) > 0 Then
'セルに転記
Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Cells(i, "A")
End If
Next
End Sub
表の中から「鈴木」を含む文字列を抽出してみます。
実行すると、「鈴木」を含む文字列を抽出できます。
実行すると、「鈴木」を含む文字列を抽出できました。
ちなみに、重複がある表示で実行すると、抽出した値が重複してしまいます。
先ほどのVBAコードを実行してみます。
抽出したデータに重複がでてきます。
抽出した値が重複しないようにしたい場合は、重複の削除が使えます。
ユニークな値のみを検索するVBAコードになります。
値を抽出して、最後に重複データを削除します。
Sub TEST13()
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
'検索値を含む文字列を探す
If InStr(Cells(i, "A"), Range("C2")) > 0 Then
'セルに転記
Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Cells(i, "A")
End If
Next
Dim LastRow
LastRow = Cells(Rows.Count, "C").End(xlUp).Row '最終行を取得
'重複を削除
Range("C5:C" & LastRow).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
実行すると、「鈴木」を含むユニークな値を抽出できます。
重複データが削除されてユニークなデータとなりました。
こんな感じで、ユニークなデータのみを抽出することができます。
さらに「Changeイベント」と組み合わせると、入力データを検索してリストから値を入力する、というのもできます。
シートモジュールのところに、VBAコードを記載します。
Private Sub Worksheet_Change(ByVal Target As Range) 'E2以外の変更は終了 If Target.Address(False, False) <> "E2" Then Exit Sub 'データを初期化 Range("E5:E10").ClearContents For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row '検索値を含む文字列を探す If InStr(Cells(i, "A"), Range("E2")) > 0 Then 'セルに入力 Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = Cells(i, "A") End If Next Dim LastRow LastRow = Cells(Rows.Count, "E").End(xlUp).Row '最終行を取得 '重複を削除 Range("E5:E" & LastRow).RemoveDuplicates Columns:=1, Header:=xlNo End Sub
まず、検索値を入力します。
「鈴木」を入力してみます。
Changeイベントで実行されて、表の中から「鈴木」を含むユニークなデータを作成できます。
あらかじめ入力規則で設定しておくと、検索した結果のリストから値を入力することができます。
検索した結果から値を入力することができます。
こんな感じで、「InStr」をうまく使えば、表の中から検索値を含む文字列を抽出することができます。
この記事では、文字列を検索するInStrについてご紹介しました。
文字列があるかを判断するには、InStrの返り値が0より大きいかどうかで、文字列を含むかを判定することができます。
文字列を含む位置を使って氏名を分けたり、カッコ内の文字列を切り出したり、検索値を含む文字列を抽出するなど、実務でかなり使えます。
文字列を検索する方法について、マスターしていきましょう。
参考になればと思います。最後までご覧くださいましてありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する