MENU
ゼロから始めるExcel-VBA
  1. ホーム
  2. VBA
  3. 氏名を分けるエクセル関数をVBAでやる方法

氏名を分けるエクセル関数をVBAでやる方法

VBA
データの操作
スポンサーリンク

エクセルで作る住所録などで氏名の姓だけ切り取って別のセルに移したいって思ったことありませんか？

または、氏名の分割したり住所の都道府県だけ取り出したいって事も。

このような分割ができないかと探していたら、ここにたどり着いたと思うので、解決方法をご紹介します。

目次

エクセル関数で氏名を分ける

もっとも一般的に住所録などで氏名を入力しているセルには、苗字と名前の間にスペース（空白）を入れて入力していると思います。

下の図では、セルA列は通し番号、B列が氏名を入浴していて、C列に苗字をD列に名前を、空白を苗字と名前の区切りと判断して自動的に分割するようにします。

氏名を分けるエクセル関数とVBA
日本人に多い苗字と名前を組み合わせてみましたので実在しそうですね

FIND関数とLEFT・RIGHT関数を組み合わせる

エクセル関数を使う方法では、C列とD列それぞれに数式を入力しますので、最初に苗字のC列から始めましょう。

苗字を取り出す

セルC2にエクセル関数を入れるのですが、間違えやすいのがLEFT関数だけを使う場合です。

左側だけ取り出す

=LEFT(B2,3）

これは、「セルB2に入力されている文字を左から数えて3文字目よりも左側の文字を取り出す」という事なので、3文字目は空欄なので「佐藤」と取り出すことに成功します。

とりっぷぼうる
とりっぷぼうる

ところが、9番の「佐々木　洋子」の時はどうなるでしょう？

もうお分かりですよね、3番目の文字は「木」ですから、取り出したら「佐々」さんになってしまうのです。

これでは使い物になりませんので、「空欄を見つけたらその左側を取り出す」と数式を書き換えます。

FIND関数を付け加える

=LEFT(B2,FIND(“　”,B2)-1)

ここで言う空欄とは全角空欄なので””の間が全角分空いていて、最後に-1が無いと、ただ単に空欄を見つけるだけになるので、-1を加えてあげる事により、空欄の文字数から-1した左側を全て苗字として取り出すようになっています。

これで名字が2文字でも3文字でも、もちろん4文字でも全角空欄より左側を認識できるようになっています。

これで完成！ではありません！

もし、氏名のセルが空欄だったら、#VALUE!とエラー表示が出ますので見栄えが凄く悪いので、氏名が未記入の時などのエラー表示に対応させれば完成です。

エラー表示に対応

=IFERROR(LEFT(B2,FIND(“　”,B2)-1),””)

エラー表示に対応するIFERROR関数で囲ってあげて、エラーの時は「””」で返して空欄にするようにしてあります。

名前を取り出す

次に名前を取り出しますが基本的なところは苗字と同じなので、今度は右から取り出せばいいからと言って、「=RIGHT(B2,2)」とやってしまえば名前が2文字の「佐藤　太郎」さんは「太郎」と取り出せますが、10番目の「長谷川　美代子」さんは「代子」にやっぱりなるんです。

さらに厄介なことは、右から数える事により氏名の全体の文字数がバラバラになるという事です。

文字数により、名前が「　洋子」などなぜか空白が取り出されてしまう事があるので、そのままでは使えません。

LEN関数を加える

LEN関数とは、セルの文字数を数えることができ、半角・全角・句読点なんでも区別なく数えることができるんです。

そこで「=RIGHT(B2,FIND(“　”,B2)-1)」とした、赤いマーカーの部分をLEN関数を使って置き換えます。

LEN関数で数える

=RIGHT(B2,LEN(B2)-FIND(“　”, B2))

これで、氏名の文字数から空白セルが右から何文字目にあるか見つけて、ここでは「佐藤　太郎」は空白セル含めて5文字で、空白セルは3文字目なので、5－3で右から2文字を取り出したという事です。

IFERROR関数加えて完成

=IFERROR(RIGHT(B2,LEN(B2)-FIND(“　”, B2)),””)

最後にエラーの時の対応「IFERROR関数を加えて」完成です。

このようにエクセル関数を組み合わせても氏名を分ける事ができますが、この場合C列・D列に延々と数式を入力していかないといけないので、無駄に数式が入っている状態になりますよね。

このような場合は無駄な数式を入力することでエクセルファイルが重くなるのを防ぐために、VBAコードを書いた方が作業が早くデータ量も少ないのでおススメですよ。

VBAで氏名を分ける

VBAを使うならば、ワークシート上にエクセル関数を使った数式を書く必要はありません。

ユーザーフォームに配置するか、ワークシート上に配置したコマンドボタンを1回クリックするだけで、一瞬で指定した範囲内の氏名が自動分割させます。

TextToColumnsメソッド

TextToColumnsメソッドを使う事により、氏名の苗字と名前の間にある空白文字（スペース）を見つけて空白位置で分割して、別々のセルに書き込む（表示）することができるようになります。

氏名を分けるエクセル関数とVBA

では、先ほどと同じB列に2行目から11行目に氏名が表示していますので、氏名を分ける範囲は「B2:B11」となります。

とりっぷぼうる
とりっぷぼうる

もちろん、今後の氏名データ入力数が増える事を想定して「B2:B100」でも作業は一緒で、増やしたからと言って一瞬の分割が5秒に時間がかかるなんてこともありません。

そして、苗字をC列に、名前をD列に分割して書き込むのもエクセル関数の時と同じです。

氏名を分けるエクセルVBA

Private Sub CommandButton1_Click()
Range(“B2:B11”).TextToColumns Destination:=Range(“C2”), DataType:=xlDelimited, Space:=True
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. B2からB11の範囲のセルそれぞれで、分割データの入力先をセルC2を左上端にして表示させ、複数の列に分割データを入力する
  3. 分割位置は空欄文字（スペース）とする
  4. マクロ記録終了

これにより、氏名の間の空白を見つけて、空白より前（苗字）はC列に、空白より後（名前）はC2の隣D2にそれぞれ分割入力されます。

ちなみに、3行目の空白文字以外でも分割できるので、何かの役に立ちそうですね。

Space空白文字
Commaカンマ（,）
Tabタブ

いかがでしたか？それほど難しくは無かったと思います。

このように名前などのデータを扱う場合に新規入力する時は、わざわざ下にスクロールして入力済み最終行を探していませんか？

自動的に最終入力行の次にデータ入力できれば、探す時間が省略でしますよ！

https://tripbowl.com/excel-vba/userform3/

VBA
文字列
目次