「127件中、58件が#N/A」
これは、研究員が実際に画面で見た数字です。Webの管理画面に表示されていた支払先の一覧をコピーして、社内のマスタと突き合わせるだけの作業。10分で終わるはずでした。
ところが、半分近くが一致しない。目で見比べても、どこからどう見ても同じ「株式会社さくら商会」です。試しにTRIM関数をかけてみましたが、結果は変わりません。関数の引数を疑い、範囲を疑い、シートを作り直してもダメでした。
原因が分かったのは、LEN関数で文字数を数えたときでした。9文字のはずの文字列が、10文字と表示されたのです。
セルの中に、目には見えない「もう1文字」が座っていました。
この記事では、その見えない1文字をどうやって見つけ、どうやって取り除くのかを、実務で使う順番どおりに整理していきます。全部を読まなくても大丈夫です。まずは最初の「30秒テスト」だけやってみてください。
まず30秒。犯人がいるかどうかを確かめる
原因を特定する前に、そもそも見えない文字が入っているのかどうかを確定させます。空いているセルに、次の2本を入力してください。A2に、照合できなかったデータが入っている前提です。
=LEN(A2)
=LEN(TRIM(CLEAN(A2)))LEN関数は文字数を数える関数、TRIM関数とCLEAN関数は余計なものを取り除く関数です（この2つはあとで詳しく説明します）。
見るべきポイントは、2つの数字が同じかどうか、それだけです。
| 上と下の結果 | 意味 | 次にやること |
|---|---|---|
| 同じ数字 | 余計な空白・制御文字は入っていない | 別の原因を疑う（後述のリンク参照） |
| 下の方が小さい | 見えない文字が混入している | このまま読み進めればOK |
| 下の方が小さいのに直らない | TRIM・CLEANでは落ちない文字がいる | 「段階4」まで進む |
もう一押し確定させたいときは、照合先のマスタに対してCOUNTIF関数を2回打ちます。
=COUNTIF(マスタの範囲, A2)
=COUNTIF(マスタの範囲, TRIM(CLEAN(A2)))上が0で、下が1以上になったら、犯人は確定です。関数の書き方でも、マスタの不備でもありません。あなたが貼り付けたデータの中に、余計な文字が同居しています。
この2行で切り分けられるようになると、「原因探しに30分かけて、直すのは10秒だった」という時間の使い方から抜け出せます。
見えない文字は「バグ」ではなく「正しく保存された情報」
ここでひとつ、誤解を解いておきたいことがあります。
見えない文字は、Excelの不具合でも、コピーの失敗でもありません。コピー元にもともと存在していた情報が、そのまま正確に運ばれてきた結果です。
たとえばWebページの見た目で「株式会社さくら商会」と1行に見えていても、裏側のHTMLでは改行やスペースを挟んで書かれていることがあります。ブラウザはそれを「見た目上は1行」として表示してくれますが、コピーしたときに運ばれるのは、見た目ではなく中身です。
Excelは受け取った文字を、律儀にすべて保存します。改行も、タブも、システム固有の制御コードも、「そういうデータだ」として扱います。
だから、人間の目には同じに見えても、Excelにとっては別の文字列です。VLOOKUPもCOUNTIFもXLOOKUPも、完全一致とは「1文字たりとも違わないこと」を指しています。9文字と10文字は、一致しません。
この考え方は、このブログで何度も出てくる「見た目と中身は別物」という原則そのものです。セルの先頭に空白が入っているだけで関数が止まるのも、まったく同じ理屈で起きています。
同じ原則で起きる代表例は先頭の空白が原因で関数が動かないケースです。こちらを先に読むと、今回の話がより腑に落ちると思います。
実際に紛れ込むのは、だいたいこの7種類
「見えない文字」とひとくくりにされがちですが、実務で遭遇するものは、ほぼ決まっています。研究員が現場で出会った頻度が高い順に並べると、次のとおりです。
| コード番号 | 正体 | よく来る場所 | TRIMで消える | CLEANで消える |
|---|---|---|---|---|
| 10 | 改行（LF） | Web、メール、セル内改行 | × | ○ |
| 13 | 復帰（CR） | 基幹システム、テキストファイル | × | ○ |
| 9 | タブ | テキストファイル、PDF | × | ○ |
| 32 | 半角スペース | あらゆる場所 | ○ | × |
| 12288 | 全角スペース | 日本語の入力フォーム | 環境による | × |
| 160 | ノーブレークスペース | Web、Word、PDF | × | × |
| 8203 | ゼロ幅スペース | Web、翻訳ツール経由 | × | × |
表の右2列が、この記事のいちばん大事なところです。
TRIM関数が担当するのは「スペース」、CLEAN関数が担当するのは「制御文字」。担当が分かれているので、片方だけでは取りこぼします。そして最後の2つ、ノーブレークスペース（160番）とゼロ幅スペース（8203番）は、どちらの関数でも消えません。
「TRIMもCLEANも試したのに直らない」という状況の正体は、ほぼこの2つです。
なお全角スペース（12288番）については、TRIMで消えたという報告と消えなかったという報告の両方があります。バージョンや環境によって挙動が変わる可能性があるため、研究員としては「TRIMに任せない」という方針をおすすめします。確実に消したいなら、後述のSUBSTITUTE関数で名指しするのが安全です。
犯人の番号を特定する
どの文字が入っているかまで突き止めたい場合は、UNICODE関数を使います。文字を数字（コード番号）に変換してくれる関数です。
末尾の1文字を調べるなら、こう書きます。
=UNICODE(RIGHT(A2,1))先頭の1文字なら、こうです。
=UNICODE(LEFT(A2,1))返ってきた数字を、さきほどの表と照らし合わせてください。10なら改行、160ならノーブレークスペース、8203ならゼロ幅スペースです。ちなみに普通の日本語が入っていれば、12354（あ）のような大きな数字が返ってきます。
文字列の途中に潜んでいる場合は、1文字ずつ分解します。B列以降に、次の式を横方向にコピーしてください。
=UNICODE(MID($A$2, COLUMN()-1, 1))「りんご」の3文字なら、12426・12435・12372 と並ぶはずです。ここに10や160が混ざっていれば、その位置が侵入地点です。
ここまでやる必要がある場面は多くありません。ただ、「毎回このシステムから出したデータだけおかしい」というときに一度だけ調べておくと、翌月からは迷わず対処できます。研究員も、経費精算システムの出力に13番（復帰）が必ず入ることを一度確認してから、毎月の作業がかなり楽になりました。
どこから入ってきたのか：発生源の4パターン
犯人の番号が分かると、どこから混入したかも見えてきます。
ケースA：Webの管理画面・社内ポータルからのコピー もっとも多いパターンです。改行（10番）やノーブレークスペース（160番）が主犯。HTMLで整形された文字列を、そのまま持ってきたときに起こります。表形式で表示されている画面をドラッグコピーすると、ほぼ確実に何かが付いてきます。
ケースB：PDFからの転記 PDFの中では、文字は「この位置にこの字を置く」という配置情報として保持されています。そのためコピーすると、行の切れ目がタブ（9番）や改行（10番）として付いてくることがあります。請求書や納品書からの転記で多発します。
ケースC：基幹システムからのCSV出力 復帰（13番）と改行（10番）がセットで付いてくるのが典型です。システムの内部仕様がそのまま出力に反映されているため、こちらでは防ぎようがありません。毎回同じ場所に同じものが入るので、対処を定型化してしまうのが正解です。
ケースD：メール・チャットからのコピー 本文が自動で折り返されている場合、その折り返し位置に改行が入ることがあります。また、Wordで作られた文書を経由すると、ノーブレークスペースが紛れ込みます。
この4つのうち、自分がどれに当たるかを把握しておくと、「またこの作業か」というときに最初からクリーニングを挟めるようになります。
直し方を、4段階で
ここからが実作業です。上から順に試して、直った時点で止めてください。
段階1：TRIM関数（スペース担当）
=TRIM(A2)前後の余計なスペースを削除し、文字の間に連続したスペースがあれば1つにまとめます。一致しない原因がスペースだけなら、これで終わります。
段階2：CLEAN関数（制御文字担当）
=CLEAN(A2)印刷できない制御文字、つまり改行・復帰・タブなどを削除します。Webやシステムから持ってきたデータでは、こちらが効くケースが多いです。
段階3：TRIM＋CLEANの合わせ技
=TRIM(CLEAN(A2))内側のCLEANで制御文字を落とし、外側のTRIMでスペースを整えます。順番を逆にすると、制御文字を消した結果できた余分なスペースが残ってしまうので、CLEANが内側と覚えてください。
コピー由来のトラブルの、体感で8割はここで片付きます。原因を特定していなくても構いません。迷ったらまずこれを打つ、で大丈夫です。
段階4：SUBSTITUTE関数で名指しする
段階3で直らなかったら、相手は160番か8203番です。この2つは、文字を指定して置き換えるSUBSTITUTE関数で対処します。
ノーブレークスペースを普通の半角スペースに変えてから整える場合は、こうです。
=TRIM(CLEAN(SUBSTITUTE(A2, UNICHAR(160), " ")))ゼロ幅スペースと全角スペースもまとめて落としたいなら、こうなります。
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, UNICHAR(160), " "), UNICHAR(8203), ""), " ", "")))長く見えますが、やっていることは「この文字を、これに置き換える」を3回繰り返しているだけです。内側から順に、160番を半角スペースに、8203番を削除、全角スペースを削除、最後にCLEANとTRIMで仕上げ、という流れになっています。
この式は、外部データを扱う部署なら、テンプレートとしてメモ帳にでも残しておく価値があります。研究員は、社内で共有している手順書の1ページ目に貼っています。
CLEANを使うときの注意点
ひとつだけ、気をつけてほしいことがあります。
CLEAN関数は改行を削除します。つまり、セルの中で意図的に改行して住所や備考を2行で書いている場合、その改行も消えます。「A列の会社名だけCLEANしたい」という場面で、住所の列まで一括処理してしまうと、レイアウトが崩れます。
照合に使う列だけを対象にする。これを守れば事故りません。セル内改行そのものの扱い方については、別記事で詳しく整理しています。
残したい改行と消したい改行を区別する方法は、セル内の改行が原因で関数が動かないケースにまとめてあります。
作業列から値貼り付けまで、実務の5ステップ
数式で直せても、その列はまだ「計算結果」です。元の列を消した瞬間に、結果も消えます。最後まで手順を通しておきましょう。
- 作業列を追加する。A列の隣に列を挿入し、B2に
=TRIM(CLEAN(A2))を入力します。 - 下までコピーする。B2の右下にカーソルを合わせ、十字になったらダブルクリックすれば、データの最終行まで一気に入ります。
- 検算する。
=COUNTIF(マスタ範囲, B2)を別の列に入れて、0がなくなったか確認します。ここを飛ばすと、直ったつもりのまま次の工程へ進んでしまいます。 - 値として貼り付ける。B列全体をコピーし、同じ場所で右クリック →「形式を選択して貼り付け」→「値」。これで数式が消え、ただの文字データになります。
- 元の列を削除する。A列を消し、B列を本番の列として使います。
慣れれば1分もかかりません。研究員は、外部データを受け取ったら照合する前にこの5ステップを通す、という順番に変えました。エラーが出てから直すより、先に整えてしまうほうが結果的に早いからです。
やりがちな失敗、3つ
失敗1：元の列を先に消してしまう 値として貼り付ける前にA列を削除すると、B列の数式が参照先を失って、すべて #REF! になります。順番は「値貼り付け → 元の列を削除」です。
失敗2：片方のデータしか直さない 見えない文字は、照合する側とされる側、どちらにも入り得ます。コピーしてきたA表だけを直しても一致しないなら、マスタ側にも同じ処理をかけてみてください。研究員は、これで1時間溶かしたことがあります。
失敗3：置換機能でスペースを全削除してしまう Ctrl+Hの置換は強力ですが、「株式会社 さくら商会」のように意味のあるスペースまで消えます。置換を使うときは、範囲を選択してから実行する癖をつけてください。
よくある質問
Q. TRIMとCLEANを両方かけても直りません。 160番（ノーブレークスペース）か8203番（ゼロ幅スペース）の可能性が高いです。段階4のSUBSTITUTEを試してください。それでも直らない場合は、半角カナと全角カナのような文字コードレベルの違いが原因かもしれません。
その場合は文字コードの違いで一致しないケースの見分け方が役に立ちます。
Q. 数字なのに計算に使えません。見えない文字のせいですか。 可能性はあります。ただ、数値が文字列として保存されている、全角で入力されているなど、別の原因のことも多いです。
数字まわりのトラブルは数値の形式が統一されていないケースで切り分けられます。
Q. 毎回同じシステムのデータで起きます。根本的に防げませんか。 出力側の仕様なので、こちら側では防げないことがほとんどです。割り切って、取り込み手順の中にクリーニングを組み込んでしまうのが現実的です。
Q. 貼り付けるときに見えない文字を持ち込まない方法はありますか。 「形式を選択して貼り付け」で「テキスト」を選ぶと、書式は落ちます。ただし改行やノーブレークスペースは文字そのものなので、これでも残ります。メモ帳に一度貼って、そこからコピーし直すと落ちる場合もありますが、確実ではありません。結局、貼ったあとにLENで確認するのがいちばん速いです。
Q. 末尾に空白があるだけでも同じことが起きますか。 起きます。見えない文字の中でも、末尾の空白はとくに気づきにくい部類です。
詳しくは末尾の空白が原因でVLOOKUPが一致しないケースをご覧ください。
研究員メモ
このシリーズを書きながら、あらためて感じていることがあります。
Excelのトラブルは、たいてい「見えないもの」が原因です。見えない空白、見えない制御文字、見えない書式。目で見て分かるミスなら、そもそも本人が気づきます。厄介なのは、画面上は完璧に見えているのに、内部では違うものとして扱われている状態です。
だからこそ、目で確認せず、数字で確認するという習慣が効きます。LEN関数で文字数を数える、COUNTIFで件数を数える、UNICODEで番号を出す。人間の目より、関数の返す数字のほうが正確です。
そしてもうひとつ。コピペは便利ですが、便利な操作ほど事故の起点になりやすいです。手入力なら起こらなかった問題が、コピペでは起こる。時短のつもりが、原因調査で1時間溶ける。研究員も何度も経験しました。
外部から持ってきたデータは、まずクリーニングしてから使う。この一手間を最初に入れるだけで、後工程の事故はかなり減ります。
表の作り方そのものに問題があると、いくらデータをきれいにしても集計が合わなくなることもあります。
データそのものではなく表の設計に原因があるパターンは、列の意味が途中で変わっている表のトラブルで解説しています。
もう一歩踏み込みたい方へ
ここまでの内容で、単独の列・単独の原因であれば解決できるはずです。
一方で、実務では「改行とノーブレークスペースと全角スペースが同じ表に同居している」「列によって混入している文字が違う」といった、複数の原因が絡み合ったデータが届きます。そうなると、どの順番で処理するかによって結果が変わってきます。
同じことは、毎月届くCSVでも起きます。開く前の取り込み方と、一度作れば翌月以降も使い回せる掃除列の数式テンプレートは、noteにまとめました。この記事1本では扱いきれなかった、もう一歩踏み込んだ内容です。
まとめ
最後に、今日から使えるチェックリストの形で整理します。
- コピペしてきたデータで一致しないなら、まず
=LEN(A2)と=LEN(TRIM(CLEAN(A2)))を比べる - 数字が違えば、見えない文字の混入が確定
- 8割は
=TRIM(CLEAN(A2))で直る - 直らなければ
=UNICODE(RIGHT(A2,1))で番号を確認する - 160番・8203番なら、SUBSTITUTEで名指しして消す
- 直したあとは必ず「値として貼り付け」で確定させる
- 照合するなら、相手側の表も同じ処理をかける
一致しないとき、真っ先に疑うべきは関数ではなくデータです。関数は、与えられたものを正確に比較しているだけで、嘘はついていません。
見えない1文字を見つけられるようになると、Excelは急に素直な道具に見えてきます。次に#N/Aが出たときは、まずLENを打ってみてください。
コメント