【ExcelVBA】InStr関数の使い方とは?実務で使うテクニックも解説!

いつもの仕事を楽に
Sponsored Link

こんにちは、VBAエンジニアのやすこれです。

みなさんはVBAでInStr関数を使ったことがありますか?

  • VBAのInStr関数の基本的な使い方を知りたい
  • InStr関数の使いどころがわからない
  • 利用の仕方を実例付きで知りたい!

という方も多いのではないでしょうか。

そこで今回は、任意の文字列の開始位置を取得できるInStr関数の基本的な使い方と実務での応用方法をサンプルコード付きで紹介します。

Sponsored Link

VBAでのInStr関数の使い方

InStr関数の基本的な使い方

InStr関数は、指定した文字列から任意の文字列を検索し、見つかった開始位置を数字で返す関数です。

  1. 基本構文
InStr([開始位置, ]文字列1, 文字列2[ ,比較形式])
  • [開始位置, ][ ,比較形式]は省略可能
  1. コード例
InStr("あいうえお", "う")
文字列1「あいうえお」から文字列2「う」の開始位置を返しています。

VBAInStr関数検索イメージ
画像のように、文字列1「あいうえお」の開始位置は「1」から始まります。文字列2「う」の位置は開始から3番目なのでサンプルのInStr関数は「3」を返します。

他にも、省略した引数を指定するとどうなるのか、検索文字列が見つからない場合はどうなるのかなど、いくつかパターンがあるので、詳しく解説します。

InStr関数の引数と返り値について

InStr関数の引数

省略可能な引数も含めて一覧で解説します。
  • 基本構文
InStr([開始位置, ]文字列1, 文字列2[ ,比較形式])
引数省略定数詳細
開始位置省略可省略した場合は「1」が指定される
「比較軽視」が指定されている場合は、省略不可
文字列1省略不可検索対象となる文字列
文字列2省略不可文字列1の中から検索する文字列
比較形式省略可vbUseCompareOption(-1)
vbBinaryCompare(0)
vbTextCompare(1)
「Option Compare」で指定した形式で比較
大文字・小文字を区別して比較(バイナリ比較)
大文字・小文字、全角・半角を区別せず比較(テキスト比較)

InStr関数の返り値

各引数の値による返り値を一覧で解説します。
条件返り値
文字列1内で文字列2が見つかった最初に見つかった位置
文字列1 = “”0
文字列1 = NullNull
文字列1内で文字列2が見つからない0
文字列2 = “”開始位置
文字列2 = NullNull
開始位置が文字列1の文字数を超える0


InStr関数の注意点と解決方法

InStr関数の注意点

指定した文字列を検索して数値を返すInStr関数ですが、注意点があります。

それは比較形式によって、返り値が変わることです。

返り値にどういう違いがでるのか、説明します。

  • 比較形式を省略した場合、大文字・小文字が区別されて検索される
  • 比較形式に「vbTextCompare」もしくは「1」を指定した場合、大文字・小文字が区別されず検索される
  • 比較形式を省略した場合、全角・半角が区別されて検索される
  • 比較形式に「vbTextCompare」もしくは「1」を指定した場合、全角・半角が区別されず検索される


InStr関数の注意点を解決する方法

InStr関数は省略可能な「比較形式」の指定によって返り値が変わります。それぞれの場合でどういった指定をするべきかしっかりと頭に入れておきましょう。

  • 大文字・小文字、全角・半角を区別する場合
  • 大文字・小文字、全角・半角を区別しない場合

大文字・小文字、全角・半角を区別する場合

大文字・小文字、全角・半角を区別するには、比較形式に「vbBinaryCompare」もしくは「0」を指定しましょう。

Dim str1 As String
str1 = "ABCDE"
Dim str2 As String
str2 = "a"
MsgBox "返り値:" & InStr(1, str1, str2, vbBinaryCompare)

検索対象となる文字列「ABCDE(= str1)」から文字列「a(= str2)」を検索しています。ただし、比較形式には「vbBinaryCompare」を指定しているため、大文字と小文字を区別しています

よって結果は次の画像の通り、「0」となります。

VBAInStr関数比較形式vbBinaryCompareイメージ

大文字・小文字、全角・半角を区別しない場合

大文字・小文字、全角・半角を区別しないには、比較形式に「vbTextCompare」もしくは「1」を指定しましょう。

Dim str1 As String
str1 = "ABCDE"
Dim str2 As String
str2 = "a"
MsgBox "返り値:" & InStr(1, str1, str2, vbTextCompare)

検索対象となる文字列「ABCDE(= str1)」から文字列「a(= str2)」を検索しています。ただし、比較形式には「vbTextCompare」を指定しているため、大文字と小文字を区別しません

よって結果は次の画像の通り、「1」となります。

VBAInStr関数比較形式vbTextCompareイメージ
また、比較形式が「vbBinaryCompare」でも「UCase」「LCase」関数を使って事前に引数の文字列の大文字・小文字をそろえておくことで、大文字・小文字の区別なく検索することができます。

  • UCase:文字列を大文字に変換する関数
  • LCase:文字列を小文字に変換する関数

Dim str1 As String
str1 = "ABCDE"
Dim str2 As String
str2 = "a"
MsgBox "返り値:" & InStr(1, str1, UCase(str2), vbBinaryCompare)

上記サンプルコードは比較形式に大文字・個別を区別する「vbBynaryCompare」を指定しています。しかし「a(= str2)」を「UCase関数」を用いて「A(= UCase(str2))」とすることで、画像のように返り値は1となります。うまく組み合わせて使ってみましょう。

VBAInStr関数比較形式UCase_vbBinaryCompareイメージ

InStr関数の実務での利用方法

名字と名前を分ける

Dim fullName As String
fullName = "田中 太郎"

Dim borderNum As Long
borderNum = InStr(fullName, " ")

Dim lastName As String
lastName = Left(fullName, borderNum - 1)

Dim fastName As String
fastName = Mid(fullName, borderNum + 1, Len(fullName) - borderNum)

まず「InStr関数」を使って、名字と名前の間にある「スペース」の位置を取得して、「borderNum」に定義しています。
Dim borderNum As Long
borderNum = InStr(fullName, " ")

VBAInStr名字と名前分けスペース取得
次に「Left関数」と先ほど定義した「borderNum」を使って「スペース」の手前までの文字列(名字)を取得しています。
Dim lastName As String
lastName = Left(fullName, borderNum - 1)

VBAInStr名字と名前分け名字取得
  • Left(文字列, 数値)
  • 対象「文字列」を「数値」で指定した文字数だけ左から取り出す
ex)Left(“ABCDE”, 3) → “ABC”

最後に「Mid関数」と「borderNum」と「Len関数」を使って「スペース」の後ろから最後までの文字列(名前)を取得しています。
Dim fastName As String
fastName = Mid(fullName, borderNum + 1, Len(fullName) - borderNum)

VBAInStr名字と名前分け名前取得
  • Mid(文字列, 数値1, 数値2)
  • 対象「文字列」から、「数値1」を開始位置として「数値2」で指定した文字数を取り出す
ex)Mid(“ABCDE”, 2, 3) → “BCD”
  • Len(文字列)
  • 対象「文字列」の文字数を数値で返す
ex)Len(“ABCDE”) → 5

以上で、InStr関数を使って名前と名字を分けることができました。いくつか他の関数もでてきましたが、ひとつひとつはとても単純です。順を追ってしっかり理解しましょう。


複数の文字列で検索する

複数の文字列でOr検索

Dim str1 As String
str1 = "ABCDE"
Dim str2 As String
str2 = "A"
Dim str3 As String
str3 = "G" If InStr(str1, str2) > 0 Or InStr(str1, str3) > 0 Then MsgBox "文字列の中に「" & str2 & "」または「" & str3 & "」がありました。" Else MsgBox "文字列の中に「" & str2 & "」も「" & str3 & "」もありませんでした。" End If

「If関数」と「Or関数」を組み合わせて、複数文字列の検索を行います。

「InStr関数」は文字列が見つかった場合に正の整数で開始位置を返すので「InStr(〇〇) > 0」とすることで文字列が見つかったかどうかの判定を行うことができます。処理結果は画像のようになります。

VBAInStr複数の文字列でOr検索
先ほど紹介した、名前と名字を分ける処理を行う際、スペースが半角・全角どちらかわからなければ、組み合わせて使うことができますね。


複数の文字列でAnd検索

Dim str1 As String
str1 = "ABCDE"
Dim str2 As String
str2 = "A"
Dim str3 As String
str3 = "B" If InStr(str1, str2) > 0 And InStr(str1, str3) > 0 Then MsgBox "文字列の中に「" & str2 & "」「" & str3 & "」どちらもありました。" Else MsgBox "文字列の中に「" & str2 & "」と「" & str3 & "」のどちらかもしくは両方ありませんでした。" End If

「If関数」と「And関数」を組み合わせて、複数文字列の検索を行います。

先ほどと同様、「InStr関数」は文字列が見つかった場合に正の整数で開始位置を返すので「InStr(〇〇) > 0」とすることで文字列が見つかったかどうかの判定を行うことができます。処理結果は画像のようになります。

VBAInStr複数の文字列でAnd検索

住所から都道府県を取り出す

Dim trgtAddress As String
trgtAddress = "東京都〇〇区"

Dim endNum As Long
If InStr(trgtAddress, "県") = 4 Then
    endNum = 4
Else
    endNum = 3
End If

Dim trgtPrefecture As String
trgtPrefecture = Left(trgtAddress, endNum)

まず都道府県は「神奈川県」「和歌山県」「鹿児島県」以外はすべて3文字であることを抑えておきましょう。

3つの県に該当しなければ、左端から3文字を取得することで、都道府県を取り出すことができます。逆に3つの県に該当する、つまり4文字目が「県」(「InStr(〇〇, “県”) = 4」)の場合は左端から4文字を取得すれば都道府県を取り出すことができます。

  • Left(文字列, 数値)
  • 対象「文字列」を「数値」で指定した文字数だけ左から取り出す
ex)Left(“ABCDE”, 3) → “ABC”


フルパスをディレクトリとファイル名に分ける

ディレクトリとファイル名に分ける処理は「InStr関数」の代わりに「InStrRev関数」を使って行います。使い方は「InStr関数」に非常によく似ていますので、紹介します。


InStrRev関数の使い方

InStrRev関数は、指定した文字列の後ろから任意の文字列を検索し、はじめに見つかった位置を数字で返す関数です。「InStr関数」とは省略可能な引数の位置が違うことに注意しましょう。

  1. 基本構文
InStrRev(文字列1, 文字列2[ ,開始位置[ ,比較形式]])
  • [ ,開始位置[ ,比較形式]]は省略可能
  1. コード例
InStrRev("あいうえお", "う")
文字列1「あいうえお」の後ろから文字列2「う」を検索して初めに見つかった位置を返しています。

VBAInStrRev関数検索イメージ
画像のように、文字列2「う」の位置は文字列1「あいうえお」の後ろから3番目なのでサンプルのInStrRev関数は「3」を返します。


InStrRev関数を使ってフルパスからディレクトリとファイル名を取得する

Dim trgtPath As String
trgtPath = "C:\サンプル\SampleFile.xlsx"

Dim dirStr As String
dirStr = Left(trgtPath, InStrRev(trgtPath, ""))

Dim fileName As String
fileName = Replace(trgtPath, dirStr, "")

まず次の部分で、ディレクトリとファイル名に分けたいフルパス「trgtPath」から、ディレクトリ「dirStr」を取り出します。
Dim trgtPath As String
trgtPath = "C:\サンプル\SampleFile.xlsx"

Dim dirStr As String
dirStr = Left(trgtPath, InStrRev(trgtPath, ""))

画像のように「InStrRev(trgtPath, “\”)」の部分で末尾の「\」の位置、つまりファイル名が始まる直前の位置を取得しLeft関数でそれよりも前の文字列であるディレクトリを取得しています。

VBAInStrRevディレクトリとファイル名分けるディレクトリ取得
  • Left(文字列, 数値)
  • 対象「文字列」を「数値」で指定した文字数だけ左から取り出す
ex)Left(“ABCDE”, 3) → “ABC”

次にフルパス「trgtPath」から取得したディレクトリ「dirStr」を取り除いて、ファイル名「fileName」を取得します。
Dim fileName As String
fileName = Replace(trgtPath, dirStr, "")

画像のように「Replace関数」を使って、フルパス「trgtPath」からディレクトリ名「dirStr」を「空の文字列」に置き換えることでファイル名「fileName」を取得しています。

VBAInStrRevディレクトリとファイル名分けるファイル名取得
  • Replace(文字列1, 文字列2, 文字列3)
  • 対象「文字列1」から「文字列2」を検索し、「文字列3」に置き換える
ex)Replace(“ABCDE”, “DE”, “”) → “ABC”

以上で、フルパスをディレクトリとファイル名に分けることができました。「InStr関数」ではなく「InStrRev関数」を使いましたが、この2つの関数は非常に似ており応用も効くので、あわせて使いこなしましょう。

VBAでのInStr関数まとめ

今回はVBAでのInStr関数の使い方と実務での使用方法を紹介いたしました。

VBAInStr関数の実務での利用方法

  • 名字と名前を分ける
  • 複数の文字列で検索する
  • 住所から都道府県を取り出す
  • フルパスをディレクトリとファイル名に分ける

注意点として紹介した小文字・大文字判定の切り替えも覚えておきましょう。まずは実例通り使ってみると、理解が深まり応用が効くのでおすすめです。

ぜひ使ってみてください。