氏名の間の「空白」を見つけて、これを元に「名字(姓)」と「名前(名)」に分解するというものです。

まず、「作業列」を使って、空白の文字位置を検出します。

(画像をクリックすると、このページのサンプルがダウンロードできます)
まずは、表の領域外の列を「作業列」として利用し、氏名間の空白の文字位置を算出します。
「作業列」とは、複雑な計算式の中間結果を一時的に保持させるセル(列)のことで、この例のように「作業列」で保持させた結果をさらに複数箇所で参照するようなケースでは、 単に計算式の複雑さを回避させるだけでなく、シート全体で効率改善的な効果も発揮させることができます。



ここでの「計算式」は、このようにしてみました。

 =IFERROR(FIND(" ",$A3),IFERROR(FIND("　",$A3),999))

まず、「氏名(分解前)」の文字列(A列)中に半角の空白があるか判断し、あればその文字位置を返します。 なければ同様のことを全角の空白で再度行ない、あればその文字位置を返します。それでもなければ「999」を返します。
空白の文字位置を探すのは「FIND関数」ですが、「あるかどうか」についてはこの「FIND関数」が失敗したかをIFERROR関数」で判断しています。
このサンプルでは、テストケースとして「山田　花子」は姓名間が全角空白、「佐藤 一郎」「鈴木 次郎」は半角空白、「山田三郎」は空白なしとしてあります。

では、空白文字位置の左の「姓」を取り出します。

空白が全角であっても、半角であっても、その文字位置が見つかってしまえば、後は簡単です。
空白の文字位置の１文字手前までが「姓」ですから、

 =LEFT($A3,$D3-1)

とすれば名字(姓)だけを取り出すことができます。

さらに、空白文字位置の右の「名」を取り出します。

「名」の取り出しは「空白の右側の文字列」となりますから、右端から「文字列全体の文字数」－「空白までも文字数」の結果の文字数を取り出せば良いわけです。

 =RIGHT($A3,LEN($A3)-$D3)

一見、これで良さそうなのですが、氏名間に空白がないデータが含まれていた場合は「#VALUE!」となってしまいます。

この対策としては「RIGHT関数」ではなく「MID関数」を使って、検出した空白文字の次の位置から右にある文字列を取り出すようにすれば良いようです。

 =MID($A3,$D3+1,999)

「MID関数」では「取り出し開始文字位置」と「取り出し文字数」を指定しますが、「取り出し文字数」が不定なのであえて大きな数値として「999」を与えて取り出しています。 「RIGHT関数」の場合は「山田三郎」の名前のところで「#VALUE!」となってしまうのですが、「MID関数」ではこのようにブランクとして表示されます。

Excel365ではTEXTBEFORE/TEXTAFTER関数が使えます。

Excel365(Microsoft365のExcel)では、2022年のアップデートによりTEXTBEFORE/TEXTAFTER関数が加わりました。
これは文字列から「区切り文字(文字列でも良い)」を検出して、この「区切り文字」の左側(前)や右側(後)の文字列を取り出してくれる機能ですから、このページの要件にはピッタリです。 しかも、この「区切り文字」では配列定数として指定できるので、このサンプルのように半角空白と全角空白をOR条件のように指定することができます。



Excelのバージョンにより利用可否がありますが、可能なバージョンであれば数式も簡単になり、しかも「区切り文字」の位置を保持するための「作業列」も要らなくなるので、大変便利であり数式の理解も簡単です。



氏名の「姓」の方は、

 =IFERROR(TEXTBEFORE($A3,{" ","　"}),$A3)

となります。

氏名の「名」の方は関数名が換わります。

 =IFERROR(TEXTAFTER($A3,{" ","　"}),"")

氏名間に空白がない場合の処置では、氏名全部を「姓」の方に寄せているので、「名」の方はブランクになるようにします。