文字列の中から任意の文字位置を数値で返すInstr関数とInstrRev関数を使って、セルの中にある不要な文字列を排除するサンプルコードです。
同じことを、Mid関数とLen関数でもできますが、Instr関数やInstrRev関数を使えばより簡単なVBAコードで実装できます。本記事後半では、参考比較のためにMid関数とLen関数を使って同じ処理をさせるVBAサンプルコードを載せております。
目次
不要な文字列を含むレコードが存在するサンプルファイル
それでは今回のサンプルファイルを見てみましょう。
今回の目的は、「仕入担当」データに含まれている()つき情報が不要だとして、それらを取り除くことです。
InStr関数でレコードの中の不要データを削除するVBAサンプルコード
次に今回のVBAコードになりますので、ぜひ真似して書いてみてくださいね。
では、さっそく実行してみてください。上手くいけば下図のようにすべての()付き情報が消えるはずです。
Instr関数で不要な文字列を削除するVBAのアルゴリズム
では、今回のVBAコードのお話しにまいりましょう。
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 | Option Explicit 'Instr関数の使用例 Sub Sample037() Dim wstSelf As Worksheet 'ワークシート用変数 Dim lngERow As Long 'レコード最終行番号 Dim r As Long '行ルーチン処理用 Dim n As Integer '「(」が何番目に出現するかの情報保持用 Set wstSelf = Worksheets("Instr関数") With wstSelf 'レコード最終行取得 lngERow = .Range("A" & .Rows.Count).End(xlUp).Row 'すべてのレコードを処理 For r = 2 To lngERow '「仕入担当」データの中に「(」の文字が何文字目に出現するか If InStr(.Cells(r, 5), "(") <> 0 Then '0文字目(出現しない）以外 '何文字目に出現したかを変数nに代入 n = InStr(.Cells(r, 5), "(") '「仕入担当」データに対し、「n-1」文字分左から抽出した結果を代入 .Cells(r, 5) = Left(.Cells(r, 5), n - 1) End If Next End With End Sub |
10行目： Dim n As Integer
Instr関数からの戻り値を代入するための変数です。Instr関数は、後ほど詳しくお話ししますが、戻り値として「数値」を返します。
22行目： If InStr(.Cells(r, 5), "(") <> 0 Then
If文の条件式になります。右辺の0と左辺のInstr関数の戻り値が等しくない場合、Then以下を実行します。それでは、今回初登場のInstr関数について、次図を使って詳しくお話ししますね。
InStr関数は、ある文字列の中から特定の文字列（検索文字列）を探します。そして、検索文字列がある場合、その位置情報（前から何番目か）を数値で返します。もし検索文字列が無い場合は、戻り値として0を返します。
すなわち、22行目のVBAコードは、InStr関数により検索文字が見つかれば0以外が返ってきますので、その時はThen以下のVBAコードを実行することになります。
25行目： n = InStr(.Cells(r, 5), "(")
10行目で宣言した変数nは、InStr関数の戻り値が代入される変数です。処理対象の文字列先頭から数えて、検索文字「(」が何文字目かが数値で代入されます。
28行目： .Cells(r, 5) = Left(.Cells(r, 5), n - 1)
Left関数により、「仕入担当」データの左から「n-1」文字目までを抽出した結果を「仕入担当」に代入しています。「n」文字目は「(」ですから、「n-1」文字目は「(」の直前の文字までになります。
このVBAコードにより、「仕入担当」データから「(」より後ろすべての文字が取り除かれます。
InStrRev関数でレコードの中の不要データを削除するVBAサンプルコード
今度はInStrREv関数でまったく同じ処理をさせたいと思います。とは言いましたが、先ほどのサンプルコードに対してほんの1部分を変えただけで同じ結果になります。では、サンプルコードをご覧下さい。
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 | 'InstrRev関数の使用例 Sub Sample037_2() Dim wstSelf As Worksheet 'ワークシート用変数 Dim lngERow As Long 'レコード最終行番号 Dim r As Long '行ルーチン処理用 Dim n As Integer '「(」が何番目に出現するかの情報保持用 Set wstSelf = Worksheets("Instr関数") With wstSelf 'レコード最終行取得 lngERow = .Range("A" & .Rows.Count).End(xlUp).Row 'すべてのレコードを処理 For r = 2 To lngERow If InStr(.Cells(r, 5), "(") <> 0 Then '何文字目に出現したかを変数nに代入 n = InStrRev(.Cells(r, 5), "(") '「仕入担当」データに対し、「n-1」文字分左から抽出した結果を代入 .Cells(r, 5) = Left(.Cells(r, 5), n - 1) End If Next End With End Sub |
ご覧の通り、VBAコード20行目の「 n = InStrRev(.Cells(r, 5), "(") 」の中の、「InStr」を「InStrRev」に変えただけです。実行すると同じ結果になります。
InStr関数が検索対象の文字列を前から探すのに対して、InStrRev関数は検索対象の文字列を後ろから探すだけの違いなので、今回の場合、検索対象の文字「(」の位置は変わりませんので、VBAの実行結果も同じになります。
このように、検索対象文字列の中に1つしかない文字列を探す場合は、InStr関数でもInStrRev関数でも結果は同じになります（下図）
Mid関数とLen関数だけで同じ結果を得るVBAサンプルコード
最後に、同じ処理をMid関数とLen関数を使い実行させるVBAコードのサンプルを参考までにご紹介しておきます。アルゴリズムについては、参考記事「VBAコードで文字列から1文字ずつ抽出するMid/Len関数」に詳しく記載しております。
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 | 'Mid関数とLen関数で同じ処理をする Sub Sample037_3() Dim wstSelf As Worksheet 'ワークシート用変数 Dim lngERow As Long 'レコード最終行番号 Dim r As Long '行ルーチン処理用 Dim strNStr As String '最終的に不要情報を除いた文字列を保持する変数 Dim n As Integer '「(」が何番目に出現するかの情報保持用 Set wstSelf = Worksheets("Instr関数") With wstSelf 'レコード最終行取得 lngERow = .Range("A" & .Rows.Count).End(xlUp).Row 'すべてのレコードを処理 For r = 2 To lngERow strNStr = "" For n = 1 To Len(.Cells(r, 5)) If Mid(.Cells(r, 5), n, 1) <> "(" Then strNStr = strNStr & Mid(.Cells(r, 5), n, 1) Else .Cells(r, 5) = strNStr Exit For End If Next Next End With End Sub |
まとめ
本記事では、InStr関数とInStrRev関数の基本的な使い方が分かるサンプルコードをご紹介しました。
InStr関数は、処理対象の文字列の中から、特定の文字列の位置を数値で取得できる関数です。また、InStr関数が文字検索を前から実行するのに対し、InStrRev関数は、後ろから文字検索を実行します。
これまでの関数同様に、文字列データの整形処理に大変便利な関数ですのでぜひ使いこなせるようになってくださいね。