マクロ講座83回VBA関数を使って名簿から文字列を抽出する

名簿の文字列を姓と名に分けるというような場合、通常はワークシート関数を使うことが多いです。 原本となる表の完成度が高い場合には、あえてマクロを使う必要はないかもしれませんが、 下処理が必要だったり、作業列がつくれない等の場合には、マクロを使って処理しましょう。

以下の例のように、名簿の形式が統一されていない場合では、予め、名簿の原本を関数が使えるように加工してやる必要があります。
もし下処理のしていない名簿を扱うときは、マクロの方が便利です。
3行マクロ講座で紹介しているコードをつかって処理しましょう。

この場合は、全角スペースを半角スペースに統一し、 スラッシュを半角に統一しなければなりません。
RangeオブジェクトのReplaceメソッドやReplace関数を使うことができます。
以下のコードは、 Replaceメソッドのケースです。

Sub 区切りを統一2() 'Replaceメソッド
  Range("B2:B21").Replace "　", " ", xlPart
  Range("B2:B21").Replace "/", " ", xlPart
End Sub

事前処理が完了したら、本題の文字列の分割にいきましょう。

Replace メソッドの構文と引数の説明

Range.Replace メソッド (Excel)パラメーター
.Replace メソッドには引数が多いので確認しておきましょう。

名前	説明
What	Excel で検索する文字列。
Replacement	置き換える文字列を指定。
LookAt	xlPart：検索テキストの一部を検索します。 xlWhole：検索テキスト全体を検索します。
SearchOrder	xlByColumns：列を下方向に検索してから、次の列に移動します。 xlByRows：行を横方向に検索してから、次の行に移動します。
MatchCase	大文字と小文字を区別して検索するには、True を指定します。
MatchByte	この引数は、Microsoft Excel で 2 バイト言語サポートを選択またはインストールした場合にのみ使用します。 2 バイト文字が 2 バイト文字とだけ一致するようにする場合は、True?を指定します。 2 バイト文字が同等の 1 バイト文字とも一致するようにする場合は、False?を指定します。
SearchFormat	メソッドの検索書式を指定します。
ReplaceFormat	メソッドの置換書式を指定します。

名簿の事前処理が終わったら、次に、名簿の名前を姓と名に分ける作業にとりかかります。 そのためには、区切りの位置を見つける必要がありますが、VBA関数でその役割をするのがInStr関数です。

InStr関数は、文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す文字列処理関数です。
InStr関数の構文

InStr関数の引数
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

区切り位置を見つける事ができれば、Left関数で左側の文字列を抽出、Mid関数で任意の位置から文字列を抽出することができます。

名簿の姓と名を区切りで分割するコードは、次のようになります。

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関数も利用しました。

Replaceメソッドの代わりにReplace関数を使った場合は次のコードになります。
Replace関数の戻り値を入れる変数nameを宣言します。

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