名簿の突き合わせを頼まれて、VLOOKUPを組んで、あとは下までコピーするだけ——のはずでした。ところが結果を見ると、200件中の6件だけが#N/A。
その6件をじっと見比べても、参照先の名前とまったく同じに見えるんです。コピーして貼り付け直しても直らない。範囲の絶対参照も合っている。FALSEも付いている。「じゃあなんで？」と、そこから1時間以上、私は関数のほうばかり疑っていました。
答えが出たのは、なんとなくLENで文字数を数えてみたときでした。同じに見える名前なのに、片方だけ1文字多い。うしろに、半角スペースが1つだけ入っていたんです。
先頭の空白なら、まだ気づけたかもしれません。文字が少し右にずれるので、違和感が出ますから。でも末尾の空白は、見た目にはほぼ何も起きません。だから厄介なんです。
ちなみに、空白が文字の「前」に入っているパターンについては別記事で詳しくまとめています。症状は似ていますが、見つけ方のコツが少し違うので、そちらが怪しい方は先頭に空白が入っているケースも確認してみてください。
この記事では、「末尾に空白があるかどうかを1分で確定させる方法」から入って、空白の正体を特定し、種類に合わせて確実に消すところまでを順番に研究していきます。
先に結論だけ
急いでいる方のために、答えを3行でまとめます。
- 疑いを確定させる式：
=LEN(A2)と=CODE(RIGHT(A2,1)) - 消す式：
=TRIM(A2)（消えないときは=TRIM(SUBSTITUTE(A2,CHAR(160)," "))） - 仕上げ：TRIMした列をコピーし、値として貼り付けて元の列と入れ替える
ここから先は、「なぜその式なのか」「TRIMで消えなかったときに何を疑うのか」を、順を追って見ていきます。急ぎでなければ、ぜひ最後まで付き合ってください。同じトラブルに二度ハマらないための部分は、後半にまとめてあります。
ステップ1｜「本当に末尾の空白か」を確定させる
トラブル対応でいちばんもったいないのは、原因を決めつけて対処してしまうことです。末尾の空白だと思い込んでTRIMをかけたのに直らず、また振り出しに戻る——これを避けるために、まず証拠を取りにいきます。
① LEN関数で文字数を数える
空いている列に、次の式を入れます。
=LEN(A2)LENは、そのセルに何文字入っているかを返す関数です。「りんご」なら3、「山田太郎」なら4。ここで想定より多い数字が出たら、目に見えない何かが紛れ込んでいます。
比較対象がある場合は、2つ並べてしまうのがいちばん早いです。
=LEN(A2)&" / "&LEN(D2)照合元と照合先の文字数を横に並べると、「3 / 4」のようにズレている行だけが浮き上がってきます。私はいつもこれをやってから原因を絞り込んでいます。
② 空白が「前」か「後ろ」かを切り分ける
文字数が多いことは分かった。では、その余分な1文字は前にあるのか、後ろにあるのか。ここを切り分けないと、対処法を間違えます。
=IF(RIGHT(A2,1)=" ","末尾に半角スペース","")RIGHT(A2,1)は、そのセルのいちばん右の1文字を取り出す関数です。それが半角スペースと一致するかをIFで判定しています。
全角スペースの可能性もあるので、実務ではこちらを使ったほうが確実です。
=IF(OR(RIGHT(A2,1)=" ",RIGHT(A2,1)=" "),"末尾に空白あり","OK")式の中の2つ目の" "は全角スペースです。パッと見では区別がつきませんが、Excelにとってはまったく別の文字です。
③ CODE関数で「空白の正体」まで突き止める
ここまで来たら、もう一歩踏み込みます。末尾の1文字が何者なのかを、文字コードで特定してしまう方法です。
=CODE(RIGHT(A2,1))CODEは、文字を数値（文字コード）に変換する関数です。返ってきた数字で、犯人の種類が分かります。
| 返ってきた数字 | 正体 | TRIMで消えるか |
|---|---|---|
| 32 | 半角スペース | 消える |
| 129（環境により異なる） | 全角スペース | 消えない |
| 160 | Web由来の特殊な空白（NBSP） | 消えない |
| 10 | 改行（セル内改行） | 消えない |
| 9 | タブ文字 | 消えない |
※全角スペースのコードは、CODEではなくUNICODE(RIGHT(A2,1))を使うと「12288」と明確に判別できます。Excel 2013以降であればUNICODEが使えますので、迷ったらこちらのほうが確実です。
なぜここまでやるのかというと、TRIMで消えるかどうかが最初から分かるからです。32ならTRIM一発で終わり。160や10なら、TRIMをいくらかけても消えません。ここを知らないまま「TRIMしても直らない」と悩んだ時間が、私にはかなりあります。
④ 目で見て確認したいときの小ワザ
式が苦手な方向けに、視覚的に確認する方法もあります。
="["&A2&"]"セルの中身をカギ括弧で挟むだけです。[りんご]なら問題なし、[りんご ]と右側に隙間が空いていたら、末尾に空白が入っています。地味ですが、報告資料に貼るときなど「証拠として見せたい」場面で重宝します。
ステップ2｜どこから空白は入ってくるのか
原因が分かったところで、そもそもなぜ末尾に空白が付くのかを整理しておきます。入り口を知っておくと、次から防げるようになります。
ケースA：システムやCSVからの出力
業務でいちばん多いのがこれです。基幹システムや会計ソフトから出力したCSVでは、桁数を揃えるために末尾がスペースで埋められていることがあります。固定長データという古くからの形式の名残で、「商品名は全20文字」と決めて、足りない分をスペースで補っているわけです。
人間が入力したわけではないので、入力者を問い詰めても解決しません。出力仕様の話なので、毎回同じように空白が付いてきます。裏を返せば、毎回同じ処理で片付くということでもあります。
ケースB：Webサイトや他アプリからのコピー
ブラウザで表示されている表をコピーしてExcelに貼ると、末尾に空白や、空白に見える別の文字が紛れ込むことがあります。先ほどの表に出てきたコード160の文字がその代表格で、これはHTMLで使われる特殊な空白です。
空白以外にも、改行コードや制御文字が一緒に付いてくることがあります。TRIMを試しても文字数が減らないときは、コピー時に見えない文字が混入するケースで解説しているCLEAN関数の出番かもしれません。
ケースC：手入力のクセ
変換を確定したあと、無意識にスペースキーを押してしまう。あるいは、次のセルへ移る前になんとなく一拍置く。この「なんとなく」が、末尾の空白を生みます。
厄介なのは、入力した本人がまったく自覚していない点です。「私はスペースなんて押していません」と言われて、実際に画面を見ても押した形跡は見えない。でもデータには入っている。犯人捜しをしても意味がないので、こういうときは仕組みで防ぎにいきます（後述します）。
ケースD：見た目を整えるためのスペース
「商品名の長さを揃えたいから」とスペースで調整するパターンです。人間の目には表が整って見えるのですが、Excelから見れば「文字＋空白」という別データになります。
見た目を揃えたいだけなら、スペースではなく、セルの書式設定にある「均等割り付け」や「インデント」を使ってください。データの中身を変えずに、表示だけを整えることができます。これが正解です。
ステップ3｜種類に合わせて消す
証拠は取れた。正体も分かった。ここからが実作業です。空白の種類に応じて、3つのルートがあります。
ルートA：半角スペースなら TRIM 一択
=TRIM(A2)TRIMは、文字列の前後にある余計な半角スペースを取り除く関数です。単語と単語の間にあるスペースは1つだけ残してくれるので、「山田 太郎」のような名前を壊す心配もありません。
実務での手順は次のとおりです。
- 空いている列（たとえばB列）に
=TRIM(A2)を入力する - データの最終行まで下にコピーする
- B列全体をコピーする
- A列を選択して、右クリック →「形式を選択して貼り付け」→「値」を選ぶ
- 作業用のB列を削除する
4番目の値として貼り付ける工程が、いちばん大事です。ここを飛ばしてB列のまま進めると、元のA列を削除した瞬間に、参照元を失ったB列が一斉に#REF!になります。私は一度これをやって、作業をまるごとやり直したことがあります。
ルートB：全角スペースなら置換で消す
TRIMでは全角スペースは消えません。こちらは置換機能を使います。
- 対象の列を選択する
Ctrl + Hで置換ダイアログを開く- 「検索する文字列」に全角スペースを1つ入力する
- 「置換後の文字列」は空欄のまま
- 「すべて置換」をクリック
注意点がひとつ。この方法は、文字の途中にある全角スペースも一緒に消してしまいます。「山田 太郎」のように氏名の間に全角スペースを入れている名簿だと、「山田太郎」に変わってしまうわけです。
氏名を扱う表では、置換は使わずに、次のルートCを使ってください。
ルートC：しぶとい空白は合わせ技で片付ける
Web由来の特殊な空白（コード160）や、複数種類が混在しているケースには、関数を組み合わせます。
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))内側のSUBSTITUTEが、コード160の特殊空白をいったん普通の半角スペースに置き換え、外側のTRIMがそれを前後からまとめて削除する、という流れです。
さらに改行や制御文字まで疑うなら、CLEANを足します。
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))そして、全角スペースが末尾にだけ入っていて、途中の全角スペースは残したい場合。これはひとつの式では難しいので、私は「末尾だけを狙い撃ちする」やり方をしています。
=IF(RIGHT(A2,1)=" ",LEFT(A2,LEN(A2)-1),A2)末尾の1文字が全角スペースなら、最後の1文字を除いた部分（LEFTで左から「全体の文字数−1」文字分を取り出す）を返し、そうでなければそのまま返す、という式です。これなら「山田 太郎 」が「山田 太郎」になり、名前の間の全角スペースは守られます。
末尾に空白が2つ以上並んでいる可能性がある場合は、この式を入れた結果をもう一度同じ式に通すか、いっそ=TRIM(SUBSTITUTE(A2," "," "))で全角を半角に読み替えてからTRIMし、必要に応じて間のスペースだけ手当てするほうが早いこともあります。データの性質に合わせて選んでください。
ステップ4｜それでも一致しないときの切り分け
空白を消したのに、まだVLOOKUPが#N/Aのまま。そんなときは、原因が別にあります。切り分けの表を置いておきます。
| 症状 | 疑うべき原因 | 確認方法 |
|---|---|---|
| TRIM後もLENの値が減らない | 半角スペース以外の文字 | =UNICODE(RIGHT(A2,1)) |
| 見た目は同じなのに一致しない | 半角カナと全角カナの混在 | =EXACT(A2,D2) |
| 数字のはずが左寄せで表示される | 数値が文字列になっている | =ISTEXT(A2) |
| 一部の行だけ一致しない | 改行やタブの混入 | =LEN(A2) で行ごとに比較 |
=EXACT(A2,D2)は、2つのセルが完全に同じかどうかをTRUE／FALSEで返す関数です。目視では判断できない違いを、機械的に切り分けてくれます。FALSEが返ったら、その2つは間違いなく別データです。
特に見落としやすいのが、半角カナと全角カナの混在です。「ｱｲｳ」と「アイウ」は人間の目には同じ言葉に見えますが、Excelにとっては別物です。心当たりがある方は、文字コードの違いで一致しなくなるケースもあわせて確認してみてください。
見落としやすい落とし穴｜数値データにTRIMをかけたとき
ここはぜひ覚えて帰ってください。
「1000 」のように、数字の末尾に空白が入っているデータにTRIMをかけると、空白は消えます。でも、その結果は**文字列としての「1000」**です。見た目は数字なのに、SUM関数で合計しても0のまま、という状態になります。
これを数値に戻すには、VALUEで包みます。
=VALUE(TRIM(A2))VALUEは、文字列として入っている数字を、計算できる本物の数値に変換する関数です。金額や数量を扱う列では、この一手間を忘れないでください。
そもそも合計が合わない、という症状で困っている方は、数字が文字列になっているケースに原因の切り分け方をまとめてあります。空白とセットで発生しやすい組み合わせです。
再発を防ぐ3つの習慣
直し方が分かったら、次は入れない工夫です。私が現場で続けている方法を3つ紹介します。
1つ目は、貼り付けの前にテキストエディタを経由すること。 Webサイトや他システムからコピーしたデータは、いったんメモ帳に貼り付け、そこからあらためてコピーしてExcelに貼ります。ひと手間ですが、書式と一緒に紛れ込む余計な文字をかなり落とせます。
2つ目は、受け入れ用の作業列をあらかじめ用意しておくこと。 毎月同じシステムからCSVを取り込むような業務なら、A列に生データ、B列に=TRIM(A2)という形をテンプレート化しておきます。取り込んだ瞬間に整形が終わるので、そもそもトラブルが起きません。
3つ目は、入力規則で釘を刺しておくこと。 複数人で入力するファイルなら、データの入力規則で「ユーザー設定」を選び、次の式を入れておく方法があります。
=A1=TRIM(A1)前後に空白があるとTRIMした結果と一致しなくなるため、入力を弾いてくれます。エラーメッセージに「前後にスペースを入れないでください」と書いておけば、口頭でルールを伝えるより確実です。
よくある質問
Q. TRIMを使うと、名前の間のスペースまで消えませんか？
消えません。TRIMが削除するのは、前後にある空白と、単語間の2つ目以降の余分な空白だけです。「山田 太郎」は「山田 太郎」のまま残ります。ただし「山田 太郎」（全角）の場合、TRIMは何もしないので、そのまま残る点にご注意ください。
Q. 空白かどうか調べたい列が20列あります。1列ずつ確認するしかないですか？
条件付き書式を使うと一気に可視化できます。範囲を選択して「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選び、=A1<>TRIM(A1)と入力して塗りつぶし色を指定してください。前後に空白があるセルだけに色が付きます。
Q. VLOOKUPの式の中で直接TRIMを使えますか？
使えます。=VLOOKUP(TRIM(A2),範囲,2,FALSE)のように書けば、検索値の空白を無視して照合できます。ただし、これは検索する側の空白を消しているだけで、参照先の表に空白が入っている場合は一致しません。その場合は参照先のデータ自体を整える必要があります。応急処置としては有効ですが、根本対処は元データを直すことです。
Q. 全角スペースと半角スペース、どちらが多いですか？
システム出力なら半角、手入力なら全角が多い印象です。日本語入力をオンにしたままスペースキーを押すと全角になるので、人が打った表には全角が混ざりやすくなります。
まとめ
末尾の空白は、Excelのトラブルの中でも特に「見えない」種類のものです。だからこそ、目で探すのではなく、式で確定させるのが近道になります。
- 疑ったら
=LEN(A2)で文字数を数える =CODE(RIGHT(A2,1))または=UNICODE(RIGHT(A2,1))で正体を特定する- 半角なら
TRIM、全角なら置換、特殊な空白ならSUBSTITUTEとの合わせ技 - 仕上げは必ず「値として貼り付け」
- 数値列なら
VALUEで計算できる形に戻す
「同じはずなのに一致しない」と感じたときに、関数ではなくデータの中身を疑えるようになると、Excelの作業時間は目に見えて短くなります。私が1時間かけて迷子になった問題も、いまなら30秒で片付きます。差がついたのは知識量ではなく、最初に何を疑うかという順番だけでした。
なお、「見た目は同じなのに中身が違う」トラブルには、空白のほかにもいくつか定番があります。合計だけが合わない症状でお困りなら全角数字が混ざっているケース、前方に空白が入っていそうなら先頭の空白が原因のケースを、それぞれ参考にしてみてください。どれも原因の根っこは同じで、「人間には同じに見えるが、Excelには違って見える」という一点に集約されます。
今回は1列のデータを整えるところまでを扱いましたが、実務では「複数列をまとめて整形したい」「毎月届くCSVを開いた瞬間に整えたい」「TRIMでは追いつかない混在データを一括で処理したい」といった場面が出てきます。そうした、もう一歩複雑なケースへの応用パターンと、コピーしてすぐ使える整形用の数式テンプレートは、noteにまとめています。この記事の内容で足りているうちは不要ですが、同じ処理を毎月繰り返している方には、時間の使い方が変わるはずです。
コメント