名前を分割するには
たまに問い合わせフォームから頂く質問で何度か同じような質問がありました。
「名前を姓と名にわけて、それぞれ別のセルに設定するにはどうしたらよいですか？」
質問で多いのが、Split関数を使うところまでは分かるけど、そのあとの書き方が分からない、という内容です。
なので、そのあたりも含めて説明します。
姓名の分割ルール
A列に姓名が列挙されており、B列に姓、C列に名前を入れたい、ということをしたい場合に、結果として以下のような形になります。
ここでは姓と名の判別を行うために、全角スペース、半角スペース、・、セル内改行（LF）、の４つの区切りパターンを用意しています。
なお、セル内の改行は改行コードLF（定数vbLf）が区切り文字になります。
それぞれをSplit関数で区切る場合は以下のように書きます。
変数sには”織田 信長”などの姓名が入るとします。
|
1 2 3 4 5 6 7 8 9 |
Sub SplitName() Dim v Dim s v = Split(s, " ") v = Split(s, " ") v = Split(s, "・") v = Split(s, vbLf) End Sub |
連続してSplit関数を書いてますが、実際にはこういう書き方をすることはほとんど無いと思います。
あくまでも、区切り文字に対するSplit関数の使い方の例です。
海外の姓名
海外の姓名を表記する場合、漢字ではなくカタカナや英字など使うことになります。
英語圏の場合は日本語と異なり、姓名の順の表記ではなく、名→姓の順で表記されます。
A列に設定された時点で名→姓の順の表記になっているかどうかは事前に確認する必要があります。
ここではカタカナや英字の場合は日本語とは逆の、名→姓の順で表記されているとして説明します。
姓と名を別のセルに貼るには
Split関数で分割した姓と名を別のセルに貼り付ける場合は、貼り付ける先のセルをどうやって書くかが問題になります。先にも書きましたが、私が受けた質問では、「貼り付ける先をどうやって書けばいいのかが分からない」、という方が結構おられました。
書き方には２通りあります。
１つはRange.Offsetプロパティを使う方法で、もう１つはCellsプロパティを使う方法です。どちらでもいいのですが、分かりやすさはCellsプロパティの方が上です。ただ、覚えてしまうとOffsetの方が使い勝手がよいと思います。
CellsプロパティはCells(縦座標、横座標)を指定します。A1セルであればCells(1, 1)、B5セルであればCells(5, 2)です。
Range.Offsetプロパティは基準となるセルから見て、どの位置にあたるかを示します。基準となるRangeオブジェクト.Offset(縦位置, 横位置) の書き方で指定します。A1セルを基準とした場合、Range(“A1”).Offset(0, 1)はB1セル、Range(“A1”).Offset(1, 2)はC2セルを表します。
Offsetはマイナスでの位置指定も可能なため、Range(“B2”).Offset(-1, -1)とすると、B2セルから縦に-1、横に-1移動したセルのA1セルを表します。
以下は、A1セルの姓名を全角スペースで分割したあとに、B1セルに姓、C1セルに名を貼り付ける例です。
16行目から20行目まではRange.Offsetでの書き方の貼り付けで、23行目以降はCellsプロパティでの書き方の貼り付けです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Sub SplitName2() Dim v Dim s Dim r As Range '// A1セルのRangeオブジェクトを取得 Set r = Range("A1") '// A1セルの値を取得 s = r.Value '// "織田 信長" '// 区切り文字で分割 v = Split(s, " ") '// Range.Offsetプロパティを使った場合 '// B列に貼り付け r.Offset(0, 1).Value = v(0) '// C列に貼り付け r.Offset(0, 2).Value = v(1) '// Cellsプロパティを使った場合 '// B列に貼り付け Cells(1, 2).Value = v(0) '// C列に貼り付け Cells(1, 3).Value = v(1) End Sub |
正規表現の設定
以下のコードでは正規表現を使うためのRegExpクラスを利用しています。
VBAの画面でツールメニュー→参照設定のダイアログを開き、「Microsoft VBScript Regular Expressions 5.5」にチェックを付けることで正規表現のRegExpクラスが利用できるようになります。
A列の姓名を全てB列とC列に分割する場合
A列に姓名が複数列挙されている場合に、それぞれの姓と名をB列とC列に貼り付ける場合は、A列を１行ずつループして姓と名に分割して貼り付ける処理を行うことになります。
以下の関数を実行すると、A列の姓名を分割してB列とC列に貼り付けます。
姓名がカタカナもしくは英字で構成されている場合は、姓名を逆に貼り付ける必要があるため、正規表現を使ってカタカナもしくは英字の姓名かを判定し、そうであれば姓名を逆に貼り付けるようにしています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Sub SplitName4() Dim v '// Split関数結果 Dim s '// A列の分割前の姓名 Dim r As Range '// 基準Rangeオブジェクト Dim i '// ループカウンタ Dim regKatakana As New RegExp '// カタカナチェック用正規表現 Dim regAlphabet As New RegExp '// 英字チェック用正規表現 Dim sFirstName '// 姓 Dim sLastName '// 名 '// カタカナ正規表現 regKatakana.Pattern = "[｡-ﾟァ-ー]" regKatakana.Global = True '// 英字正規表現 regAlphabet.Pattern = "[a-zA-Z]" regAlphabet.Global = True '// A1セルを基準セル Set r = Range("A1") '// ループカウンタを初期化 i = 0 '// A列ループ Do '// A列の現ループの行位置の姓名を取得 s = r.Offset(i, 0).Value '// 姓名が取得できなかった場合 If s = "" Then '// ここでループを抜ける Exit Do End If '// 各区切り文字を含んでいる場合は、その区切り文字で分割 If (InStr(1, s, " ") > 0) Then v = Split(s, " ") ElseIf (InStr(1, s, " ") > 0) Then v = Split(s, " ") ElseIf (InStr(1, s, "・") > 0) Then v = Split(s, "・") ElseIf (InStr(1, s, vbLf) > 0) Then v = Split(s, vbLf) End If '// 正規表現で姓名がカタカナの場合、または、姓名が英字の場合 If (regKatakana.Test(v(0)) = True And regKatakana.Test(v(1))) Or _ (regAlphabet.Test(v(0)) = True And regAlphabet.Test(v(1))) Then sFirstName = v(1) '// 姓（A列の姓名が逆） sLastName = v(0) '// 名（A列の姓名が逆） Else sFirstName = v(0) '// 姓 sLastName = v(1) '// 名 End If '// B列に姓を貼り付け r.Offset(i, 1).Value = sFirstName '// C列に名を貼り付け r.Offset(i, 2).Value = sLastName '// 次セル用にループカウンタを加算 i = i + 1 Loop End Sub |