エクセルでデータを扱っていると、「フルネームを苗字と名前に分けたい」「スペースで区切られた氏名を姓と名に分割したい」「スペースなしの氏名を分けたい」といった場面に遭遇することはありませんか?
この記事では【Excel】エクセルで苗字と名前を分ける(関数・数式・スペースなしで分割も・氏名を姓と名に分けるなど)方法について解説していきます。
ポイントは
・LEFT関数とFIND関数でスペース区切りの氏名を分割
・「区切り位置」機能で簡単に苗字と名前を分ける
・スペースなしの場合は名前リストとVLOOKUP関数で認識
です。
それでは詳しく見ていきましょう。
目次
エクセルでスペースありの苗字と名前を分ける方法1【LEFT関数とFIND関数で姓を抽出】
エクセルで氏名が「山田 太郎」のようにスペースで区切られている場合、関数を使って苗字と名前を分けることができます。
LEFT関数とFIND関数を組み合わせることで、スペースの位置を見つけて苗字を取り出すことが可能です。
例えば、以下のようなデータがあるとします。
このデータから苗字だけを取り出したい場合、この方法を使えば簡単に実現できるでしょう。
解決方法
具体的な手順を見ていきましょう。
まず、苗字を表示したいセル(例としてB2)に以下の数式を入力します。
=LEFT(A2,FIND(” “,A2)-1)
この数式は、A2セルからスペースの位置を見つけ、その位置の1文字前までを取り出します。
FIND関数でスペースの位置を特定し、LEFT関数でその位置まで文字列を取得するわけです。
数式を下方向にコピーすれば、すべての行で苗字が抽出されます。
次に、名前を取り出すにはRIGHT関数とLEN関数を組み合わせます。
C2セルに以下の数式を入力しましょう。
=RIGHT(A2,LEN(A2)-FIND(” “,A2))
この数式は、全体の文字数からスペースの位置を引いた文字数分を右側から取り出します。
結果として「健」「美咲」「大輔」「花子」「翔太」と名前が取得できます。
【操作のポイント:FIND関数でスペースの位置を特定し、LEFT関数で苗字、RIGHT関数とLEN関数で名前を取り出すことで、氏名を姓と名に分割できます】
全角・半角スペース両方に対応する方法
データに全角スペースと半角スペースが混在している場合は、SUBSTITUTE関数で統一してから処理します。
=LEFT(SUBSTITUTE(A2,” “,” “),FIND(” “,SUBSTITUTE(A2,” “,” “))-1)
この数式は、まず全角スペースを半角スペースに置き換えてから苗字を抽出するため、どちらのスペースにも対応できますね
また、複数のスペースが含まれている場合は、最初のスペースだけを認識して処理されます。
エクセルで区切り位置機能を使って苗字と名前を分ける方法2【「データ」タブの「区切り位置」機能】
関数を使わずに、もっと簡単に氏名を分割したい場合もあるでしょう。
そんな時に役立つのが、エクセルの「区切り位置」機能を使った一括分割です。
この機能を使えば、数式を入力することなく、スペースで区切られた氏名を瞬時に苗字と名前に分けることができます。
例えば、以下のような氏名データを分割したい場合を考えてみましょう。
これらの氏名を一度に苗字と名前に分けたい場合に便利です。
解決方法
「データ」タブをクリックして、分割したい氏名の範囲(A2:A5など)を選択します。
「データツール」グループの「区切り位置」ボタンをクリックします。
「区切り位置指定ウィザード」が表示されるので、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択し、「次へ」をクリックします。
区切り文字の選択画面で「スペース」にチェックを入れ、「次へ」をクリックします。
データのプレビューで正しく分割されていることを確認し、「完了」をクリックします。
氏名が自動的に苗字と名前に分割され、A列に苗字、B列に名前が表示されるわけです。
注意点として、元のデータが上書きされるため、必要に応じて事前にデータをコピーしておくことをおすすめします。
また、右側の列にデータがある場合は上書きされてしまうため、空白の列を確保してから実行しましょう。
全角・半角スペース混在データの処理
全角スペースと半角スペースが混在している場合は、区切り位置機能を実行する前に、置換機能で統一しておくと確実です。
「ホーム」タブの「検索と選択」から「置換」を選択し、全角スペースを半角スペースに一括置換してから区切り位置機能を使用します。
エクセルでスペースなしの苗字と名前を分ける方法3【規則性ありではLEFTやＭID関数：規則性無しでは名前リストとVLOOKUP関数で認識】
スペースがない氏名を分割する場合は、どこで区切るべきか判断が難しいですよね?
そんな時には、名前リストを用意してVLOOKUP関数で認識させる方法が便利です。
あらかじめ一般的な名前のリストを作成し、VLOOKUP関数と組み合わせることで、スペースなしの氏名でも苗字と名前を自動的に分割できます。
例えば、以下のようなスペースなしの氏名データがあるとします。
これらの氏名を正確に苗字と名前に分けたい場合を考えてみましょう。
解決方法(規則がある場合)
まず、規則性がある場合の方法を見ていきます。
例えば「苗字は2文字、名前は残り全部」というルールがある場合、シンプルな関数で処理できます。
「ホーム」タブをクリックして、苗字を表示したいセルに移動します。
B2セルに以下の数式を入力します。
=LEFT(A2,2)
この数式は左から2文字を取り出すため、「佐藤」「鈴木」「高橋」「田中」と苗字が抽出されます。
次に名前を取り出すために、C2セルに以下の数式を入力します。
この数式は3文字目から残りすべてを取り出すため、「健」「美咲」「大輔」「花子」と名前が表示されるわけです。
数式を下方向にコピーすれば、すべての氏名が分割されます。
解決方法(規則がない場合・名前リストを使用)
規則性がない場合は、名前リストを活用します。
まず、別のシート(例:「名前リスト」シート)に一般的な名前のリストを作成しましょう。
次に、元のデータシートに戻り、氏名から名前部分を探すために複数のパターンを試す数式を作成します。
B2セルに苗字を抽出する数式を入力します。
=IF(COUNTIF(名前リスト!$A:$A,RIGHT(A2,1))>0,LEFT(A2,LEN(A2)-1),IF(COUNTIF(名前リスト!$A:$A,RIGHT(A2,2))>0,LEFT(A2,LEN(A2)-2),LEFT(A2,LEN(A2)-3)))
この数式は、まず右端1文字が名前リストにあるかチェックし、なければ右端2文字をチェック、それでもなければ右端3文字・・・とリストを元に名前として扱い、残りを苗字とします。
C2セルに名前を抽出する数式を入力します。
=RIGHT(A2,LEN(A2)-LEN(B2))
この数式は全体の文字数から苗字の文字数を引いた分を右側から取り出すため、名前が抽出されるわけです。
数式を下方向にコピーすれば、名前リストに基づいて氏名が自動的に分割されます。
ただし、この方法は名前リストに含まれていない名前には対応できないため、できるだけ多くの名前をリストに登録しておくことが重要ですね。
また、「佐々木」のように3文字の苗字がある場合は、数式をさらに調整する必要があります。
まとめ エクセルで氏名を姓と名に分ける(関数・数式・スペースなしで分割も・苗字と名前を分ける)方法
エクセルで苗字と名前を分ける方法をまとめると、スペースで区切られている場合はLEFT関数とFIND関数で苗字を抽出しRIGHT関数とLEN関数で名前を取り出すことができ、区切り位置機能を使えば数式なしで簡単に一括分割可能です。
また、スペースなしで規則性がある場合はLEFT関数とMID関数で指定文字数を抽出でき、規則性がない場合は名前リストを用意してVLOOKUP関数やCOUNTIF関数で認識させることで分割できます。
これらの方法を状況に応じて使い分けていけば、ほとんどのケースで氏名分割の問題を解決できるでしょう。
特にスペースありの氏名分割はLEFT関数とFIND関数の組み合わせが最も基本的で汎用性が高いため、まずこの方法をマスターすることをおすすめします。
ただし、スペースなしの氏名分割は完全な自動化が難しいため、重要なデータの場合は分割後に目視確認することも大切かもしれません。
エクセルの文字列操作関数を正しく理解して、効率的なデータ整形を実現していきましょう!
