文字列を抽出する
マクロ講座83回VBA関数を使って名簿から文字列を抽出する
名簿から文字列を抽出する VBA関数を使って
名簿の文字列を姓と名に分ける、文字列を分割
名簿などの文字列をマクロのVBA関数を使ってササッと分割しましょう。
VBA関数とは、VBAというプログラミング言語の中に用意されている関数のことです。
VBA関数はふだんExcelで使っているワークシート関数とは別の関数です。
同じ名前でどちらでも使われている関数もある反面、ワークシート関数にあってもVBA関数にはないということもあります。
(サンプルファイルは、こちらから マクロ83回サンプルデータ)
名簿の文字列を分割・・・事前処理
名簿の文字列を姓と名に分けるというような場合、通常はワークシート関数を使うことが多いです。
原本となる表の完成度が高い場合には、あえてマクロを使う必要はないかもしれませんが、
下処理が必要だったり、作業列がつくれない等の場合には、マクロを使って処理しましょう。
下処理のしていない名簿の区切りをマクロで統一する
以下の例のように、名簿の形式が統一されていない場合では、予め、名簿の原本を関数が使えるように加工してやる必要があります。
もし下処理のしていない名簿を扱うときは、マクロの方が便利です。
3行マクロ講座で紹介しているコードをつかって処理しましょう。
もし下処理のしていない名簿を扱うときは、マクロの方が便利です。
3行マクロ講座で紹介しているコードをつかって処理しましょう。
Replaceメソッドで文字列の区切りを統一
この場合は、全角スペースを半角スペースに統一し、
スラッシュを半角に統一しなければなりません。
RangeオブジェクトのReplaceメソッドやReplace関数を使うことができます。
以下のコードは、 Replaceメソッドのケースです。
RangeオブジェクトのReplaceメソッドやReplace関数を使うことができます。
以下のコードは、 Replaceメソッドのケースです。
Sub 区切りを統一2() 'Replaceメソッド Range("B2:B21").Replace " ", " ", xlPart Range("B2:B21").Replace "/", " ", xlPart End Sub事前処理が完了したら、本題の文字列の分割にいきましょう。
Replace メソッドの構文と引数の説明
Range.Replace メソッド (Excel)パラメーター
.Replace メソッドには引数が多いので確認しておきましょう。
.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関数の構文
InStr関数は、文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す文字列処理関数です。
InStr関数の構文
InStr関数の引数
start
省略可能です。各検索の開始位置を表す数式を指定します。 省略すると、先頭の文字から検索されます。引数 start に Null 値が含まれると、エラーになります。 引数 compare を指定した場合は、start も必ず指定します。
string1
必ず指定します。検索先の文字列式を指定します。
string2
必ず指定します。引数 string1 内で検索する文字列式を指定します。
compare
省略可能です。文字列比較の比較モードを表す数値を設定します。 引数 compare を省略するとバイナリモードで比較が行われます。
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関数も利用しました。
VBA関数のReplace関数を使った場合
Replaceメソッドの代わりにReplace関数を使った場合は次のコードになります。
Replace関数の戻り値を入れる変数nameを宣言します。
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