この記事について この記事は、生成AIを活用した自動生成フローで作成しています。Microsoft公式のTRIM関数とデータのクリーンアップ方法を確認し、空白に見える異なる文字を架空の品目コードで比較しました。Excel実機での入力・計算は未確認です。
検証ステータス:📘 公式情報確認済み・Excel実機未確認
ExcelのTRIMで空白が消えない?CHAR(160)とSUBSTITUTEで照合ずれを直す
対応環境・できること・制限事項(公式情報確認:2026年11月17日)
| 項目 | 対象・条件 |
|---|---|
| 対象 | Excel for Microsoft 365、Excel 2024/2021/2019/2016(Microsoft公式TRIM関数の適用先) |
| 操作 | 新規ブックのセルに架空データと数式を入力。マクロ・外部接続・管理者権限は不要 |
| できること | ASCII空白と改行しない空白(160)を区別し、対象を明示して置換する |
| 制限 | Excel実機での表示・計算は未検証。全角空白や他の制御文字は別の問題 |
| 安全 | 原本を上書きせず、別列に正規化結果を作る。実データを外部サービスへ貼らない |
CSVやWeb画面から商品コードを貼り付けたとき、見た目は同じ「AB-12」なのに照合が一致しないことがあります。原因の一つは、前後に付いた空白が普通の半角スペースではなく、Webページなどで使われる「改行しない空白」だからです。ExcelのTRIM関数は万能な空白除去ではありません。Microsoftの説明では、TRIMが対象とするのは文字コード32のASCII空白であり、160の空白は単独では取り除けません。
この違いを知っておくと、XLOOKUPやCOUNTIFの条件が合っているのに一致しないとき、式全体を作り直す前に入力値を点検できます。以下は実データを使わない小さな再現例です。結果は公式仕様から予想されるもので、実機測定値ではありません。
まず安全な新規ブックで比べる
Excelの空のブックを開き、A1に「元データ」、B1に「TRIMのみ」、C1に「160を置換してTRIM」と入力します。A2には次の数式を入れます。
=" "&"AB-12"&" "
A3には次の数式を入れます。
=CHAR(160)&"AB-12"&CHAR(160)
A2は一般的な半角スペースを両端に付けた文字列です。A3は見た目が似ていても、両端が文字コード160です。直接Webページから不可視文字をコピーするより、CHARで生成したほうが実験条件を固定できます。なお、環境によって表示上の空白幅は違って見える可能性があります。
B2に =TRIM(A2)、B3に =TRIM(A3) を入力してください。C2とC3には、それぞれ次の数式を入力します。
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
=TRIM(SUBSTITUTE(A3,CHAR(160)," "))
SUBSTITUTEは指定文字列を別の文字列へ置換します。ここでは160をいったん普通の半角スペースに変え、その後TRIMで前後の空白を除きます。削除するのではなく半角スペースへ変えるのは、文章の途中に160がある場合でも単語同士を無理につなげないためです。
| セル | 期待される内容 | ="AB-12"との比較 |
|---|---|---|
| B2 | AB-12 | 一致する想定 |
| B3 | 両端に160が残るAB-12 | 一致しない想定 |
| C2 | AB-12 | 一致する想定 |
| C3 | AB-12 | 一致する想定 |
比較はD2に =B2="AB-12"、D3に =B3="AB-12"、E3に =C3="AB-12" と入力して確かめられます。期待値は順にTRUE、FALSE、TRUEです。画面に見える文字だけでなく、論理値を観察するのがポイントです。
なぜTRIMだけでは直らないのか
ExcelのTRIMは、先頭・末尾の通常の半角スペースを取り除き、単語間の連続した通常スペースを一つに整えます。しかし「空白らしく見えるすべてのUnicode文字」を消す関数ではありません。Microsoft公式は160の空白を明示的に例外として挙げています。
ここで重要なのは、見た目と内部データを分けて考えることです。照合関数は、画面上で同じ幅に見えるかどうかではなく、セルが保持している文字列を使います。文字コード160が紛れ込んだ品目コードと、何も付いていない品目コードは別の値です。元の照合式にIFERRORを付けてエラー表示だけ消しても、データの不一致は解決しません。
検査したいときは、先頭の文字について =CODE(LEFT(A3,1)) を試す方法もあります。この実験では160が返る想定です。実際のCSVに複数種類の不可視文字が混じる場合、先頭一文字だけでは十分ではありません。末尾や途中の文字、改行、タブ、全角空白なども確認してください。
1条件だけ変えるとどうなるか
A3の式の両方の CHAR(160) を CHAR(32) に置き換えてみます。これでA3は通常の半角スペースを両端に持つ文字列に変わります。B3の =TRIM(A3) だけでも「AB-12」になると予想できます。C3の式は変更しません。変えた条件が「空白の種類だけ」であるため、TRIMが効く境界を比較できます。
反対に、A3を =UNICHAR(12288)&"AB-12"&UNICHAR(12288) に変えると、今度は全角空白が付きます。今回の CHAR(160) 専用の置換では全角空白は対象外です。すべての空白を一括削除する式を安易に使わず、どの文字を正規化するのか決める必要があります。商品名や人名では、意味のあるスペースまで消すと別の問題を作ります。
うまくいかないときの切り分け
数式がそのまま表示される場合は、セルが文字列形式になっていないか、先頭にアポストロフィが入っていないかを確認します。期待したTRUEにならない場合は、まず元データの前後に別の空白や改行が混ざっていないか調べます。C列に置換式を入れたのに変化しない場合は、A列に本当に160が含まれているかを確認してください。文字の種類が違えば、160だけを置換しても効果はありません。
文字コードが160ではない場合は、公式のデータクリーンアップ資料を参照し、CLEANやSUBSTITUTEなどを必要な範囲に限定して組み合わせます。CLEANは万能なUnicode除去関数ではありません。空白以外の文字を含めて一律に削除する前に、何を残すべきかを業務側と合意しましょう。
検索や集計が直らない場合は、正規化後のC列を参照する式になっているかも点検します。元のA列を参照したままでは、C列にきれいな値を作っても検索結果は変わりません。両方の表を照合する場合、片側だけ正規化しても表記揺れが残ることがあります。
業務で使うなら原本と変換結果を分ける
顧客台帳、部品コード、Webから取得した注文一覧では、空白の混入が重複判定やマスタ照合の失敗につながります。ただし、原本の列に上書きするのは避けましょう。「原本」「正規化結果」「変更有無」の三列を残し、=A2=C2 などで差分を把握してから適用します。正規化で同じコードに統合される件数も確認し、識別子が意図せず重複しないか確かめてください。
最初は架空の数行で数式と結果を確認し、次にコピーした少量の業務データで照合結果の差を検証します。問題があれば正規化列の参照をやめるだけで元データへ戻せる構成にします。文字コード160を取り除くことが目的なのではなく、業務で同じものを同じ値として扱えるようにすることが目的です。
公式情報・確認範囲
Microsoft Support:TRIM関数 — 通常の半角スペースと160の違い
Microsoft Support:データのクリーンアップ — TRIM、CLEAN、SUBSTITUTEの使い分け
公式資料の記載内容を2026年11月17日に確認しました。掲載した式のExcel実機での実行、WindowsとMacでの表示差、実業務データでの照合は未検証です。
