Excelで見た目が同じ文字が一致しないときの直し方｜TRIM・CLEANで空白を除く
商品コードを見比べても同じなのに、VLOOKUPやXLOOKUPでは「見つからない」と出る。重複の色も付かない。そんなときは、貼り付けた文字に空白、改行、タブなどが混ざっていないかを確認します。
公式情報確認：2026年10月8日。対象はExcel for Microsoft 365、Excel 2024、2021、2019、2016です。
配布ファイルは数式と保存値、7種類の境界例、再計算、マクロと外部接続がないことを検査しました。Windows・Mac版Excelの日本語画面と実機操作は未検証です。
1．見た目が同じでも一致しない原因を分ける
Excelの完全一致は、画面で同じように見えるかではなく、セルに入っている文字列が同じかで判定します。例えば PRD-002 と PRD-002 は、後者の末尾に半角空白があるため別の文字列です。
| 混入例 | 見え方 | 主な確認方法 |
|---|---|---|
| 先頭・末尾の半角空白 | セル上では気づきにくい | LEN関数、数式バー、隣列でTRIM |
| 改行されない空白（文字コード160） | 普通の空白に見える | SUBSTITUTEで半角空白へ置換 |
| 全角空白 | 日本語の文字間では自然に見える | SUBSTITUTEで半角空白へ置換 |
| 改行・タブ | 行の高さや位置が少し変わることがある | CLEAN、数式バー |
| 文字途中の本来必要かもしれない空白 | 誤入力か仕様か判断できない | 元資料とコード定義を確認 |
Microsoftのユーザー向け技術コミュニティでも、コピーした文字が重複として判定されない例に対し、余分な空白や印刷されない文字を確認する案内が繰り返し出ています。ただし、コミュニティ回答は公式サポート記事ではありません。本稿では関数の仕様をMicrosoft公式資料で確認し、架空データで再現しています。
2．元データを上書きせず確認列を作る
いきなり検索と置換をかけると、本来必要な空白まで消すおそれがあります。最初は元の列を残し、隣に「整形後コード」列を作ります。
- 業務ファイルではなく、コピーした練習ファイルを開きます。
- 照合できない文字列が入った列の右に空の列を追加します。
- 見出しを「整形後コード」とします。
- 元の文字列と整形後の文字列を左右に並べます。
- 照合結果も別列に出し、直った行と残った行を分けます。
原因確認には =LEN(B6) も使えます。正しいコードが7文字なのに8文字と出る場合は、目に見えない1文字が混ざっている可能性があります。ただしLENだけでは、その1文字が空白、改行、別の記号のどれかまでは分かりません。
3．TRIM・CLEAN・SUBSTITUTEを組み合わせる
今回の実践ファイルでは、E6セルに次の式を入れ、下の行までコピーしています。
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(B6,CHAR(160)," ")," "," ")))TRIMが担当すること
TRIM は先頭と末尾の半角空白を取り除き、文字の途中に連続する半角空白を1個にします。Microsoft公式は、別のアプリから受け取った不規則な空白を整える用途を説明しています。
ただしTRIMが対象とするのは、主に7ビットASCIIの文字コード32です。Webページで使われる文字コード160の「改行されない空白」は、TRIMだけでは除去できないと公式に明記されています。
CLEANが担当すること
CLEAN は、最初の32個の7ビットASCII非表示文字を取り除きます。練習ファイルでは、途中に入れたタブと改行が削除されます。UnicodeにはCLEANだけで除去できない非表示文字もあるため、「CLEANを通せばすべて直る」とは考えません。
SUBSTITUTEが担当すること
SUBSTITUTE は指定した文字を別の文字へ置き換えます。式では、文字コード160と全角空白をいったん普通の半角空白へ変え、その後でTRIMに渡しています。
式を一度に理解しづらい場合は、列を分けても構いません。最初にSUBSTITUTE、次にCLEAN、最後にTRIMと段階を分けると、どの関数で文字が変わったか確認しやすくなります。
4．値に戻す前に照合結果を確認する
実践ファイルのD列は元の文字列をそのまま照合し、F列は整形後の文字列を照合します。No.2からNo.6は、D列では「見つからない」、F列では架空の商品名が表示されます。
- D列とF列を見比べます。
- E列の整形後コードが正しい商品コードと同じか確認します。
- No.7の
PRD -007は、文字途中の空白が残ることを確認します。 - 元資料でNo.7が誤入力だと確認できた場合だけ、個別に修正します。
- 整形結果を固定したい場合は、E列をコピーします。
- 同じ場所または別の確認列へ［値の貼り付け］を使います。
- 保存して開き直し、照合結果が変わらないか確認します。
5．消えない文字と失敗しやすい点
| 失敗しやすい点 | 確認方法 |
|---|---|
| TRIMだけですべての空白が消えると思う | 文字コード160と全角空白はSUBSTITUTEで明示的に置換する |
| CLEANですべてのUnicode非表示文字が消えると思う | 公式が対象外とする文字がある。残る行は元データと文字コードを確認する |
| 文字途中の空白を一括削除する | 商品名や氏名など、本来必要な空白まで消える。コード定義が確定した列だけ個別対応する |
| 元データを直接上書きする | 隣列で整形し、照合結果と件数を確認してから値にする |
| 「見つからない」を空欄で隠す | 不一致件数を数え、残った行を確認できる表示にする |
| 数字の文字列化問題と混同する | 今回は文字の空白・制御文字が対象。数値型と文字列型の違いは別に確認する |
6．無料実践ファイルの確認結果
- 架空データ7件のみを使用
- No.1は整形前後とも一致
- No.2～6は整形前に不一致、整形後に一致
- No.7は途中の空白を保持し、整形後も不一致
- TRIM、CLEAN、SUBSTITUTE、CHAR、VLOOKUP、IFERRORを使用
- 数式エラーなし。保存値と再計算結果を検査
- マクロ、外部リンク、外部データ接続なし
公開前には、利用するExcelの版で日本語の関数入力、値の貼り付け、保存後の再計算を確認してください。会社や顧客の実データを使う前に、必ずコピーと架空データで試します。
確認した公式資料
- Microsoft：TRIM関数（2026年10月8日確認）
- Microsoft：CLEAN関数（2026年10月8日確認）
- Microsoft：SUBSTITUTE関数（2026年10月8日確認）