文字列を抽出する

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

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

名簿の文字列を姓と名に分ける、文字列を分割

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

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

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

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

下処理のしていない名簿の区切りをマクロで統一する

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

Replaceメソッドで文字列の区切りを統一

この場合は、全角スペースを半角スペースに統一し、 スラッシュを半角に統一しなければなりません。
RangeオブジェクトのReplaceメソッドやReplace関数を使うことができます。
以下のコードは、 Replaceメソッドのケースです。
Sub 区切りを統一2() 'Replaceメソッド
  Range("B2:B21").Replace " ", " ", xlPart
  Range("B2:B21").Replace "/", " ", xlPart
End Sub
事前処理が完了したら、本題の文字列の分割にいきましょう。
Replace メソッドの構文と引数の説明
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 メソッドの置換書式を指定します。

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

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

InStr関数は、文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す文字列処理関数です。
InStr関数の構文
文字列を抽出するマクロ1
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
文字列を抽出するマクロ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

VBA関数のReplace関数を使った場合

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