名簿から文字列を抽出する VBA関数を使って

文字列を分割

名簿などの文字列をマクロのVBA関数を使ってササッと分割しましょう。 VBA関数とは、VBAというプログラミング言語の中に用意されている関数のことです。 VBA関数はふだんExcelで使っているワークシート関数とは別の関数です。 同じ名前でどちらでも使われている関数もある反面、ワークシート関数にあってもVBA関数にはないということもあります。

(サンプルファイルは、こちらから マクロ83回サンプルデータ

名簿の文字列を分割・・・事前処理

名簿の文字列を姓と名に分けるというような場合、通常はワークシート関数を使うことが多いです。 原本となる表の完成度が高い場合には、あえてマクロを使う必要はないかもしれませんが、下処理が必要だったり、作業列がつくれないなどの場合には、マクロを使って処理しましょう。
半角スペースと全角スペースが混在
以下の例のように、名簿の形式が統一されていない場合などでは、予め、名簿の原本を関数が使えるように加工してやる必要があります。 もし下処理のしていない名簿を扱うときは、マクロの方が便利です。3行マクロ講座で紹介しているコードをつかって処理しましょう。
文字列を抽出するマクロ6
この場合は、全角スペースを半角スペースに統一し、 スラッシュを半角に統一しなければなりません。RangeオブジェクトのReplaceメソッドやReplace関数を使うことができます。 以下のコードは、Replaceメソッドのケースです。
Sub 区切りを統一2() 'Replaceメソッド
  Range("B2:B21").Replace " ", " ", xlPart
  Range("B2:B21").Replace "/", " ", xlPart
End Sub
事前処理が完了したら、本題の文字列の分割にいきましょう。

区切り位置を探す・・・InStr関数

次に、名簿の名前を姓と名に分ける作業です。 そのためには、区切りの位置を見つける必要がありますが、VBA関数でその役割をするのがInStr関数です。
InStr関数は、文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す文字列処理関数です。
文字列を抽出するマクロ1
start
省略可能です。各検索の開始位置を表す数式を指定します。 省略すると、先頭の文字から検索されます。引数 start に Null 値が含まれると、エラーになります。 引数 compare を指定した場合は、start も必ず指定します。
string1
必ず指定します。検索先の文字列式を指定します。
string2
必ず指定します。引数 string1 内で検索する文字列式を指定します。
compare
省略可能です。文字列比較の比較モードを表す数値を設定します。 引数 compare を省略するとバイナリモードで比較が行われます。
サンプルの名簿で氏名の入力されているB列の個々の名前からその区切り位置として使われている半角スペースの位置を見つけるコードは 次の様になります。(例ではC列に区切り位置を例として出力しています。)
Sub 区切り位置1()
Dim 位置 As Integer, name As String, i As Long
  For i = 2 To 21
    name = Range("B" & i).Value
    Range("C" & i).Value = InStr(name, " ")
  Next
End Sub
文字列を抽出するマクロ2
区切り位置を見つける事ができれば、Left関数で左側の文字列を抽出、Mid関数で任意の位置から文字列を抽出することができます。
文字列を抽出するマクロLeft関数
文字列を抽出するマクロLeft関数
名簿の姓と名を区切りで分割するコードは、次のようになります。
Sub 区切りを統一してから姓名を分ける()
Dim 位置 As Long, name As String, i As Long
  Range("B2:B21").Replace " ", " ", xlPart '全角スペースを半角スペースに
  Range("B2:B21").Replace "/", " ", xlPart 'スラッシュを半角スペースに
  For i = 2 To 21
    name = Trim(Range("B" & i).Value)
    位置 = InStr(name, " ")
    Range("C" & i).Value = Left(name, 位置 - 1)
    Range("D" & i).Value = Mid(name, 位置 + 1)
  Next
End Sub
名前の前後に余分なスペースがある場合を考慮してTrim関数も利用しました。
文字列を抽出するマクロ4
Replaceメソッドの代わりにReplace関数を使った場合は次のコードになります。
Sub 行ごとに姓名を分ける() 'Replace関数を使って
Dim 位置 As Long, name As String, i As Long
  For i = 2 To 21
    name = Trim(Range("B" & i).Value) 'トリムしておく
    name = Replace(name, "/", " ") 'スラッシュを半角スペースに
    name = Replace(name, " ", " ") '全角スペースを半角スペースに
    位置 = InStr(name, " ")
    Range("C" & i).Value = Left(name, 位置 - 1)
    Range("D" & i).Value = Mid(name, 位置 + 1)
  Next
End Sub
スポンサーリンク
スポンサーリンク