エクセルで氏名を姓と名に分ける方法(スペース区切りの式と、分けられないケース)【LibreOfficeで計算して確認】

名簿の氏名が1つの列に入っていて、姓と名を別の列に分けたいときがあります。この記事では、氏名の間のスペースを目印に分ける式を、14パターンの入力で実際に計算して確かめた結果だけで紹介します。「分けられない行」を見つける方法も入れています。

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/name_split.py)。外部サイトの説明は参照していません。Excelでは未確認です。使う関数はExcelにもありますが、本番のデータに使う前に、数行で結果を確かめてください。

先に結論

姓と名の間にスペースが1つだけあるデータなら、FINDでスペースの位置を探し、LEFTとMIDで切り出せます。
全角スペース(「山田　太郎」)は、先に半角に置き換えてから探します。
「山田太郎」のようにスペースがない氏名は、式では分けられません(どこまでが姓かは、文字からは決まらないため)。この記事の式は、そういう行を「要確認」と出して人が見る形にしています。

手順1: スペースを半角1つにそろえる(B列)

A2に氏名が入っているとして、B2に次の式を入れ、下へコピーします。

=IF(A2="","",TRIM(SUBSTITUTE(A2,"　"," ")))

SUBSTITUTEで全角スペースを半角に置き換え、TRIMで前後のスペースを消して、途中の連続したスペースを1つにします。

手順2: 分けてよい行かを判定する(C列)

=IF(A2="","",IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=1,"OK","要確認"))

スペースの数を「文字数 − スペースを消した文字数」で数え、ちょうど1つなら「OK」、0個や2個以上なら「要確認」にします。

手順3: 姓と名を切り出す(D列・E列)

D2(姓): =IF(C2="OK",LEFT(B2,FIND(" ",B2)-1),"")
E2(名): =IF(C2="OK",MID(B2,FIND(" ",B2)+1,LEN(B2)),"")

「OK」の行だけ、最初のスペースの手前を姓、後ろを名にします。

実際に計算した結果

14パターンを入れて計算しました(▢は全角スペース、□は半角スペースを表します)。

入力	整えた氏名(B)	判定(C)	姓(D)	名(E)
山田□太郎	山田太郎	OK	山田	太郎
山田▢太郎	山田太郎	OK	山田	太郎
山田太郎	山田太郎	要確認
□山田□太郎□(前後にスペース)	山田太郎	OK	山田	太郎
山田□□太郎(スペース2個)	山田太郎	OK	山田	太郎
山田□▢太郎(半角と全角)	山田太郎	OK	山田	太郎
佐々木□花子	佐々木花子	OK	佐々木	花子
ジョン□スミス	ジョンスミス	OK	ジョン	スミス
ジョン□ポール□スミス	ジョンポールスミス	要確認
(空欄)
山田□(姓だけ+末尾スペース)	山田	要確認
田中(U+00A0のスペース)次郎	田中次郎(見た目だけ。下記)	要確認
山田	山田	要確認
高橋▢▢一郎	高橋一郎	OK	高橋	一郎

14パターン×5列の計70件すべて、期待値と一致しました。注目点は3つです。

スペースが2個以上あっても、前後や連続分は手順1で1つにそろうので、「山田□□太郎」は分けられます。
**「ジョンポールスミス」のようにスペースが2か所ある行は「要確認」**になります。どこで姓と名を分けるかは、スペースの数だけでは決められないためです。
**「山田太郎」「山田」は分けられず「要確認」**です。式の結果が空欄になるだけだと見落とすので、判定列を作っておくのが安全です。

見た目はスペースなのに分けられないとき

上の表の「田中(U+00A0)次郎」は、見た目はスペースですが、TRIMでも半角・全角スペースの置き換えでも消えない別の文字(ノーブレークスペース、U+00A0)です。Webからコピーしたデータで入ることがあります。この行は判定が「要確認」になりました。

B列の式を次のように変えると、この文字も半角スペースにそろえられます。

=IF(A2="","",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"　"," "),UNICHAR(160)," ")))

LibreOfficeで計算したところ、「田中(U+00A0)次郎」は「田中次郎」に整いました(UNICHARはExcel 2013以降の関数です。Excelでは未確認)。ほかの種類のスペースは検証していません。

注意点: この式ができないこと

スペースがない氏名(「山田太郎」)を、姓と名に分けることはできません。
漢字の読み(ふりがな)や、外国の名前の姓名の順は判定しません。「要確認」の行は人が見てください。
「ジョンポールスミス」のような3つ以上の並びは、どこで切るかを決めていません。

名簿をまとめて整えたいなら

氏名のスペースの整理だけなら、この記事の式で足ります。会社名の「(株)」を「株式会社」にそろえる、電話番号の記号を取る、といった整形もまとめて関数だけで行いたい場合は、運営者が作った名簿クレンジングシート(Excelテンプレート・500円)があります。姓と名を分ける列は入っていません(この記事の式を自分で足す形になります)。50行までのお試し版は、無料の名簿クレンジングシートで中身を確かめられます。Excelの確認はWindowsデスクトップ版16のみです。

ブラウザだけで試したいときは、無料のスペース一括削除ツールも使えます(入力は外部に送信されません)。

まとめ

整えた氏名は =IF(A2="","",TRIM(SUBSTITUTE(A2,"　"," ")))
判定は「スペースがちょうど1つか」を LEN と SUBSTITUTE で数える
姓は LEFT(…,FIND(" ",…)-1)、名は MID(…,FIND(" ",…)+1,LEN(…))
スペースなし・2か所以上の行は「要確認」にして人が見る
式はいずれもLibreOfficeで計算した結果。Excelでは未確認のため、本番の表に使う前に数行で確かめる

確認方法: この記事の式は、LibreOffice Calcで実際に再計算し、14パターンの入力(半角・全角スペース、前後のスペース、スペース2個、半角と全角の混在、スペースなし、スペース2か所、空欄、U+00A0のスペースなど)について、整えた氏名・判定・姓・名・特殊スペースを直す式の計70件が期待値と一致することを検証スクリプト(site/verify/name_split.py)で確認しました。TRIM・SUBSTITUTE・LEN・FIND・LEFT・MID・IF・UNICHARはExcelにもある関数ですが、Excelでは未確認です。外部サイトの記述は参照していません。

参考にした情報

出典: 筆者がLibreOfficeで計算して確認(検証スクリプト: site/verify/name_split.py)