読者です 読者をやめる 読者になる 読者になる

t-hom’s diary

主にVBAネタを扱っているブログです。

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

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

その現象を再現したのがこちら。
f:id:t-hom:20160920135926p:plain

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

このようにTRIM関数を使っても、スペースが消えてくれない。
f:id:t-hom:20160920140154p:plain

VBAで謎のスペースを調査

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

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

すると結果は、、んん?
f:id:t-hom:20160920140434p:plain

なぜか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

すると!
f:id:t-hom:20160920142410p:plain

でたっ!やっぱ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

結果はこのとおり。
f:id:t-hom:20160920144107p:plain
きれいにTrimされている。

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

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。