サイト内検索

基本 文字列操作関数

VBAコードで文字列から1文字ずつ抽出するMid/Len関数

文字列の中にある不要な情報を、Mid関数とLen関数を使って一気に削除するサンプルコードです。

集計業務をする中で、文字列の中から「任意の長さの文字列を抽出したい」、あるいは「任意の長さの文字列を削除したい」ということがありますよね。今回はそれらの文字列処理を実現するVBAコードのうち、Mid関数とLen関数の基本的な使い方を紹介します。

経験上、任意の文字列を取得するためにMid関数とLen関数を組み合わせて使うことが多いです。ですので、今回は1つの記事でまとめて詳しくお話しします。

では今回のサンプルファイルになります。 サンプルシートの内容は、いつものSampleシートを少しアレンジしたものです。

Mid関数とLen関数解説のためのExcelワークシートサンプル
Mid関数説明用

今回の目的は、VBAで仕入担当のデータから余計な補足データ「(6/21休暇予定)」を取り除くことです。

ここで注目して欲しいのは、仕入担当欄の中に「沖」、「長谷川」という2文字以外の苗字を持つ新しい担当者を入れたことです。 Left関数で紹介したときは、仕入担当は全員2文字でしたので、Left関数を使って簡単に処理できましたよね。

ところが、今回はLeft関数を使って先頭から2文字を取得する手法は使えません。そこでMid関数とLen関数を使います。

Mid関数とLen関数を使えば、苗字の文字長さに関係なく、すべての不要な補足情報を削除できます。

ではその処理をするVBAコードをご紹介しますね。ぜひ真似して書いてみてください。

VBAにMid関数とLen関数を使用したサンプルコード
Mid関数を使用したVBAコードサンプル

では、VBAを実行してみてください。上手くいけば、「仕入担当」列の鈴木さんの横にある補足情報がすべて消えるはずです。

どうでしたか?それでは、VBAコードのお話しに行きましょう。

 

今回の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関数の説明

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関数をやさしく説明
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」へ代入しています。ちょっと分かりずらいかもしれないので、次図でお話ししますね。

文字列削除のロジックをやさしく説明
35行目処理の説明

「仕入担当」データが、"長谷川"のときの例でお話ししてみますね。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文の工夫次第で、さまざまな要件に対する文字列処理が可能になります。

[スポンサーリンク]



サイト内検索

-基本, 文字列操作関数
-

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.