今回は、Excelで名前の抽出と入れ替えの方法、またミドルネームが混在している状態でも行える方法です。
今回はこのように名前を抽出と入れ替えができるようになります。
今回式をコピーできるように掲載しています。その際の注意事項が２つあります。
- セルC2がベースになっていますので、セルの数値は使用される環境に合わせてください
- 「”」（ダブルクォーテーション）はりつけた際、文字の形式が変わる場合がありますのでその際は「”」に修正してご利用ください。
手順
- 名前の半角スペースと全角スペースを「半角スペース」に統一
- スペースの数をカウント
- 名前をラストネーム・ミドルネーム・ファーストネームをそれぞれ抽出
- 名と姓を入れ替える
この順番で行っていきます。
手順１：名前の半角スペースと全角スペースを「半角スペース」に統一
今回のデータでは５人全角スペースになっていますので、これを半角スペースに統一していきます。
統一したものはC列です。
（列は特に追加してもしなくてもどちらでも構いません。今回はわかりやすいように列を追加しています。）
スペースを統一する理由
名前を抽出するためには、何文字目を起点に抜き出すかを判定するため、Find関数を使います。
データで半角スペースと全角スペースが混在していると、Find関数で抜き出せなくなりエラーになります。
また、置換をしておかないと式が長くなりわかりづらくなるのと、ミスにもつながりやすくなりますため、スペースを統一します。
スペースは半角・全角どちらでも大丈夫です。今回は半角スペースで進めさせていただきます。
スペースを統一する方法は大きく２つ
- 名前の列を選択し、置換する方法
- 関数で置換する方法
名前の列を選択し、置換する方法が最も簡単です。
関数で置換する方法
SUBSTITUTE関数を使います。
SUBSTITUTEの使い方はSUBSTITUTE(文字列,”検索文字列”,”置換文字列”)となります。
半角スペースに置換する式以下の通り
=SUBSTITUTE(B2,” ”,” “)
手順２：スペースの数をカウント
次にスペースの数をカウントします。
半角スペースの数を基に外国人などミドルネームがある方を判別しています。
- １＝日本人の名前
- ２＝海外の方のようにミドルネームがある方
今回わかりやすいように列を分けていますが、通常は分けません。
この数値は関数で判別しています。
式は事こちらの式です。
=LEN(C2)-LEN(SUBSTITUTE(C2,” “,””))
全体の文字数－半角スペースを無くした時の文字数＝半角スペースの数
という仕組みです。
手順３：名前をラストネーム・ミドルネーム・ファーストネームをそれぞれ抽出
下図のようにファーストネーム・ミドルネーム・ラストネームをそれぞれ抜き出します。
ミドルネームとラストネームは海外の方の名前と抜き出し方が異なります。半角スペースの数で判定し、条件分岐をChoose関数で行います。
ファーストネーム抜き出し
ファーストネームを抜き出す式はこちらです。
=LEFT(C2,FIND(” “,C2)-1)
Left関数を使用して左から指定した文字数分抜き出します。
指定した文字数＝左からスペースまでの文字数ー半角スペース１文字
ミドルネーム抜き出し
ミドルネームを抜き出す式はこちらです。
=CHOOSE(LEN(C2)-LEN(SUBSTITUTE(C2,” “,””)),””,MID(C2,FIND(” “,C2)+1,LEN(C2)-FIND(” “,C2)-LEN(RIGHT(C2,LEN(C2)-FIND(” “,C2,FIND(” “,C2)+1)))))
一気に難しくなった感じがしますが、やっていることはたった３つです。
- Choose関数で日本人か海外の人どうかを半角スペースの数で条件分岐
- 日本人の名前にはミドルネームがないので空白にする
- 海外の人の名前はミドルネームのみを抽出
Choose関数で１だったら、日本の人の名前、２だったら海外の人の名前としています。
今回の場合、日本人にはミドルネームがないので、空白になっています。
=CHOOSE(LEN(C2)-LEN(SUBSTITUTE(C2,” “,””)),””,
上記式の最後の””,の部分が日本の人の名前の対応部分で、空白にしています。
海外の人の名前はミドルネームのみを抽出
=MID(C2,FIND(” “,C2)+1,LEN(C2)-FIND(” “,C2)-LEN(RIGHT(C2,LEN(C2)-FIND(” “,C2,FIND(” “,C2)+1))))
ミドルネームを抜き出す関数はMid関数を使っています。
Mid関数は「文字列」、「開始位置」、「抜き出す文字数」の３つの情報が必要になります。
文字列は名前（セルC2）を選択し、開始位置は「一つ目の半角スペース後１文字目」を指定しています。
「フルネームの文字数」－「一つ目の半角スペースまで」－「２つ目の半角スペースからラストネームの文字数」＝抜き出す文字数
となっています。
マリア デ ピソタを例にすると、開始位置は４文字目なのでFind関数で取得した値＋１文字が開始位置になります。
次にフルネームの文字数＝3+1+1+1+3=9文字
※半角スペースは１文字としてカウントします。
一つ目の半角スペースまでの文字数＝４文字
２つ目の半角スペースからラストネームの文字数＝４文字
抜き出す文字数＝９－４－４＝１文字
となります。
２つ目の文字数の抜き出し方ですが、まず２つ目のスペースの位置を特定する必要があります。
それはFind関数を重ねる事で特定が可能です。
=FIND(” “,C2,FIND(” “,C2)+1)
Find関数には開始位置を設定する項目がありますので、一つ目のスペースのあとの文字から検索をするようにします。
２つ目のスペースの位置が特定できたら、フルネームから、２つ目のスペースまでの文字数を引き、引いた数をRight関数で右から文字を抜き出します。
マリア デ ピソタを例にすると、９文字－６文字＝３文字を右から抽出
これでミドルネームを抜き出すことができます。
ラストネーム抜き出し
ラストネームを抜き出す式はこちらです。
=CHOOSE(LEN(C2)-LEN(SUBSTITUTE(C2,” “,””)),RIGHT(C2,LEN(C2)-FIND(” “,C2)),RIGHT(C2,LEN(C2)-FIND(” “,C2,FIND(” “,C2)+1)))
仕組みはミドルネームの抽出よりは簡単です。
日本人は１つ目の半角スペースを、海外の人の名前は半角２つ目のスペースを特定し、フルネームの文字数－半角スペースの位置＝抜き出す文字数とし、名前の右から文字を抜き出しているだけです。
名と姓を入れ替える
行を増やしたくないという方はこちらをご利用ください。名→姓となっている名前を一発で姓→名の順に入れ替える式です。
名前の抜き出しができれば、あとは順序を変え、＆でつなげてあげるだけです。
名前を入れ替える式がこちらです。
=CHOOSE(LEN(C2)-LEN(SUBSTITUTE(C2,” “,””)),RIGHT(C2,LEN(C2)-FIND(” “,C2))&” “&LEFT(C2,FIND(” “,C2)-1),RIGHT(C2,LEN(C2)-FIND(” “,C2,FIND(” “,C2)+1))&” “&MID(C2,FIND(” “,C2)+1,LEN(C2)-FIND(” “,C2)-LEN(RIGHT(C2,LEN(C2)-FIND(” “,C2,FIND(” “,C2)+1)+1)))&” “&LEFT(C2,FIND(” “,C2)-1))
まとめ
いかがでしたでしょうか。
今回の記事もお役立ちなれたらうれしいです。
コメント