Excel TRIM関数で消えない謎の半角スペースをVBAでなんとかする

先日、仕事で受け取ったExcelデータにTRIM関数で除去できない謎の半角スペースが混じっていた。

その現象を再現したのがこちら。

A1セルとA2セルは全く同じに見えるが、A1セルの1文字目はトリムできない謎のスペース。A2セルの方は通常のスペースである。

このようにTRIM関数を使っても、スペースが消えてくれない。

VBAで謎のスペースを調査

いったいこのスペースは何なのか。VBAで調べてみた。
まずはふつうにイミディエイトウインドウに出力してみる。

Sub とりあえず出力()
    Debug.Print Range("A1").Value
    Debug.Print Range("A2").Value
End Sub

すると結果は、、んん？

なぜかA1セルのスペースはクエッションマークとして表示されるようだ。

ひと文字だけで試してみる。

Sub 謎のスペースだけ出力()
    謎のスペース = Left(Range("A1").Value, 1)
    Debug.Print 謎のスペース
End Sub

結果は「?」がひと文字表示された。
これってただのハテナなのか？

比較してみる。

Sub 謎のスペースはハテナなのか()
    謎のスペース = Left(Range("A1").Value, 1)
    Debug.Print 謎のスペース = "?"
End Sub

結果はFalse。
ただのハテナではないようだ。
では文字コードを見てみよう。

Sub 謎のスペースの文字コードは()
    謎のスペース = Left(Range("A1").Value, 1)
    Debug.Print Asc(謎のスペース)
End Sub

「63」とのこと。
ASCIIコード表で調べてみると、、
ASCII文字コード - IT用語辞典

やっぱりハテナ。。
ちなみに文字コード同士の比較だと一致する。

Sub 文字コード同士を比較()
    謎のスペース = Left(Range("A1").Value, 1)
    Debug.Print Asc(謎のスペース) = Asc("?")
End Sub

これはお手上げかーと思ったそのとき、最後の一手がひらめいた。
そうだ、文字列とByte型配列は相互置換できるんだった。

Byte型配列を使って謎のスペースの正体にせまる。

ということで、謎のスペースをByte型配列に代入し、その数値を見てみることにした。

Sub バイト型配列を使って正体を暴く()
    Dim 謎のスペース() As Byte
    謎のスペース = Left(Range("A1").Value, 1)
    
    Debug.Print "---謎のスペースのコード---"
    Debug.Print 謎のスペース(0)
    Debug.Print 謎のスペース(1)
    
    Dim ハテナ() As Byte
    ハテナ = "?"
    
    Debug.Print "---ハテナのコード---"
    Debug.Print ハテナ(0)
    Debug.Print ハテナ(1)
End Sub

すると！

でたっ！やっぱByteデータは嘘つかない。

「文字コード 160」でGoogle検索してみたところ、謎のスペースの正体は、HTMLでよく利用される、NBSP(ノーブレークスペース)だった。

ノーブレークスペース - Wikipedia

NBSPを除去する関数を作成

さて、謎のスペースの正体が分かったところで、今度はそれを除去する関数がほしい。
Chr(160)で簡単にできるかなと思っていたけれど、失敗。
ここでもByte配列を使用することにした。
できたのがこちら。

Function NBSP2SP(str As String) As String
    Dim nbsp(0 To 1) As Byte
    nbsp(0) = 160
    nbsp(1) = 0
    NBSP2SP = Replace(str, nbsp, " ")
End Function

実際に使ってみた。

Sub サンプル()
    Debug.Print "--普通にTrim出力--"
    Debug.Print Trim(Range("A1").Value)
    
    Debug.Print "--NBSPを除去してTrim出力--"
    Debug.Print Trim(NBSP2SP(Range("A1").Value))
End Sub

結果はこのとおり。

きれいにTrimされている。

今回のようなケースは、ブラウザからExcelに文字列を張り付けた際に発生する場合がある。
特に、表のマージンをCSSではなくnbspで調整しているケースで、表をそのまま文字選択してExcelに張り付けるような操作をすると発生する。
受け取ったExcelデータにTrimできない余分なスペースがあったら、VBAで除去しよう。

2018/07/18 追記

teratailで以下質問に答えたところhatena19さんから補足をいただいたので紹介。
teratail.com

Unicode文字ならわざわざバイト調べなくても標準のAscW、ChrW関数が使えるとのこと。

Function NBSP2SP(str As String) As String
    NBSP2SP = Replace(str, ChrW(160), " ")
End Function