先日、仕事で受け取った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(ノーブレークスペース)だった。
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