氏名の間の「空白」を見つけて、これを除去するというものです。

まず、SUBSTITUTE関数、TRIM関数の説明です。

(画像をクリックすると、このサンプルがダウンロードできます)
このシートでは、A列が「不統一に入力された氏名」です。氏名間の空白があるものないもの、全角のもの半角のもの、半角で空白２個のものなどが混在しています。
ここでは氏名だけですが、他の情報（例えば住所とか電話番号）も持つシートだとして、他の用途で氏名でLOOKUP参照しようなどと考えると、参照先も同じ基準で氏名間の空白を入力しているとはとても思えません。 そのままLOOKUP参照すれば、実際には存在しているのに#N/Aになってしまうのが目に見えているわけです。

この問題に気がついて、途中から氏名間の空白は「全角にする」などと統一すると決めたとしても、既に登録済みのデータをどうするかの問題もありますし、運用で「全角にする」としてもどこまで守られるのか判りません。 （マクロで確認するとか、氏名を姓と名を別項目にするとかなら話は別です)

現状のデータをこのまま有効化させるのには、氏名間の空白を外してしまい、姓名の区別のない「氏名」としてしまうのが最も簡単な方法のはずですから、その方法を説明します。
まずは、全角空白を除去する記述です。（B列）

 =SUBSTITUTE($A2,"　","")

R1C1参照形式の場合は、

 =SUBSTITUTE(RC1,"　","")

となります。
SUBSTITUTE関数で、全角空白「"　"」を空文字列「""」に置き換えてしまうのです。
B列の処理結果を見ていただければ解ると思いますが、全角空白はこれで除去できるのですが、半角空白は何も影響を受けません。

では、次に半角空白を除去する記述です。（C列）

 =SUBSTITUTE($A2," ","")

R1C1参照形式の場合は、

 =SUBSTITUTE(RC1," ","")

となります。
全角と同様にSUBSTITUTE関数で、半角空白「" "」を空文字列「""」に置き換えてしまうのです。
C列の処理結果を見ていただければ解ると思いますが、半角空白はこれで除去できるのですが、全角空白は何も影響を受けません。

念のため、TRIM関数の処理も見ておきます。（D列）

 =TRIM($A2)

R1C1参照形式の場合は、

 =TRIM(RC1)

となります。
TRIM関数を誤解している人がいるのであえて提示していますが、TRIM関数はこのような用途には利用できません。 TRIM関数の作用は「余剰な空白の削除」と説明されていますが、複数の連続した空白を１つにするということであって、全ての空白の除去にはなりません。

では、全角空白を除去してその結果から半角空白を除去すれば良いのでは．．．

もう、お気づきとは思いますが、
・全角空白を除去する時、半角空白を含む文字列や空白が元々ない文字列は何も影響を受けない。
・半角空白を除去する時、全角空白を含む文字列や空白が元々ない文字列は何も影響を受けない。
というわけですから、全角空白除去した文字列（B列）から半角空白を除去すれば良いということになり、C列の関数の参照先をA列からB列に変更してみます。 すると、このように全角も半角も全ての空白が除去された氏名ができあがります。

これを１列で一気に行なってみます。

複数の関数を組み合わせて利用する例はこれまでも何度も登場していると思いますが、同様に同じ関数を複数回使用しても構わないわけです。 SUBSTITUTE関数の置換元となる第１引数に別の関数（ここではSUBSTITUTE関数）の処理結果を埋めてしまうわけです。

 =SUBSTITUTE(SUBSTITUTE($A2,"　","")," ","")

R1C1参照形式の場合は、

 =SUBSTITUTE(SUBSTITUTE(RC1,"　","")," ","")

となります。

いかがでしょうか。