文字列の中にある不要な情報を、Mid関数とLen関数を使って一気に削除するサンプルコードです。
集計業務をする中で、文字列の中から「任意の長さの文字列を抽出したい」、あるいは「任意の長さの文字列を削除したい」ということがありますよね。今回はそれらの文字列処理を実現するVBAコードのうち、Mid関数とLen関数の基本的な使い方を紹介します。
経験上、任意の文字列を取得するためにMid関数とLen関数を組み合わせて使うことが多いです。ですので、今回は1つの記事でまとめて詳しくお話しします。
では今回のサンプルファイルになります。 サンプルシートの内容は、いつものSampleシートを少しアレンジしたものです。
今回の目的は、VBAで仕入担当のデータから余計な補足データ「（6/21休暇予定）」を取り除くことです。
ここで注目して欲しいのは、仕入担当欄の中に「沖」、「長谷川」という2文字以外の苗字を持つ新しい担当者を入れたことです。 Left関数で紹介したときは、仕入担当は全員2文字でしたので、Left関数を使って簡単に処理できましたよね。
ところが、今回はLeft関数を使って先頭から2文字を取得する手法は使えません。そこでMid関数とLen関数を使います。
Mid関数とLen関数を使えば、苗字の文字長さに関係なく、すべての不要な補足情報を削除できます。
ではその処理をするVBAコードをご紹介しますね。ぜひ真似して書いてみてください。
では、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 33 34 35 36 37 38 39 40 41 42 43 | Option Explicit 'Mid関数の使用例 Sub Sample037() Dim wstSelf As Worksheet 'ワークシート用変数 Dim lngERow As Long 'レコード最終行番号 Dim strSelf As String '仕入担当用変数 Dim r As Long '行ルーチン処理用 Dim i As Integer '仕入担当データ文字数ルーチン用 Set wstSelf = Worksheets("Mid関数") With wstSelf 'レコード最終行取得 lngERow = .Range("A" & .Rows.Count).End(xlUp).Row 'すべてのレコードを処理 For r = 2 To lngERow strSelf = "" '変数初期化 '「仕入担当」の先頭1文字目から処理する For i = 1 To Len(.Cells(r, 5)) If Mid(.Cells(r, 5), i, 1) = "(" Then '「(」か0文字の場合 Exit For 'For ～ Next iの処理を中断し抜ける Else 'それ以外 '1文字ずつ変数「strSelf」に入れる strSelf = strSelf & Mid(.Cells(r, 5), i, 1) End If Next i .Cells(r, 5) = strSelf Next r End With End Sub |
今回のVBAコードの処理内容ですが、まず全てのレコードの「仕入担当」データを先頭から1文字ずつ取り出します。そして、補足情報の始まりの文字「(」が出たら繰り返し処理を中断しループを抜けます。
その1文字ずつ取り出すために、Len関数とMid関数を使います。ではVBAコードのお話しです。
8行目：Dim strSelf As String
ループ処理により1文字ずつ取り出された「仕入担当」データの格納先変数になります。
12行目：Dim i As Integer
「仕入担当」データを1文字ずつ取り出すために、「仕入担当」データの文字長さ分だけ繰り返し処理する必要があります。そのルーチン処理に使う変数です。
23行目：strSelf = ""
各レコードの処理を始める前に、変数の初期化のために実行されるVBAコードです。この後のVBAコードで「仕入担当」データの先頭文字から1文字ずつ取り出して、変数「strSelf」に格納します。
26行目：For i = 1 To Len(.Cells(r, 5)) ～ Next i
1レコードに対する繰り返し処理になります。iは「仕入担当」データの何文字目を処理しているのかを格納する変数です。では初登場のLen関数について、次図で詳しくお話しします。
Len関数の使い方は、()内に処理対象の文字列を入れるだけです。そしてLen関数は「数値」を戻り値として返します。よって、For ～ Next i で繰り返す回数は、上の例では「沖」の場合は1回、「長谷川」の場合は3回となります。
ここで補足です。今回のFor文「For ～ Next i 」のNextの次にループ処理用に宣言した「i」をつけています。
For文の正式な書き方になるのですが、省略もできます。今回は、For文がネスティング（入れ子状態）しているので、あえて書きました。
28行目：If Mid(.Cells(r, 5), i, 1) = "(" Then
Ifの条件文になります。左辺が右辺「(」と等しい場合は、Then以下のVBAコードを実行することになります。左辺には今回初登場のMid関数がありますので、次図で詳しくお話しします。
今回は、Mid関数を使って「仕入担当」データの左から1文字ずつ取り出し、それが「(」と等しいか（True）、異なるか（False）を順次比較しています。
30行目：Exit For
28行目の条件式でTrue、すなわち「仕入担当」データからMid関数で取り出した1文字が、「(」に等しい場合に実行されるVBAコードです。
「Exit For」は、英語の他動詞「～抜ける、終了させる」の意味そのもので、目的語を「For」に取りますので「For文を抜ける」とも解釈できます。つまり、実行中のFor文を中断し処理を抜けることになります。
繰り返しになりますが、実行中のFor～Nextによる繰り返し処理を中断し、「Next以降に抜ける」というVBAコードです。この例では、抜ける先は38行目「Next i」の次の行になります。
32行目：Else
28行目のIf文の条件式でFalse（「(」と異なる）の場合に、Else以降が実行されます。Elseの使い方については、 「VBAコードの分岐条件(IF文) 」の記事でもご紹介しております。
35行目：strSelf = strSelf & Mid(.Cells(r, 5), i, 1)
直前の32行目の条件が成立した際に実行されるVBAコードになります。「Mid(.cells(r, 5), i, 1)」は前述の通り、「仕入担当」データの1文字分の文字列データです。
取り出した1文字分の文字列を、変数「strSelf」とつなげて、それを変数「strSelf」へ代入しています。ちょっと分かりずらいかもしれないので、次図でお話ししますね。
「仕入担当」データが、"長谷川"のときの例でお話ししてみますね。i=1のとき、右辺の「strSelf」は、VBAコード23行目によって初期値「""（長さ0の文字列）」になってます。
その初期値「""」とMid関数で取り出された"長"の文字列を「&（アンバサンド）」でつないで、それを左辺「strSelf」に代入しています。よって、i=1の処理終了時点では「strSelf」は"長"になります。
次のi=2のとき、右辺の変数「strSelf」にはi=1の処理後の値”長"が入ってます。それにMid関数で取り出された"谷"の文字をつなげると、右辺は"長谷"になりこれが左辺「strSelf」に代入されます。
よって、i=2の終了時点では変数「strSelf」には"長谷"が入ったことになります。以下i=3の場合も考え方は同じですので、図をみながら確認してみてくださいね。
40行目：.Cells(r, 5) = strSelf
直前のFor ～ Next iによって、1レコード分の「仕入担当」データの抽出が完了しました。変数「strSelf」には抽出された「仕入担当」のデータが入ってますので、その抽出結果を「仕入担当」のセル.Cells(r, 5)に戻します。
ポイントは、28行目のMid関数で「仕入担当」の文字列を1文字ずつ取り出し確認しながら、目的の「(」を探していることです。
そして、「(」が見つかったときに変数「strSelf」に格納するループ処理を中断し、それまでの変数「strSelf」に格納されたデータを「仕入担当」欄に戻しています。結果的に、「(」以降の文字が削除されたことになります。
まとめ
今回のサンプルシートは、仕入担当の苗字が1～3文字とばらつきがありましたが、Mid関数とLen関数を使って()付の不要な情報をきれいに削除できました。今回紹介した基本的な使用例をベースに、さらに複雑な文字列に対しても、For文とIf文の工夫次第で、さまざまな要件に対する文字列処理が可能になります。