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

スポンサーリンク

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

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

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

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

  • TRIMやReplaceで消せない空白に困っている
  • Webや外部システムから取り込んだデータの整形をしたい
  • ノーブレークスペースの原因と対処法をまとめて知りたい
  1. ノーブレークスペースとは？
  2. ノーブレークスペースが混入する原因
    1. Webサイトからのコピペ
    2. 外部システムからのデータ連携
    3. Wordや他のOfficeアプリからの貼り付け
    4. ChromeやTeamsなどのChromiumアプリからのコピペ
  3. ノーブレークスペースの見分け方
    1. CODE関数で文字コードを確認する
    2. LEN関数とSUBSTITUTE関数で個数を特定する
  4. 条件付き書式でNBSPを含むセルをハイライトする
    1. 設定手順
    2. 複数の見えない空白をまとめてハイライトする
    3. 作業列に「マーカー」を立てる方法
  5. ノーブレークスペースを関数で削除する方法
    1. パターン1: NBSPだけを削除する
    2. パターン2: NBSPと前後の半角スペースをまとめて削除する
    3. パターン3: 制御文字も含めて一括クレンジングする
    4. パターン4: すべてのスペースを削除する
  6. 空白の種類と対処関数の早見表
  7. ノーブレークスペースをVBAで削除する方法
    1. Replace関数でNBSPを削除する
    2. 選択範囲のNBSPを一括削除するマクロ
    3. すべての空白を一括削除するマクロ
    4. VBEの起動方法
    5. WorksheetFunction.TrimではNBSPは削除できない
  8. VLOOKUPやCOUNTIFで不一致が起きたときの対処法
    1. VLOOKUPで一致しないときの確認手順
    2. COUNTIFやSUMIFで集計がずれるとき
    3. XLOOKUPやXMATCHでNBSPが原因で#N/Aになるとき
    4. EXACT関数で”見た目は同じなのに不一致”の原因を特定する
  9. CSV出力時の文字化け対策
  10. 検索と置換（Ctrl+H）でNBSPを一括削除する
    1. テンキーがある場合
    2. テンキーがない場合
  11. Mac版Excelでの注意点
    1. Macでの代替手段
    2. Mac特有のNBSP混入経路
  12. Power QueryでNBSPを削除する方法
    1. GUI操作で削除する手順
    2. M言語のコードで削除する
  13. GoogleスプレッドシートでNBSPを削除する方法
  14. ピボットテーブルでNBSPが引き起こす集計エラーと対策
    1. 起きやすい3つの症状
    2. よくある発生パターン
    3. 対策：ピボット作成前にクレンジングする
  15. Excel 365のREGEXREPLACE関数でNBSPを削除する
    1. NBSPを正規表現で削除する数式
    2. NBSPと半角スペースをまとめて整形する
    3. SUBSTITUTE式との使い分け
  16. ノーブレークスペース以外の「見えない空白」の見分けと削除
    1. 代表的な見えない空白文字の一覧
    2. UNICHAR関数で指定して削除する
    3. Microsoft 365なら正規表現で一括処理する
  17. よくある質問（FAQ）
  18. NBSPを最初から混入させない3つの予防策
    1. 予防策1: テキストエディタ経由でペーストする
    2. 予防策2: 値のみ貼り付け（Ctrl+Shift+V / Alt+E+S+V）を習慣にする
    3. 予防策3: Power Queryを取り込み窓口にする
    4. Q. TeamsやSlackからコピペするとNBSPが入りますか？
    5. Q. EXACT関数でFALSEなのに目視では同じ文字列です。なぜですか？
    6. Q. XLOOKUPでも#N/Aが出ます。VLOOKUPと同じ対処法ですか？
    7. Q. Ctrl+Vで貼り付けるたびにNBSPが入ります。防ぐ方法はありますか？
    8. Q. CODE関数で160と出ました。これは必ずNBSPですか？
    9. Q. SUBSTITUTE+CHAR(160)を入れたのに、VLOOKUPの不一致が直りません。
    10. Q. スマートフォンのメモアプリからコピーするとNBSPが入りますか？
    11. Q. Power QueryのText.TrimでNBSPも消えますか？
    12. Q. 「検索と置換」で削除したのに、まだ空白があります。
    13. Q. NBSP以外にも「見えない空白」はありますか？
    14. Q. Mac版ExcelでもAlt+0160は使えますか？
  19. まとめ
    1. 関連記事
    2. 関数一覧

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

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

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

項目半角スペースノーブレークスペース
文字コード32160
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を挿入できます。知らずに入力されている場合もあるので注意が必要です。

ChromeやTeamsなどのChromiumアプリからのコピペ

近年、業務でよく使われるアプリからのコピペが混入経路として増えています。Chrome・Edge・Teams・Slack・VS Codeはいずれも内部でChromiumベースのレンダリングエンジンを使っています。テキストをコピーするとHTMLもクリップボードに乗るため、Excelに貼り付けたとき、HTML内の   がそのままNBSPとして混入します。

アプリ混入のしやすさ補足
Chrome / Edge高いWebページから直接コピーするとHTMLが同伴する
Microsoft Teams高いチャットメッセージにNBSPが含まれることが多い
Slack中程度環境・バージョンによって差がある
VS Code中程度MarkdownやHTMLファイルの内容をコピーした場合
macOS（Option+Space）高いmacOSの入力ショートカットでNBSPが意図せず入力される

コピペの頻度が多いほど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文字数がわかります。

条件付き書式でNBSPを含むセルをハイライトする

クレンジングする前に、まず「どのセルにNBSPが入っているか」を視覚的に確認したいときに便利な方法です。条件付き書式と組み合わせると、データ全体を見渡しながらNBSPの混入状況を一目で把握できます。

設定手順

  1. NBSPの有無を確認したいセル範囲を選択する（例: A2:A100）
  2. [ホーム] タブ → [条件付き書式] → [新しいルール] をクリック
  3. [数式を使用して、書式設定するセルを決定] を選ぶ
  4. 数式欄に次の式を入力する
=ISNUMBER(SEARCH(CHAR(160),A2))
  1. [書式] ボタンから塗りつぶし色（例: 薄い赤）を選んで [OK] をクリック

これで、A列の中でNBSPを含むセルだけが赤く塗られて表示されます。データの行数が多くても、スクロールしながら混入箇所を確認できます。

複数の見えない空白をまとめてハイライトする

NBSPだけでなく、ゼロ幅スペースや全角スペースなど、複数の「見えない空白」を一括で見つけたい場合は、OR関数で条件を組み合わせます。

=OR(ISNUMBER(SEARCH(CHAR(160),A2)),ISNUMBER(SEARCH(UNICHAR(8203),A2)),ISNUMBER(SEARCH("　",A2)))

NBSP・ゼロ幅スペース・全角スペースのいずれかを含むセルがすべてハイライトされます。

TIP

条件付き書式を設定したまま「検索と置換」でNBSPを削除すると、削除されたセルからハイライトが消えていく様子が見えるので、進捗確認にもなります。クレンジングのビフォーアフターを記録に残したいときにも便利です。

作業列に「マーカー」を立てる方法

条件付き書式の代わりに作業列を追加して、フラグを立てるやり方もあります。

=IF(ISNUMBER(SEARCH(CHAR(160),A2)),"NBSP有","")

該当するセルだけ「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)," ")))

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

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

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

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

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

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

NOTE

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

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

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

空白の種類文字コード原因対処方法
半角スペース32手入力・システム出力TRIM関数
全角スペース12288日本語入力SUBSTITUTE関数で "　" を置換
NBSP160Webコピー・Word貼付SUBSTITUTE(A1,CHAR(160),"")
タブ文字9テキストファイル取込SUBSTITUTE(A1,CHAR(9),"")
改行（LF）10Alt+Enter・CSVSUBSTITUTE(A1,CHAR(10),"")
改行（CR）13Mac系データ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）を開く必要があります。

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

WorksheetFunction.TrimではNBSPは削除できない

VBAで空白を整形しようとして WorksheetFunction.Trim を試す方がいますが、これではNBSPは削除できません。WorksheetFunction.Trim はExcelのTRIM関数と同じ動作で、半角スペース（コード32）の除去と連続スペースの圧縮のみを行います。NBSPには無効です。

' NG: これではNBSPは消えない
cell.Value = Application.WorksheetFunction.Trim(cell.Value)

' OK: ChrW(160)でNBSPを明示的に指定する
cell.Value = Replace(cell.Value, ChrW(160), "")

VBEの通常の Trim 関数も同様で、NBSPは削除されません。VBAでNBSPを消すには、必ず Replace(文字列, ChrW(160), "") を使ってください。WorksheetFunction.Trim と VBA の Trim 関数の違いはVBA WorksheetFunction.Trim の使い方で詳しく解説しています。

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つあります。

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

TIP

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

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

XLOOKUPやXMATCHでNBSPが原因で#N/Aになるとき

Microsoft 365を使っている場合、VLOOKUPの代わりにXLOOKUP関数を使っているケースも多いでしょう。XLOOKUPでもNBSPが混入した検索値は「別の文字列」として扱われるため、同じく #N/A になります。

対処法はVLOOKUPと同じで、検索値をSUBSTITUTEで包みます。

=XLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),B:B,C:C,"該当なし")

XMATCHも同様です。検索値だけでなく、検索範囲にもNBSPが混入している場合は両方をクレンジングする必要があります。

=XMATCH(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),TRIM(SUBSTITUTE(B:B,CHAR(160)," ")))

EXACT関数で”見た目は同じなのに不一致”の原因を特定する

EXACT関数はアルファベットの大文字・小文字まで区別して2つのセルを比較する関数です。NBSPの診断ツールとしても使えます。

=EXACT(A1,B1)

目視では同じ文字列なのにEXACTが FALSE を返す場合、NBSPか制御文字が混入しています。VLOOKUPが一致しない原因を追うときは、まずEXACTで「本当に同じ文字列か」を確認してください。確認できたら、CODE関数で文字コードを調べると効率よく特定できます。

クレンジング後にEXACTで TRUE になったことを確認すれば、削除が完了したかのビフォーアフター検証にも使えます。

CSV出力時の文字化け対策

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

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

検索と置換（Ctrl+H）でNBSPを一括削除する

関数や作業列を作らずに、その場でNBSPを消したいときは、Excelの「検索と置換」が便利です。Ctrl + H で置換ダイアログを開けば、選択範囲やシート全体のNBSPをまとめて削除できます。

数式を残さず元のセルを直接きれいにできるので、「とにかく今すぐ消したい」というときの即効性ある方法です。

テンキーがある場合

テンキー付きのキーボードなら、検索欄に直接NBSPを入力できます。

  1. Ctrl + H で「検索と置換」を開く
  2. 「検索する文字列」の欄にカーソルを置く
  3. Alt キーを押しながら、テンキーで 0160 と入力する
  4. 「置換後の文字列」は空欄のままにする
  5. 「すべて置換」をクリック

Alt + 0160 は、文字コード160のNBSPを直接入力するショートカットです。見た目には何も入力されていないように見えますが、検索欄にはきちんとNBSPが入っています。

NOTE

このショートカットは必ずテンキー（キーボード右側の数字キー）を使ってください。キーボード上部の数字キーでは正しく入力できません。

テンキーがない場合

ノートパソコンなどテンキーがない場合は、削除したいNBSPをセルからコピーして使います。

  1. NBSPが含まれているセルをダブルクリックして編集状態にする
  2. NBSPの部分だけをドラッグで選択して Ctrl + C でコピーする
  3. Ctrl + H で「検索と置換」を開く
  4. 「検索する文字列」の欄に Ctrl + V で貼り付ける
  5. 「置換後の文字列」は空欄のままにする
  6. 「すべて置換」をクリック

実際のNBSP文字をコピーして使うので、文字コードを覚えていなくても確実に処理できます。NBSPと半角スペースが区別しにくくて不安なときも、この方法なら間違いがありません。

TIP

NBSPを残しつつ前後の余分なスペースだけ整えたい場合は、置換後の文字列に半角スペースを1つ入れて「すべて置換」してください。連続スペースの圧縮は、そのあとTRIM関数で仕上げると確実です。

Mac版Excelでの注意点

Mac版Excelを使っている方は、Windowsとは操作が一部異なります。前述のテンキーを使った Alt + 0160 の入力ショートカットは、Mac版Excelでは使えません。

Macでの代替手段

Mac版Excelでは、次のいずれかの方法でNBSPを「検索と置換」で扱えます。

  1. コピペ法を使う: NBSPを含むセルから該当部分をコピーして、検索ダイアログに貼り付ける（前述の「テンキーがない場合」と同じ手順）
  2. 数式バーで =CHAR(160) を計算して値貼り付け: 別のセルに =CHAR(160) を入力し、結果を値としてコピーして検索欄に貼り付ける
  3. 関数またはVBAで処理する: =SUBSTITUTE(A1,CHAR(160),"") を使うか、Mac版でもVBAは動くので前述のマクロをそのまま実行する

NOTE

Mac版Excelの「検索と置換」を開くショートカットは Cmd + H です（Windowsの Ctrl + H 相当）。ダイアログを開いたあとの操作はWindowsとほぼ同じですが、NBSPの直接入力ショートカットは使えない点だけ注意してください。

Mac特有のNBSP混入経路

macOSでは、Option + Space でNBSPが入力できます。文章の途中で「改行してほしくない箇所」に意図的に入れているユーザーもいるので、Macで作成された文書をコピペするとNBSPが混入しやすい傾向があります。Pages・Keynote・Mailなどのアプリ間コピペでも同様です。

Power QueryでNBSPを削除する方法

外部システムのCSVやデータベースから定期的にデータを取り込む方には、Power Queryでの処理がおすすめです。一度設定しておけば、次回からはデータを更新するだけで自動的にNBSPが削除されます。

毎回手作業でクレンジングする手間がなくなるので、繰り返し取り込むデータには特に効果的です。

GUI操作で削除する手順

Power Queryエディター上でメニューを操作するだけで、NBSPを削除できます。

  1. NBSPを削除したい列を選択する
  2. リボンの [変換] タブ → [値の置換] をクリック
  3. [詳細オプション] を展開し、[特殊文字を使用して置換] → [特殊文字を挿入] → [改行なしスペース] を選ぶ
  4. 「検索する値」に #(00A0) が自動で挿入される
  5. 「置換後の値」は空欄のまま（削除する場合）にして [OK] をクリック

#(00A0) は、Power Queryでの文字コード160（16進数で00A0）の表記です。[特殊文字を挿入] から選べば、この記述を手で打たなくても自動で入力されます。

M言語のコードで削除する

数式バー（詳細エディター）に直接書く場合は、M言語の Text.Replace を使います。

= Text.Replace([列名], Character.FromNumber(160), "")

Character.FromNumber(160) がNBSPを指定しています。これを空文字 "" に置き換えることで削除できます。NBSPと前後のスペースをまとめて整えたい場合は、Text.Trim と組み合わせます。

= Text.Trim(Text.Replace([列名], Character.FromNumber(160), " "))

NBSPをいったん半角スペースに変換してから Text.Trim で前後の空白を除去する流れです。Excelの関数で言うと =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) と同じ考え方です。

NOTE

Power Queryの Text.Trim は、半角スペースは除去できますがNBSPは除去できません。これはExcelのTRIM関数と同じ落とし穴です。NBSPを消すには、必ず Character.FromNumber(160) を指定した Text.Replace を先にかけてください。

GoogleスプレッドシートでNBSPを削除する方法

GoogleスプレッドシートでもNBSPに悩まされることがありますが、対処法はExcelとほとんど同じです。

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

Excelとまったく同じ構文で動作します。CHAR(160) でNBSPを指定し、空文字に置き換えるだけです。Excelで使っていた数式をそのままコピーして使えるので、スプレッドシートに移行しても覚え直す必要はありません。

注意点もExcelと共通で、TRIM関数だけではNBSPは削除できません。スプレッドシートでも「TRIMで消えない空白」に出くわしたら、まず CHAR(160) を疑ってみてください。

ピボットテーブルでNBSPが引き起こす集計エラーと対策

NBSPの影響はVLOOKUPだけにとどまりません。ピボットテーブルでも深刻な問題を引き起こします。

起きやすい3つの症状

症状原因見分け方
同じ値なのに別々の行に集計されるNBSPありとなしが「別の文字列」として扱われるピボットの行ラベルに似た値が2行並ぶ
数値フィールドで「グループ化」できないNBSPが混入したセルが「文字列」と判定される「選択対象をグループ化することはできません」エラー
件数が合わない見た目は同じ値なのに個別に集計されるCOUNT/SUMの合計が元データと一致しない

よくある発生パターン

Webからコピーした都道府県名・商品名・担当者名にNBSPが入っていると、ピボットテーブルは「東京」と「東京（NBSP付き）」を別々の項目として集計します。行ラベルに「東京」が2行並び、それぞれに異なる集計値が表示される状態になります。

WARNING

ピボットテーブルを更新しても直りません。元データのNBSPを除去してからテーブルを再作成（または更新）する必要があります。

対策：ピボット作成前にクレンジングする

ピボットテーブルの元データ列にクレンジング済みの作業列を追加し、その列をピボットの行・列・フィルターフィールドに使います。

手順

  1. 元データの隣に作業列（例: D列）を追加する
  2. 作業列に次の数式を入力する
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
  1. 作業列全体を値として貼り付け（Ctrl+C → Alt+E+S+V+Enter）して数式を固定する
  2. ピボットテーブルの参照範囲を元の列から作業列に変更して再作成する

TIP

定期的にデータを更新している場合は、前述の「Power QueryでNBSPを削除する方法」を使うと、更新のたびに自動クレンジングが走るため作業列が不要になります。ピボットテーブルの元データソースをPower Queryの出力テーブルに変更しておくのがおすすめです。

ピボットテーブルの基本操作はExcelピボットテーブルの使い方で解説しています。

Excel 365のREGEXREPLACE関数でNBSPを削除する

Microsoft 365では正規表現関数（REGEXREPLACE・REGEXTEST・REGEXEXTRACT）が追加されました。これを使うと、NBSPを含む複数種類の空白文字を1つの数式でまとめて削除できます。

NOTE

REGEXREPLACE関数はMicrosoft 365（サブスクリプション版）専用です。Excel 2021・2019などの永続ライセンス版では使用できません。

NBSPを正規表現で削除する数式

=REGEXREPLACE(A1,"x{00A0}","")

x{00A0} はNBSPのUnicodeコードポイント（U+00A0）を正規表現で指定する書き方です。これを空文字 "" に置換することでNBSPを削除できます。

NBSPと半角スペースをまとめて整形する

通常の半角スペースも一緒に処理する場合は、文字クラス [...] で両方を指定します。

=TRIM(REGEXREPLACE(A1,"[x{00A0} ]+"," "))

NBSPと半角スペースの「1個以上の連続」をまとめて半角スペース1つに置換し、TRIM関数で前後を整えます。

SUBSTITUTE式との使い分け

方法対応バージョン書きやすさ柔軟性
SUBSTITUTE(A1,CHAR(160),"")すべてのExcel高い1種類ずつ指定
REGEXREPLACE(A1,"x{00A0}","")Microsoft 365のみやや難しい複数パターンを1式で処理可

日常的な用途ではSUBSTITUTE式で十分です。複数種類の特殊文字を一度に処理したい場合や、正規表現に慣れている場合はREGEXREPLACEが有効です。

REGEXREPLACE関数のその他の使い方はREGEXREPLACE関数の使い方で解説しています。

ノーブレークスペース以外の「見えない空白」の見分けと削除

NBSPがいちばん有名ですが、Webや海外システムから取り込んだデータには NBSP以外にも見えない空白文字 が混入することがあります。「CHAR(160)を消しても、まだVLOOKUPが一致しない」というときは、これらを疑ってみてください。

代表的な見えない空白文字の一覧

文字名Unicode10進コード主な出現箇所
ノーブレークスペース（NBSP）U+00A0160Webコピー・Word貼付
ゼロ幅スペースU+200B8203Web HTML・PDF抽出
ゼロ幅非接合子（ZWNJ）U+200C8204アラビア語・ペルシャ語データ
狭い改行なしスペースU+202F8239フランス語ロケールの数値区切り
全角スペースU+300012288日本語入力
BOM（バイトオーダーマーク）U+FEFF65279UTF-8 CSVの先頭セル

UNICHAR関数で指定して削除する

文字コード255までしか扱えないCHAR関数では、ゼロ幅スペース（コード8203）などは指定できません。Excel 2013以降は UNICHAR関数 を使えば、Unicode全範囲を指定できます。

=SUBSTITUTE(A1,UNICHAR(8203),"")

複数種類をまとめて削除したい場合は、SUBSTITUTE関数をネストします。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),UNICHAR(8203),""),UNICHAR(65279),"")

内側から「NBSP削除 → ゼロ幅スペース削除 → BOM削除」と順番に処理しています。

TIP

「何の文字が入っているかさっぱりわからない」というときは、=UNICODE(MID(A1,N,1)) を使うとUnicode値で確認できます。CODE関数は255までしか返せませんが、UNICODE関数は8203などの大きなコードも正しく取得できます。

Microsoft 365なら正規表現で一括処理する

Microsoft 365のREGEXREPLACE関数なら、複数種類の見えない空白を1つの数式で削除できます。

=REGEXREPLACE(A1,"[x{00A0}x{200B}x{200C}x{202F}x{FEFF}]","")

文字クラス [...] の中にUnicodeコードポイントを並べることで、該当する文字すべてを空文字に置換します。複数システムからデータを集約する業務では、この一行で大半の見えない空白を処理できます。

よくある質問（FAQ）

NBSPを最初から混入させない3つの予防策

削除方法を知っておくことも大事ですが、そもそも混入させない習慣をつけると整形作業の手間が減ります。

予防策1: テキストエディタ経由でペーストする

クリップボードにHTMLが乗っている状態のままExcelに貼ると、NBSPが一緒に入ります。ChromeやTeamsからコピーした内容は、一度メモ帳（Windows）やテキストエディット（Macのプレーンテキストモード）に貼り付けてから再コピーしてください。HTML情報が取り除かれて、NBSPのない純粋なテキストになります。

予防策2: 値のみ貼り付け（Ctrl+Shift+V / Alt+E+S+V）を習慣にする

Excelのショートカット Alt+E+S+V+Enter（「形式を選択して貼り付け」→「値」）でペーストすると、書式やHTMLを除いたテキストだけが入ります。Ctrl+Vの代わりにこの操作を習慣にするだけで、Webや他アプリからの混入をかなり防げます。Googleスプレッドシートでは Ctrl+Shift+V が値のみ貼り付けのショートカットです。

予防策3: Power Queryを取り込み窓口にする

定期的にCSVや外部データを取り込む業務なら、Power Queryを入口にして自動クレンジングを組み込むのが根本的な解決策です。一度設定しておけば、毎回手動で整形する必要がなくなります。具体的な設定方法は前述の「Power QueryでNBSPを削除する方法」を参照してください。

NOTE

入力規則（データの入力規則）ではNBSPの混入を防ぐことはできません。入力規則はキーボードからの直接入力を制限する機能であり、貼り付け操作には無力です。予防策は「貼り付け方法の工夫」で対応してください。

Q. TeamsやSlackからコピペするとNBSPが入りますか？

入りやすい傾向があります。TeamsとSlackはChromiumベースのレンダリングを使っており、チャットのテキストをコピーするとHTMLが同伴することがあります。確認するには =CODE(LEFT(A1,1)) で文字コードを見てください。160が出ればNBSPが混入しています。回避するには「テキストエディタ経由でペースト」か「値のみ貼り付け（Alt+E+S+V）」を使うのが確実です。

Q. EXACT関数でFALSEなのに目視では同じ文字列です。なぜですか？

NBSPか制御文字が混入している可能性が高いです。=CODE(MID(A1,N,1)) でN番目の文字のコードを確認し、160や制御文字のコード（0〜31）が出たら特殊文字が隠れています。=SUBSTITUTE(A1,CHAR(160),"") で削除してから再度EXACTで確認してください。

Q. XLOOKUPでも#N/Aが出ます。VLOOKUPと同じ対処法ですか？

はい、同じです。XLOOKUP の検索値を TRIM(SUBSTITUTE(A2,CHAR(160)," ")) で包んでください。=XLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),B:B,C:C,"該当なし") のように記述します。XMATCHも同様に検索値のクレンジングが必要です。

Q. Ctrl+Vで貼り付けるたびにNBSPが入ります。防ぐ方法はありますか？

「値のみ貼り付け」（Alt+E+S+V+Enter）をCtrl+Vの代わりに使う習慣が最も手軽な対策です。または、貼り付け前にメモ帳を経由してHTMLを除去する方法もあります。定期的に取り込むデータであれば、Power Queryをデータ入口にして自動クレンジングを設定しておくと根本的に解決できます。

Q. CODE関数で160と出ました。これは必ずNBSPですか？

はい、文字コード160はNBSPです。確実に削除するには =SUBSTITUTE(A1,CHAR(160),"") を使ってください。160以外の文字コードが出た場合は、前述の「空白の種類と対処関数の早見表」を参照してください。

Q. SUBSTITUTE+CHAR(160)を入れたのに、VLOOKUPの不一致が直りません。

NBSP以外にも見えない文字が混入している可能性があります。次の組み合わせ式で、NBSPと制御文字を一括除去してください。

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

それでも解決しない場合は、=CODE(MID(A1,N,1)) で各文字の文字コードを1文字ずつ調べ、想定外の値がないか確認してください。

Q. スマートフォンのメモアプリからコピーするとNBSPが入りますか？

アプリによって異なりますが、iOSのメモ帳・Slackなど一部のアプリはNBSP（文字コード160）を使って単語間の折り返しを制御しています。「TRIMで消えない」と感じたら、=CODE(LEFT(A1,1)) で文字コードを確認してみてください。

Q. Power QueryのText.TrimでNBSPも消えますか？

消えません。Power QueryのText.TrimはExcelのTRIM関数と同様に半角スペース（コード32）の前後除去のみ対応しており、NBSPには無効です。前述の「Power QueryでNBSPを削除する方法」に記載のText.Replaceを先にかけてください。

Q. 「検索と置換」で削除したのに、まだ空白があります。

複数種類の空白が混在している可能性があります。全角スペース（コード12288）やタブ（コード9）が残っている場合は、それぞれ別途置換が必要です。一度に全種類を処理したい場合は、前述の「すべての空白を一括削除するマクロ」を使うと確実です。

Q. NBSP以外にも「見えない空白」はありますか？

はい、あります。代表的なのはゼロ幅スペース（Unicode U+200B、コード8203）で、Webからのコピーで混入することが多いです。CHAR関数は255までしか対応しないため、UNICHAR関数を使って =SUBSTITUTE(A1,UNICHAR(8203),"") で削除します。詳しくは「ノーブレークスペース以外の『見えない空白』の見分けと削除」の章を参照してください。

Q. Mac版ExcelでもAlt+0160は使えますか？

使えません。Mac版Excelにはテンキーによる文字コード直接入力のショートカットがないため、「コピペ法」か「関数による削除」を使ってください。詳しくは「Mac版Excelでの注意点」の章を参照してください。

まとめ

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

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

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

関連記事

関数一覧

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

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

スポンサーリンク
タイトルとURLをコピーしました