Excel関数のTRIMとCLEANで貼り付けたデータの掃除をする方法

外部のシステムからデータをエクスポートしたり、WebサイトからコピーしてExcelに貼り付けたりしたとき、「見た目は合っているはずなのに、VLOOKUP関数でエラーが出る」「検索してもヒットしない」といった不思議な現象に悩まされたことはありませんか？

実はその原因の多くは、セルの中に紛れ込んだ「不要なスペース」や、目に見えない「制御文字」というゴミのようなデータにあることが多いです。

こうしたデータの汚れを取り除き、扱いやすい状態にする作業を「データクレンジング」と呼びます。Excelには、この掃除を自動でやってくれる強力な味方として「TRIM（トリム）関数」と「CLEAN（クリーン）関数」が用意されています。

この記事では、初心者の方に向けて、TRIM関数とCLEAN関数の役割と使い分け、そして2つを組み合わせて一気にデータをきれいにする方法をわかりやすく解説します。

TRIM関数で「不要なスペース」を掃除する

まずは、TRIM関数から見ていきましょう。TRIM関数を一言でいうと、「余計な空白を消してくれる関数」です。

TRIM関数が何をしてくれるのか

私たちがWebサイトや他のソフトからデータをコピーして貼り付けると、文字の前後に意図しないスペースが入ってしまうことがよくあります。

例えば、本来は「株式会社サンプル」としたいのに、データ上は「 株式会社サンプル 」のように、先頭や末尾に空白が入っているケースです。人間が見れば同じものだとわかりますが、Excelにとって「株式会社サンプル」と「 株式会社サンプル 」は完全に別の文字列として扱われます。

TRIM関数を使うと、以下のようなルールで空白を削除してくれます。

• 文字列の先頭にあるスペースをすべて削除する
• 文字列の末尾にあるスペースをすべて削除する
• 文字と文字の間にある連続したスペースは、1つだけ残してあとは削除する

つまり、「単語間の最低限のスペースは維持しつつ、外側の余計な空白だけをカットする」という賢い動きをしてくれるのがTRIM関数の特徴です。

TRIM関数の基本的な使い方

使い方は非常にシンプルです。

数式：`=TRIM(文字列)`

「文字列」の部分には、直接文字を入力するか、掃除したいセルを指定します。

例えば、セルA2に「 鹿児島黒豚 」という、前後にスペースが入った文字が入っている場合、別のセルに次のように入力します。

`=TRIM(A2)`

これで、結果は「鹿児島黒豚」となります。

なぜTRIM関数を使うべきなのか

「手動で消せばいいのでは？」と思うかもしれませんが、数百件、数千件という大量のデータを扱う場合、手作業は現実的ではありません。また、TRIM関数でデータを整えることで、以下のような実務上の大きなメリットが得られます。

1. 関数の動作が正確になる

VLOOKUP関数やCOUNTIF関数、MATCH関数などは、1文字でも違うと「一致しない」と判断します。TRIM関数で形式を統一すれば、検索漏れや誤ったエラーを防げます。

1. データの形式が統一される

名簿や商品リストなど、データの見た目が揃うため、一貫性が高まります。

1. テキスト連結時のミスを防げる

複数のセルを結合して一つの文章を作る際、元のデータに余計なスペースがあると、結合後の文章に変な隙間ができてしまいます。これを事前に防ぐことができます。

CLEAN関数で「見えない文字」を掃除する

次に、CLEAN関数について解説します。TRIM関数が「見える空白」を相手にするのに対し、CLEAN関数は「目に見えない文字」を相手にします。

CLEAN関数が何をしてくれるのか

外部システムやCSVファイルからデータを取り込んだ際、セルの中に「制御文字」と呼ばれる特殊なコードが紛れ込むことがあります。これは改行コードや印刷不能な文字などのことで、セルを見ただけでは空白に見えたり、あるいは何も入っていないように見えたりします。

しかし、この「見えないゴミ」が潜んでいると、以下のようなトラブルが起こります。

• 見た目は同じなのに、検索してもヒットしない
• コピーして別のソフトに貼り付けると、文字が崩れる
• フィルタをかけたときに、同じ項目なのに別々のグループに分かれてしまう

CLEAN関数は、こうした「印刷不能な制御文字」を検知して、まとめて削除してくれる関数です。

CLEAN関数の基本的な使い方

TRIM関数と同様に、使い方はとても簡単です。

数式：`=CLEAN(文字列)`

例えば、セルA2に外部システムから取り込んだ「見えない文字が含まれる顧客名」が入っている場合、次のように入力します。

`=CLEAN(A2)`

これで、目に見えない不要な制御文字が取り除かれ、データとして扱いやすいクリーンな状態になります。

CLEAN関数の使いどころ

CLEAN関数は、特に以下のようなシーンで役立ちます。

• 企業の基幹システムから出力したデータをExcelに貼り付けたとき
• 形式が不安定なCSVデータを読み込んだとき
• Web上のフォームから送信された問い合わせ内容をリスト化したとき

「文章そのものを美しく整える」というよりは、「システム的なゴミを取り除いて、Excelが正しく認識できる状態にする」というイメージで使いましょう。

実践！TRIMとCLEANを組み合わせて一気に掃除する

実際の業務では、「不要なスペース」と「見えない文字」の両方が混在していることがほとんどです。その場合、一つずつ関数を適用させるのは手間ですよね。

そこで活用したいのが、関数の中に別の関数を入れる「ネスト（入れ子）」というテクニックです。

組み合わせ技：`=TRIM(CLEAN(セル))`

TRIM関数とCLEAN関数を組み合わせて、一度に掃除する方法がこちらです。

数式：`=TRIM(CLEAN(A2))`

この数式は、内側から順番に処理が行われます。

1. まず `CLEAN(A2)` が実行され、見えない制御文字が削除されます。
2. 次に、その結果に対して `TRIM(…)` が実行され、前後の不要なスペースが削除されます。

この組み合わせを使うことで、外部から貼り付けた汚れたデータを、一撃で「純粋な文字列だけ」に整えることができます。まさに最強のデータ掃除セットと言えるでしょう。

大量データを効率的に掃除するワークフロー

関数を使ってデータを掃除した後は、そのままにしておくのではなく、以下の手順で仕上げを行うのがおすすめです。

1. 作業列を作る

元のデータ（A列）の隣に、掃除用の列（B列）を作ります。

1. 数式を入力してオートフィル

B2セルに `=TRIM(CLEAN(A2))` と入力し、そのまま下のセルまでオートフィル（セルの右下をダブルクリックしてコピー）します。これで全データの掃除が完了します。

1. 「値として貼り付け」で確定させる

関数が入ったままだと、元のA列を消したときにエラーになってしまいます。

• 掃除後のB列をコピーする
• 同じ場所（または元のA列）で右クリックし、「形式を選択して貼り付け」から「値」を選択して貼り付ける。

1. 作業列を削除する

値に変換した後は、関数が入っていた作業列は不要になるため、削除して完了です。

さらに踏み込んだ掃除：TRIMで消えない「特殊な空白」への対処

ここまでTRIM関数とCLEAN関数について解説してきましたが、実はこれらを使っても消えない「しぶとい空白」が存在します。

それが、Web上のデータによく含まれる「非改行スペース（` `）」と呼ばれる特殊な空白です。この文字は、見た目はただの半角スペースですが、ExcelのTRIM関数では「空白」として認識されず、削除してくれません。

もし `=TRIM(CLEAN(A2))` を使っても空白が消えない場合は、`SUBSTITUTE関数`を組み合わせて、その特殊な空白を普通の空白に置き換えてからTRIMにかけるという技を使います。

応用数式：`=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))`

※`CHAR(160)` が非改行スペースを指します。これを一度普通の半角スペース（`" "`）に変換し、その後にTRIMで消し飛ばすという流れです。

ここまで来れば、ほとんどの「貼り付けデータのゴミ」は完全に除去でき、VLOOKUP関数などの集計関数もストレスなく動作するはずです。

まとめ

Excelで外部データを扱う際、TRIM関数とCLEAN関数は「データクレンジング」の基本となる非常に重要なツールです。

• TRIM関数：前後や文字間の「余計なスペース」を消す
• CLEAN関数：目に見えない「制御文字（ゴミ）」を消す
• 組み合わせ技：`=TRIM(CLEAN(セル))` で一気に掃除する

「データがうまく一致しない」「検索に引っかからない」と感じたら、まずはこの2つの関数を思い出してください。データをきれいに整えることは、分析や集計のミスを減らし、仕事のスピードを格段に上げるための近道になります。

ぜひ明日からの業務で、まずは簡単な名簿やリストの掃除から試してみてくださいね。