ExcelのTRIMで空白が消えない?CHAR(160)とSUBSTITUTEで照合ずれを直す

VBA・Officeカテゴリを表すパンダのイラスト VBA・Office

この記事について この記事は、生成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"との比較
B2AB-12一致する想定
B3両端に160が残るAB-12一致しない想定
C2AB-12一致する想定
C3AB-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を取り除くことが目的なのではなく、業務で同じものを同じ値として扱えるようにすることが目的です。

公式情報・確認範囲

公式資料の記載内容を2026年11月17日に確認しました。掲載した式のExcel実機での実行、WindowsとMacでの表示差、実業務データでの照合は未検証です。

文書情報

記事タイトル
ExcelのTRIMで空白が消えない?CHAR(160)とSUBSTITUTEで照合ずれを直す
作成日
更新日
Source URL
https://papanda925.com/?p=18713

ライセンス: 本記事のうち、当サイトが権利を有する本文・自作図表は、特記なき限り CC BY 4.0 で利用できます。生成AIを活用して作成・編集した内容を含みます。コードについて、別途ライセンス表示またはリンク先GitHubリポジトリのライセンスがある場合は、その条件を優先します。引用・第三者資料・画像・商標等は本ライセンスの対象外です。 利用ポリシー

タイトルとURLをコピーしました