Excelで名前の姓・名の抽出・入れ替えの仕方。ミドルネームが混在している時の抽出も対応

未分類

今回は、Excelで名前の抽出と入れ替えの方法、またミドルネームが混在している状態でも行える方法です。

今回はこのように名前を抽出と入れ替えができるようになります。

今日のブログでできる事画像

今回式をコピーできるように掲載しています。その際の注意事項が2つあります。

  • セルC2がベースになっていますので、セルの数値は使用される環境に合わせてください
  • 「”」(ダブルクォーテーション)はりつけた際、文字の形式が変わる場合がありますのでその際は「”」に修正してご利用ください。

手順

  1. 名前の半角スペースと全角スペースを「半角スペース」に統一
  2. スペースの数をカウント
  3. 名前をラストネーム・ミドルネーム・ファーストネームをそれぞれ抽出
  4. 名と姓を入れ替える

この順番で行っていきます。

手順1:名前の半角スペースと全角スペースを「半角スペース」に統一

今回のデータでは5人全角スペースになっていますので、これを半角スペースに統一していきます。
統一したものはC列です。
(列は特に追加してもしなくてもどちらでも構いません。今回はわかりやすいように列を追加しています。)

名前の半角スペースと全角スペースを「半角スペース」に統一画像

スペースを統一する理由

名前を抽出するためには、何文字目を起点に抜き出すかを判定するため、Find関数を使います。
データで半角スペースと全角スペースが混在していると、Find関数で抜き出せなくなりエラーになります。
また、置換をしておかないと式が長くなりわかりづらくなるのと、ミスにもつながりやすくなりますため、スペースを統一します。

スペースは半角・全角どちらでも大丈夫です。今回は半角スペースで進めさせていただきます。

スペースを統一する方法は大きく2つ

  • 名前の列を選択し、置換する方法
  • 関数で置換する方法

名前の列を選択し、置換する方法が最も簡単です。

関数で置換する方法

SUBSTITUTE関数を使います。
SUBSTITUTEの使い方はSUBSTITUTE(文字列,”検索文字列”,”置換文字列”)となります。
半角スペースに置換する式以下の通り

=SUBSTITUTE(B2,” ”,” “)

手順2:スペースの数をカウント

次にスペースの数をカウントします。
半角スペースの数を基に外国人などミドルネームがある方を判別しています。

  • 1=日本人の名前
  • 2=海外の方のようにミドルネームがある方

今回わかりやすいように列を分けていますが、通常は分けません。

手順2:スペースの数をカウント画像

この数値は関数で判別しています。
式は事こちらの式です。

=LEN(C2)-LEN(SUBSTITUTE(C2,” “,””))

全体の文字数-半角スペースを無くした時の文字数=半角スペースの数

という仕組みです。

手順3:名前をラストネーム・ミドルネーム・ファーストネームをそれぞれ抽出

下図のようにファーストネーム・ミドルネーム・ラストネームをそれぞれ抜き出します。
ミドルネームとラストネームは海外の方の名前と抜き出し方が異なります。半角スペースの数で判定し、条件分岐をChoose関数で行います。

手順3:名前をラストネーム・ミドルネーム・ファーストネームをそれぞれ抽出画像

ファーストネーム抜き出し

ファーストネームを抜き出す式はこちらです。

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

Left関数を使用して左から指定した文字数分抜き出します。
指定した文字数=左からスペースまでの文字数ー半角スペース1文字

ミドルネーム抜き出し

ミドルネームを抜き出す式はこちらです。

=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)))))

一気に難しくなった感じがしますが、やっていることはたった3つです。

  • Choose関数で日本人か海外の人どうかを半角スペースの数で条件分岐
  • 日本人の名前にはミドルネームがないので空白にする
  • 海外の人の名前はミドルネームのみを抽出

Choose関数で1だったら、日本の人の名前、2だったら海外の人の名前としています。
今回の場合、日本人にはミドルネームがないので、空白になっています。

=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関数は「文字列」、「開始位置」、「抜き出す文字数」の3つの情報が必要になります。

文字列は名前(セルC2)を選択し、開始位置は「一つ目の半角スペース後1文字目」を指定しています。
「フルネームの文字数」-「一つ目の半角スペースまで」-「2つ目の半角スペースからラストネームの文字数」=抜き出す文字数
となっています。

マリア デ ピソタを例にすると、開始位置は4文字目なのでFind関数で取得した値+1文字が開始位置になります。

次にフルネームの文字数=3+1+1+1+3=9文字
※半角スペースは1文字としてカウントします。

一つ目の半角スペースまでの文字数4文字

2つ目の半角スペースからラストネームの文字数=4文字

抜き出す文字数=9-4-4=1文字

となります。

2つ目の文字数の抜き出し方ですが、まず2つ目のスペースの位置を特定する必要があります。
それはFind関数を重ねる事で特定が可能です。

=FIND(” “,C2,FIND(” “,C2)+1)

Find関数には開始位置を設定する項目がありますので、一つ目のスペースのあとの文字から検索をするようにします。

2つ目のスペースの位置が特定できたら、フルネームから、2つ目のスペースまでの文字数を引き、引いた数をRight関数で右から文字を抜き出します。

マリア デ ピソタを例にすると、9文字-6文字=3文字を右から抽出
これでミドルネームを抜き出すことができます。

ラストネーム抜き出し

ラストネームを抜き出す式はこちらです。

=CHOOSE(LEN(C2)-LEN(SUBSTITUTE(C2,” “,””)),RIGHT(C2,LEN(C2)-FIND(” “,C2)),RIGHT(C2,LEN(C2)-FIND(” “,C2,FIND(” “,C2)+1)))

仕組みはミドルネームの抽出よりは簡単です。
日本人は1つ目の半角スペースを、海外の人の名前は半角2つ目のスペースを特定し、フルネームの文字数-半角スペースの位置=抜き出す文字数とし、名前の右から文字を抜き出しているだけです。

名と姓を入れ替える

行を増やしたくないという方はこちらをご利用ください。名→姓となっている名前を一発で姓→名の順に入れ替える式です。
名前の抜き出しができれば、あとは順序を変え、&でつなげてあげるだけです。
名前を入れ替える式がこちらです。

=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))

まとめ

いかがでしたでしょうか。

今回の記事もお役立ちなれたらうれしいです。

コメント