Excelで消えない空白の正体と消し方｜ノーブレークスペース完全対策ガイド

Webサイトからコピーしたデータに空白が入っていて、TRIM関数で消そうとしたのに消えない。そんな経験はありませんか？

見た目はただのスペースなのに、いつもの方法では消せないのは本当に厄介ですよね。そのまま放置すると、VLOOKUPの不一致や集計ミスの原因になってしまいます。

この記事では、その正体であるノーブレークスペース（NBSP） の見分け方から、関数やVBAでの削除方法まで解説します。

この記事は次のような人におすすめ

TRIMやReplaceで消せない空白に困っている
Webや外部システムから取り込んだデータの整形をしたい
ノーブレークスペースの原因と対処法をまとめて知りたい

ノーブレークスペースとは？
ノーブレークスペースが混入する原因
ノーブレークスペースの見分け方
1. CODE関数で文字コードを確認する
2. LEN関数とSUBSTITUTE関数で個数を特定する
ノーブレークスペースを関数で削除する方法
空白の種類と対処関数の早見表
ノーブレークスペースをVBAで削除する方法
VLOOKUPやCOUNTIFで不一致が起きたときの対処法
1. VLOOKUPで一致しないときの確認手順
2. COUNTIFやSUMIFで集計がずれるとき
CSV出力時の文字化け対策
まとめ
1. 関連記事
2. 関数一覧

ノーブレークスペースとは？

ノーブレークスペース（Non-Breaking Space、略称: NBSP）は、文字コード 160 の特殊な空白文字です。

通常の半角スペース（文字コード 32）とは別の文字として扱われます。見た目はまったく同じスペースなのに、Excelの関数やVBAの標準的な処理では「空白」として認識されません。

項目	半角スペース	ノーブレークスペース
文字コード	32	160
HTMLエンティティ
TRIM関数	削除できる	削除できない
VBA Trim関数	削除できる	削除できない
見た目	スペース	スペース（見分けがつかない）

NOTE
NBSPはもともとHTMLで「この位置では改行しない」ことを指定するための文字です。Webページの文字間隔を調整する目的で使われることが多く、Webからデータをコピーすると混入しやすくなっています。

ノーブレークスペースが混入する原因

NBSPがExcelに入り込む主なルートは次のとおりです。

Webサイトからのコピペ

最も多い原因がWebサイトからのコピー&ペーストです。HTMLソースにが含まれていると、Excelに貼り付けたときにNBSPとして残ります。

外部システムからのデータ連携

基幹システムや会計ソフトからCSVやテキストで出力したデータに含まれていることがあります。システム側がNBSPを空白の代わりに使っているケースです。

Wordや他のOfficeアプリからの貼り付け

Wordで作成した文書をコピーすると、NBSPが混入することがあります。Wordでは Ctrl + Shift + Space で意図的にNBSPを挿入できます。知らずに入力されている場合もあるので注意が必要です。

ノーブレークスペースの見分け方

NBSPは見た目では普通のスペースと区別できません。「空白があるのに消せない」と感じたら、次の方法で確認してみてください。

CODE関数で文字コードを確認する

セルの先頭文字を調べるには、次の数式を使います。

=CODE(LEFT(A1,1))

結果が 160 であればNBSPです。通常の半角スペースなら 32 が返ります。

途中に隠れている場合は、LEN関数で文字数を数えてみてください。見た目の文字数とLEN関数の結果がずれていたら、見えない文字が混入しています。

=LEN(A1)

LEN関数とSUBSTITUTE関数で個数を特定する

NBSPが何個含まれているかを数えるには、次の数式が便利です。

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(160),""))

SUBSTITUTE関数でNBSPを削除した文字列のLENと比較することで、NBSP文字数がわかります。

ノーブレークスペースを関数で削除する方法

見分け方がわかったら、実際に削除してみましょう。ここでは用途別に4つの関数パターンを紹介します。

パターン1: NBSPだけを削除する

最もシンプルな方法です。SUBSTITUTE関数とCHAR関数を組み合わせます。

=SUBSTITUTE(A1,CHAR(160),"")

CHAR(160) がNBSPを指定しています。これを空文字 "" に置き換えることで削除できます。

パターン2: NBSPと前後の半角スペースをまとめて削除する

外部データには、NBSPと通常のスペースが両方入っていることがよくあります。TRIM関数も組み合わせると、まとめてきれいにできます。

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

TIP
ここでのポイントは、NBSPを 空文字ではなく半角スペースに置換 している点です。NBSPを半角スペースに変換してからTRIM関数で処理すると、前後の空白除去と連続スペースの圧縮が同時に行えます。

パターン3: 制御文字も含めて一括クレンジングする

Webデータには改行コードやタブなどの制御文字が混じることもあります。CLEAN関数を加えると、印刷できない制御文字もまとめて削除できます。

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

処理の流れはこうなっています。

SUBSTITUTE: NBSPを半角スペースに変換
CLEAN: 制御文字（文字コード 0〜31）を削除
TRIM: 前後のスペース削除 + 連続スペースの圧縮

パターン4: すべてのスペースを削除する

全角スペース・半角スペース・NBSPをすべて削除したい場合は、SUBSTITUTE関数をネストします。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CLEAN(A1),CHAR(160),"")," ",""),"　","")

ちょっと長く見えますが、やっていることはシンプルです。内側から順に「制御文字の削除 → NBSPの削除 → 半角スペースの削除 → 全角スペースの削除」と処理しています。

NOTE
すべてのスペースを消してしまうと、姓名間のスペースなど必要な空白まで消えてしまいます。用途に応じてパターン1〜3を使い分けてください。

空白の種類と対処関数の早見表

Excelで遭遇する「消せない空白」はNBSPだけではありません。まとめて整理しておきましょう。

空白の種類	文字コード	原因	対処方法
半角スペース	32	手入力・システム出力	TRIM関数
全角スペース	12288	日本語入力	SUBSTITUTE関数で `"　"` を置換
NBSP	160	Webコピー・Word貼付	`SUBSTITUTE(A1,CHAR(160),"")`
タブ文字	9	テキストファイル取込	`SUBSTITUTE(A1,CHAR(9),"")`
改行（LF）	10	Alt+Enter・CSV	`SUBSTITUTE(A1,CHAR(10),"")`
改行（CR）	13	Mac系データ	`SUBSTITUTE(A1,CHAR(13),"")`
制御文字全般	0〜31	システム出力	CLEAN関数

TIP
「何の文字が入っているかわからない」ときは、まず =CODE(MID(A1,N,1)) で1文字ずつ文字コードを確認するのが確実です。Nに位置番号を入れて調べてみてください。

ノーブレークスペースをVBAで削除する方法

大量のデータを処理する場合は、VBAを使うと効率的です。VBAでの空白削除の基本はVBA 文字列の空白を削除する方法まとめでまとめていますが、ここではNBSPに特化した方法を紹介します。

Replace関数でNBSPを削除する

VBAでは ChrW(160) でNBSPを指定できます。

'--- NBSPを削除する基本コード ---
Dim sBefore As String '変換前の文字列
Dim sAfter As String  '変換後の文字列

sBefore = Range("A1").Value
sAfter = Replace(sBefore, ChrW(160), "")

MsgBox "[" & sBefore & "] → [" & sAfter & "]"

NOTE
ChrW はUnicodeの文字コードに対応した関数です。Chr 関数でも160を指定できますが、環境によっては正しく動作しないことがあります。確実に処理するには ChrW を使ってください。

選択範囲のNBSPを一括削除するマクロ

実務で使いやすい、範囲を選択して一括処理するマクロを紹介します。

Sub NBSPを一括削除()

    Dim rng As Range    '処理対象の範囲
    Dim cell As Range   '各セル

    '--- 範囲を選択するダイアログを表示 ---
    Set rng = Application.InputBox( _
        Prompt:="NBSPを削除する範囲を選択してください。", _
        Title:="範囲選択", _
        Type:=8)

    '--- 各セルのNBSPを削除 ---
    For Each cell In rng
        If Not IsEmpty(cell) Then
            cell.Value = Replace(cell.Value, ChrW(160), "")
        End If
    Next cell

    MsgBox "処理が完了しました。"

End Sub

このマクロを実行すると範囲選択のダイアログが表示されます。対象範囲をドラッグで選択すれば、その中のNBSPをまとめて削除できます。

すべての空白を一括削除するマクロ

NBSPだけでなく、半角・全角スペースやタブ・改行もまとめて削除したい場合は、次のマクロが便利です。

Sub すべての空白を一括削除()

    Dim rng As Range    '処理対象の範囲
    Dim cell As Range   '各セル
    Dim sTemp As String '一時変数

    '--- 範囲を選択するダイアログを表示 ---
    Set rng = Application.InputBox( _
        Prompt:="空白を削除する範囲を選択してください。", _
        Title:="範囲選択", _
        Type:=8)

    '--- 各セルの空白を削除 ---
    For Each cell In rng
        If Not IsEmpty(cell) Then
            sTemp = cell.Value
            sTemp = Replace(sTemp, ChrW(160), "") 'NBSP
            sTemp = Replace(sTemp, " ", "")        '半角スペース
            sTemp = Replace(sTemp, "　", "")       '全角スペース
            sTemp = Replace(sTemp, vbCrLf, "")     '改行(CR+LF)
            sTemp = Replace(sTemp, vbLf, "")       '改行(LF)
            sTemp = Replace(sTemp, vbTab, "")      'タブ
            cell.Value = sTemp
        End If
    Next cell

    MsgBox "処理が完了しました。"

End Sub

TIP
姓名間のスペースなど、残したい空白がある場合はNBSPの行（ChrW(160)）だけ残して他の行をコメントアウトしてください。

VBEの起動方法

マクロの実行にはVBE（Visual Basic Editor）を開く必要があります。

Alt + F11 でVBEを起動する
リボンから起動する場合: [開発] タブ → [Visual Basic] をクリック
[開発] タブが表示されていない場合: [ファイル] → [オプション] → [リボンのユーザー設定] → [開発] にチェックを入れて [OK]
VBEが開いたら [挿入] → [標準モジュール] でモジュールを追加し、コードを貼り付ける

VLOOKUPやCOUNTIFで不一致が起きたときの対処法

NBSPが原因で最もよく困るのが、VLOOKUPやCOUNTIFの不一致です。検索値にNBSPが混入していると、見た目は同じ文字列でも「別の値」として判定されてしまいます。

VLOOKUPで一致しないときの確認手順

VLOOKUPで #N/A エラーが出たら、まず検索値と検索範囲の文字コードを比べてみてください。

=CODE(LEFT(A1,1))
=CODE(LEFT(B1,1))

片方が160、もう片方が32であれば、NBSPが原因です。次の数式でNBSPを除去してからVLOOKUPに渡せば解決します。

=VLOOKUP(TRIM(SUBSTITUTE(A1,CHAR(160)," ")),B:C,2,FALSE)

COUNTIFやSUMIFで集計がずれるとき

COUNTIFやSUMIF（条件に合うセルを数える・合計する関数）でも同じ問題が起きます。条件値にNBSPが含まれていると、一致するはずのセルがカウントされません。

対処法は2つあります。

事前クレンジング: 前述のパターン2の数式で列全体のNBSPを除去してから集計する
作業列を追加: =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) の列を作り、その列を集計対象にする

TIP
「数式は合っているのに結果がおかしい」と感じたら、NBSPを疑ってみてください。特にWebからコピーしたデータや外部システムのCSVデータは要注意です。

VBAで空白を一括処理する方法は、VBA 文字列の空白を削除する方法まとめで詳しく解説しています。

CSV出力時の文字化け対策

NBSPが含まれたセルをCSVで保存すると、文字コード 160 が正しく変換されず「?」に化けることがあります。

CSVで出力する前にNBSPを削除しておくのが確実な対策です。前述の関数パターンやVBAマクロで事前にクレンジングしてから保存してください。

まとめ

TrimやReplaceで消せない空白の正体は、文字コード 160 のノーブレークスペース（NBSP）です。

NBSPはWebコピーや外部システム連携で混入しやすい
見た目では普通のスペースと区別できない
=CODE(LEFT(A1,1)) で文字コードを確認すれば判別できる
関数では =SUBSTITUTE(A1,CHAR(160),"") で削除できる
VBAでは Replace(文字列, ChrW(160), "") で削除できる
TRIM・CLEAN関数と組み合わせると一括クレンジングに便利

外部からデータを取り込む機会が多い方は、「CHAR(160)」というキーワードを覚えておくと安心です。ぜひ活用してみてください。

関数一覧

Excel関数の一覧は下記の記事で確認できます。

Excel関数機能別一覧

エラー値が表示される場合は、下記の記事も参考にしてみてください。

Excel関数のエラー値一覧