EXCEL : VBAのTRIM関数と同じ動きをするEXCELのワークシート関数はCLEANであってTRIMじゃない。 そしてNBSP(ノーブレークスペース）について。

VBAのTRIM関数は両端だけスペースを除去しますが、EXCELワークシート関数のそれは、単語の間のスペースも1スペースに詰める動作をします。

つまり、
  A  B  C  
を VBAでTRIMすると
A  B  C
ですが、
ワークシート関数だと
A B C
が帰ってきます。
↑それぞれの文字列のスペースの個数に注目。

VBAのTRIMと同じ動きにしたいなら ワークシート関数の CLEAN を使います。これ、重要。
今までもしかしてうっかり間違ってワークシート関数のTRIMを使っていたところがあるかも。。


そして、さっきのところで実はわざとやったのですが、
  A  B  C  
の文字列には普通のスペースではないNBSP（ノンぶれーキングスペース）を使いました。
理由は、WEBのHTMLで通常のスペースをいくら並べても1つ分のスペースとしてしか表示されませんし、それをブラウザの表示からコピーしても1つのスペースになります。そのため、商品番号などにスペースを複数含むような場合、HTMLで記述する場合は後々コピペされることを考えると注意が必要になります。
ノーブレークスペース - Wikipedia

この文字  A  B  C  をコピーして直接EXCELに貼り付けると 通常のスペースではなく NBSP として張り付きます。
このスペースはTRIMやCLEANでは除去できません。
テキストからスペースや編集記号を削除する - Excel - Office.com

これを除去するためには EXCELのワークシート関数であれば SUBSTITUTE関数で CHAR(160) を対象に置換。VBAであればREPLACE関数で CHRW(160) を対象に置換すればよいことになります。

※なお、NBSPを維持してEXCELに貼り付けるためには、セル編集モードになっていない状態で直接貼り付ける必要があるみたい。
編集モードのセルに貼り付けると普通のスペースになってしまった。当方のEXCEL2010にて。コピー元のブラウザはFirefox.