Excel約5分で読めます

Excelで前後のスペースを消す|TRIMで消えない空白

Excelで前後のスペースを消すを表したイメージ
記事の内容を表したイメージ(AI生成)

TRIMを使っても前後の空白が消えない、という状況は「空白に見えるが実は別の文字が入っている」ことが主な原因です。ExcelのTRIMが取り除けるのは半角スペース(文字コード32番)だけで、全角スペースやノーブレークスペース(改行させないための特殊な空白文字)には効きません。

この記事では、TRIMで消えない空白の種類を見分ける方法と、それぞれに対応する数式を順に示します。コピー貼り付けでデータを取り込んだとき、Webシステムや基幹システムからエクスポートしたとき、外部委託先から受け取った名簿や申込一覧に特に起きやすい現象です。

作業の前に、必ず元のファイルをコピーして別名で保存しておいてください。数式で置換した後に「元のほうが正しかった」と気づいても、上書きしていると戻せなくなります。


TRIMが消せる空白と消せない空白

TRIM(A1)は、文字列の先頭と末尾にある半角スペースをすべて除去し、文字列の途中に連続した半角スペースがある場合は1つに詰めます。これ以外の空白文字には対応していません。

消えない空白として現場で多いのは次の3種類です。

  • 全角スペース(文字コード:Unicodeで U+3000):日本語入力のまま誤って打ち込まれることがあります。見た目が広く、半角スペースと区別しにくいです。
  • ノーブレークスペース(NBSP、文字コード160番):WebページやPDFからコピーした文字列に混入しやすいです。TRIMはこれを「スペースではない文字」として扱います。
  • タブ文字(文字コード9番):CSVや基幹システムのエクスポートに含まれることがあります。

空白の種類を見分ける手順

どの種類の空白が入っているかを確認してから対処すると、余計な変換ミスを防げます。

CODE関数で先頭の文字コードを調べる

セルA1に「 名前」(先頭に何か空白がある状態)が入っているとします(架空の例です)。

=CODE(LEFT(A1,1))
  • 結果が 32 → 半角スペース。TRIMで消えるはずなので、別の問題を確認します。
  • 結果が 160 → ノーブレークスペース。後述のSUBSTITUTE対応が必要です。
  • 結果が 12288 → 全角スペース(Unicodeコードポイント)。ただしCODE関数の返す値は環境により異なります(後述)。
  • 結果が 9 → タブ文字。

CODE関数は先頭の1文字しか調べないため、先頭が半角スペースで、その後ろにノーブレークスペースが混じっているケースは見落とします。念のため末尾もCODE(RIGHT(A1,1))で確認すると安心です。

LEN関数でTRIM前後の文字数を比べる

=LEN(A1)-LEN(TRIM(A1))

この差が0でなければ、TRIMが何らかの文字を除去しています。差が0のまま「空白に見える」なら、TRIMが効かない種類の空白が残っています。


種類ごとの対処数式

画面の表示はExcelの版や設定により異なります。以下の関数名は一般的な呼び名で記載しています。

ノーブレークスペース(コード160)を除去する

SUBSTITUTE関数の第2引数にCHAR(160)を使い、空文字列に置き換えます。

=TRIM(SUBSTITUTE(A1,CHAR(160),""))

SUBSTITUTEでコード160の文字を除去した後、TRIMで残った半角スペースを処理します。順番は内側から外側へ評価されるので、この書き方で両方に対応できます。

全角スペースを除去する

全角スペースはCHAR関数では直接指定しにくいため、全角スペースそのものを文字列として書きます。

=TRIM(SUBSTITUTE(A1," ",""))

第2引数の" "の中は全角スペース1文字です。見た目で区別しにくいため、数式を書くときは全角スペースが確実に入っているセルからコピーして貼り付けると安全です。全角スペースを空文字列に置き換えた後、TRIMで半角スペースを処理しています。

タブ文字を除去する

=TRIM(SUBSTITUTE(A1,CHAR(9),""))

複数の種類が混在している場合

ノーブレークスペース・全角スペース・タブ・半角スペースがすべて混じっているケースでは、SUBSTITUTEを入れ子にします。

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(9),"")," ",""))

内側から順に処理されます。一番内側でコード160を除去→次にタブを除去→次に全角スペースを除去→最後にTRIMで半角スペースを整理する、という流れです。

入れ子が深くなるほど読みにくくなるため、Googleスプレッドシートを使っている場合はREGEXREPLACEを使う方法もありますが、Excelの場合はこの入れ子方式が基本的な対応です。


数式の結果を「値として貼り付ける」手順

数式でクリーニングした結果を元の列に反映させるには、数式のままでは元データを参照し続けるため、値に変換する必要があります。

  1. 数式を入力した列を選択してコピーします。
  2. 貼り付けたい場所で「形式を選択して貼り付け」→「値」を選びます。
  3. 数式列と元データ列の両方が正しくなったことを目視で確認してから、不要なほうを削除します。

元データをそのまま上書きしないよう、必ず作業前のファイルコピーを手元に残してください。

なお、個人情報を含む名簿や申込一覧を外部のクリーニングツールや生成AIサービスに貼り付けて処理する場合は、組織の情報管理ルールを事前に確認してください。


迷いやすい例外:TRIMで消えるはずなのに消えない場合

半角スペース(コード32)であることは確認できているのにTRIMが効かないケースが、まれに報告されます。確認ポイントは2つです。

セルの書式が「文字列」になっている:セルが文字列書式に設定されていると、数式の結果も文字列として扱われ、期待どおりに動かないことがあります。セルの書式を「標準」に変更してから数式を再入力してみてください。

数式ではなく「値」として表示されている:数式を入力したつもりが、セルに=TRIM(A1)という文字そのものが表示されている場合は、セルが文字列書式になっているサインです。同様に書式を「標準」に直して対応します。


次の行動

まずはCODE(LEFT(A1,1))で先頭の文字コードを調べ、何が入っているかを特定してください。コード160ならSUBSTITUTE(A1,CHAR(160),"")、全角スペースならSUBSTITUTE(A1," ","")をTRIMと組み合わせます。作業後は元データと処理後のデータをLEN関数で比較し、文字数が意図どおりに変わっているかを確認してから値貼り付けに進んでください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ