CSVや外部システムから取り込んだデータに、目に見えない文字が混ざっていたことはありませんか？ 文字化けのような記号が表示されたり、VLOOKUPが一致しなかったり。手作業で探すのは大変ですし、そもそも見えない文字は目視では見つかりません。
CLEAN関数を使えば、こうした印刷できない制御文字をまとめて削除できます。この記事では基本の書き方から実務の活用パターンまで紹介します。
この記事は次のような人におすすめ
- CSVや他システムから取り込んだデータの文字化けを解消したい
- 印刷できない制御文字を一括で削除したい
- CLEAN関数とTRIM関数の違いを知りたい
CLEAN関数とは？
CLEAN（クリーン）関数は、文字列からASCII制御文字を削除する関数です。英語の「clean」は「きれいにする」という意味で、データを浄化するための関数です。
ここでいう「制御文字」とは、文字コード0〜31の印刷できない文字のことです。改行コード（CHAR(10)）やタブ（CHAR(9)）、キャリッジリターン（CHAR(13)）などが該当します。
これらの文字は画面上では見えないか、四角い記号として表示されます。外部データを取り込んだときに紛れ込むことが多いです。VLOOKUPの不一致やセル内改行の崩れなど、厄介なトラブルの原因になります。
NOTE
CLEAN関数が削除するのはASCII制御文字（コード0〜31）のみです。通常のスペースや全角スペースは削除しません。スペースの整理にはTRIM関数を使います。
CLEAN関数の書き方（構文と引数）
基本構文
=CLEAN(文字列)引数の説明
| 引数 | 必須/省略可 | 説明 |
|---|---|---|
| 文字列 | 必須 | 制御文字を削除したいテキスト、またはセル参照 |
引数は「文字列」の1つだけです。セル参照のほか、ダブルクォーテーションで囲んだ文字列を直接指定することもできます。
CLEAN関数の基本的な使い方
制御文字を削除する基本例
セルA1に改行コード付きの文字列が入っている場合です。
=CLEAN(A1)CLEAN関数を適用すると、改行やタブなどの制御文字がすべて削除されます。見た目がおかしいデータも、この一手で綺麗になります。
セル参照で一括処理する
データが大量にある場合は、作業列を使って一括処理するのが効率的です。
=CLEAN(A2)この数式を下方向にコピーして、整形後のデータを「値として貼り付け」で元の列に上書きすれば完了です。
文字列を直接指定する
セル参照ではなく、数式内に文字列を直接書くこともできます。
=CLEAN("テスト" & CHAR(10) & "データ")CHAR(10)は改行コードです。CLEAN関数が改行を除去して、「テストデータ」と表示されます。
CLEAN関数の実務活用パターン
CLEAN+TRIMで外部データを整形する
外部データには制御文字とスペースの両方が混ざっていることがよくあります。CLEAN関数とTRIM関数を組み合わせれば、両方を一度に処理できます。
=TRIM(CLEAN(A2))まずCLEAN関数で制御文字を除去し、次にTRIM関数で余分なスペースを整理します。CSVや他システムからのデータ取り込み後は、このセットで処理しておくと安心です。
CLEAN+SUBSTITUTE+TRIMの3点セットで完全クレンジング
Webページからコピーしたデータには、ノーブレークスペース（CHAR(160)）が含まれることがあります。この文字はCLEAN関数でもTRIM関数でも削除できません。
SUBSTITUTE関数を加えた3点セットなら、ほぼすべての不要文字を除去できます。
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))処理の流れは次のとおりです。
- SUBSTITUTE関数でノーブレークスペースを半角スペースに変換
- CLEAN関数で制御文字を除去
- TRIM関数で余分なスペースを整理
この3点セットは外部データのクレンジングで最も頼りになる定番パターンです。迷ったらまずこの数式を試してみてください。
CLEANで除去できない文字をCHAR+SUBSTITUTEで対処する
CLEAN関数はASCII制御文字（コード0〜31）だけを削除します。コード128以上のUnicode制御文字やNBSP（CHAR(160)）は対象外です。
こうした文字はCODE関数で文字コードを特定し、CHAR関数とSUBSTITUTE関数で個別に削除します。
=CODE(MID(A1,1,1))まずCODE関数でセル内の先頭文字の文字コードを調べます。正体がわかったら、SUBSTITUTE関数で削除しましょう。
=SUBSTITUTE(A1,CHAR(127),"")たとえば文字コード127（DEL文字）が混入していた場合、上の数式で削除できます。CLEAN関数の対象外の文字に遭遇したら、この手順で対処してください。
NOTE
ノーブレークスペースの詳細と対策はノーブレークスペースの解説記事を参照してください。
CLEAN関数のよくあるエラーと対処法
CLEAN関数自体はエラーが出にくい関数です。ただし意図した結果にならないケースがあります。
| 症状 | 原因 | 対処法 |
|---|---|---|
| 文字化けが消えない | Unicode制御文字（コード128以上） | CODE関数で文字コードを特定し、SUBSTITUTE+CHARで削除 |
| スペースが残る | CLEAN関数はスペースを削除しない | TRIM関数で余分なスペースを除去 |
| 見えない文字が残る | ノーブレークスペース（CHAR(160)） | =SUBSTITUTE(A1,CHAR(160),"") で削除 |
| #VALUE!エラー | 引数が指定されていない | 文字列の引数は必須。セル参照か文字列を指定してください |
| VLOOKUPが一致しない | CLEAN後もまだ不一致 | ASC関数で全角英数字を半角に統一してみてください |
TIP
CLEAN関数で文字化けが消えないときは、
=CODE(MID(A1,1,1))で1文字ずつ文字コードを確認してみましょう。0〜31以外の値が返ってきたら、CLEAN関数の対象外の文字です。
似た関数との違い・使い分け
CLEAN関数とよく比較される関数の違いを整理します。
| 項目 | CLEAN関数 | TRIM関数 | SUBSTITUTE関数 |
|---|---|---|---|
| 削除対象 | 制御文字（コード0〜31） | 余分な半角スペース | 指定した文字列 |
| 改行の削除 | できる | できない | CHAR(10)を指定すればできる |
| スペースの処理 | できない | 前後削除＋内部を1つに圧縮 | 指定すれば削除可能 |
| NBSP（CHAR(160)） | できない | できない | CHAR(160)を指定すれば削除可能 |
| 引数の数 | 1つ（文字列のみ） | 1つ（文字列のみ） | 3〜4つ（文字列、検索文字列、置換文字列、[置換対象]） |
| 使う場面 | 外部データの制御文字除去 | スペースの整理 | 特定の文字を狙って置換・削除 |
使い分けのポイントは「何を消したいか」です。
- 制御文字を一括で消したい → CLEAN関数
- 余分なスペースを整理したい → TRIM関数
- 特定の文字を狙って消したい → SUBSTITUTE関数
- 全部まとめて整形したい →
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))の3点セット
まとめ
CLEAN関数は「文字列からASCII制御文字を一括削除する」ための関数です。
- ASCII制御文字（コード0〜31）の改行・タブ・CRなどを削除する
- 引数は「文字列」の1つだけでシンプル
=TRIM(CLEAN(A2))でスペース整理と制御文字除去を同時に処理できる- ノーブレークスペース（CHAR(160)）はCLEAN対象外。SUBSTITUTE関数で対処する
- 3点セット
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))が最強のクレンジング数式
外部データの取り込みでおかしな文字が混ざっていたら、まずCLEAN関数を試してみてください。TRIM関数やSUBSTITUTE関数と組み合わせれば、ほとんどのクレンジングに対応できます。
関連記事
関数一覧
Excel関数の一覧は下記の記事で確認できます。
エラー値が表示される場合は、下記の記事も参考にしてみてください。
