CSVをインポートしたら、セルの中に謎の改行が入っていた。Webページからコピペしたデータに、見えない文字が紛れ込んでいた――そんな経験はありませんか？
目視ではわからない制御文字が混入すると、数式がうまく動かなかったり、集計結果がずれたりします。原因を特定するだけでも一苦労ですよね。
そんなときに使うのがCLEAN関数です。改行やタブなどの「印刷できない制御文字」をまとめて削除してくれます。
この記事では、CLEAN関数の基本から、TRIM関数・SUBSTITUTE関数との組み合わせ、CLEAN関数では消えない文字への対処法まで紹介します。
スプレッドシートのCLEAN関数とは？印刷できない制御文字を削除する関数
CLEAN関数（読み方：クリーン関数）は、テキストからASCII制御文字を削除する関数です。
名前は英語の「clean（きれいにする）」が語源です。目に見えない不要な文字をきれいに取り除くイメージですね。
CLEAN関数が削除するのは、ASCIIコード0〜31の「制御文字」と呼ばれる文字です。代表的なものを挙げると、次のとおりです。
- CHAR(9)：タブ
- CHAR(10)：改行（ラインフィード / LF）
- CHAR(13)：復帰（キャリッジリターン / CR）
これらは画面に表示されない文字なので、目視では見つけられません。CLEAN関数を使えば、まとめて削除できます。
CLEAN関数の構文と引数
=CLEAN(テキスト)カッコの中に「制御文字を削除したい文字列」を入れるだけです。
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| テキスト | 必須 | 制御文字を削除したい文字列やセル参照 |
引数はひとつだけ。TRIM関数と同じく、シンプルな構文です。
CLEAN関数が削除する文字・しない文字
CLEAN関数はすべての不要文字を消してくれるわけではありません。削除できる範囲をきちんと把握しておきましょう。
| 文字の種類 | 文字コード | CLEAN | TRIM | 主な発生源 |
|---|---|---|---|---|
| 改行（LF） | CHAR(10) | 削除できる | 不可 | CSVインポート・コピペ |
| 復帰（CR） | CHAR(13) | 削除できる | 不可 | Windows環境のCSV |
| タブ | CHAR(9) | 削除できる | 不可 | TSVファイル・コピペ |
| 半角スペース | CHAR(32) | 削除できない | 削除できる | 手入力・CSV |
| 全角スペース | — | 削除できない | 削除できない | 日本語入力の切り替えミス |
| ノーブレークスペース | CHAR(160) | 削除できない | 削除できない | Webコピペ・HTML |
ポイントは、CLEAN関数とTRIM関数は担当範囲が違うということです。CLEAN関数は制御文字（CHAR(0)〜31）を担当し、TRIM関数は半角スペース（CHAR(32)）を担当します。どちらか片方だけでは不十分なケースが多いので、組み合わせて使うのが定番です。
基本的な使い方：改行・制御文字を一括削除する
CSVインポートで混入した改行を削除する
CSVファイルをインポートすると、セルの中に改行が入り込むことがあります。CLEAN関数で削除してみましょう。
A1に「東京都千代田区」（セル内改行あり）が入っているとします。
=CLEAN(A1)結果は「東京都千代田区」です。改行が削除されて、1行にまとまりました。
列全体に適用するのが実務的な使い方です。B1に数式を入れて下方向にコピーすれば、一括で整形できますよ。
NOTE
改行を削除するのではなく、改行をスペースや特定の区切り文字に「置換」したい場合は、SUBSTITUTE関数を使います。
=SUBSTITUTE(A1, CHAR(10), " ")で改行を半角スペースに置き換えられます。
CODE関数で制御文字を見つける診断コード
「制御文字が入っているかどうか」を確認したいときは、CODE関数が便利です。CODE関数は、文字列の先頭1文字のASCIIコードを返します。
実務で手軽に使える診断コードを紹介します。
=LEN(A1)-LEN(CLEAN(A1))結果が「0」なら制御文字は入っていません。「0」以外なら、その数だけ制御文字が混入しています。
たとえばA1に改行が2つ入っている場合、LEN(A1)は元の文字数+2、LEN(CLEAN(A1))は元の文字数です。差は「2」。制御文字が2文字あるとわかります。
LEN関数の使い方と組み合わせた、覚えておくと便利な診断テクニックです。
TRIM関数・SUBSTITUTE関数との組み合わせパターン
CLEAN関数は制御文字だけを削除する関数です。実務では、スペースや見えない文字も同時に処理したいケースがほとんどです。ここでは定番の組み合わせパターンを紹介します。
CLEAN+TRIMで制御文字とスペースを同時に除去
もっとも基本的な組み合わせです。CLEAN関数で制御文字を削除し、TRIM関数で余分なスペースを削除します。
=TRIM(CLEAN(A1))CSVインポートやコピペデータの整形では、この2つをセットで使うのが定番です。「とりあえずTRIM(CLEAN())」を習慣にしておくと、多くのデータ整形トラブルを未然に防げます。
CLEAN+SUBSTITUTE+TRIMで完全クリーニング
全角スペースやCHAR(160)（ノーブレークスペース）も含めて、すべての不要文字を一括除去したい場合の数式です。
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1," "," "),CHAR(160)," ")))ちょっと長く見えますが、やっていることはシンプルです。
- 内側のSUBSTITUTE：全角スペースを半角スペースに変換
- 外側のSUBSTITUTE：CHAR(160)を半角スペースに変換
- CLEAN：制御文字（改行・タブ等）を削除
- TRIM：余分な半角スペースを削除
この4段構えで、ほぼすべての不要文字を除去できます。外部データを取り込む場面では、このフル版を使っておくと安心です。
TIP
同じ列に毎回この長い数式を入れるのが面倒なら、ヘルパー列（補助列）を1列用意して数式を入れておくのがおすすめです。元データを上書きしたい場合は、補助列をコピーして「値のみ貼り付け」で戻しましょう。
CLEAN関数で消えない文字への対処法
CLEAN関数はASCIIコード0〜31の制御文字しか削除しません。それ以外の「見えない文字」は残ったままになります。
CHAR(160)ノーブレークスペースの削除
Webページからコピペしたデータに混入しやすいのが、CHAR(160)のノーブレークスペースです。見た目は普通のスペースと同じですが、CLEAN関数でもTRIM関数でも削除できません。
SUBSTITUTE関数でCHAR(160)を指定して置換します。
=SUBSTITUTE(A1, CHAR(160), " ")CHAR(160)を半角スペースに変換してから、TRIMで整形するのが定番です。
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))「CLEAN関数を使ったのにまだ変な文字が残っている」という場合は、CHAR(160)を疑ってみてください。
Unicode制御文字の削除（REGEXREPLACE）
まれに、ASCIIコード128以上のUnicode制御文字が混入することがあります。CLEAN関数の対象外なので、正規表現で除去します。
Googleスプレッドシートには正規表現で置換できるREGEXREPLACE関数があります。
=TRIM(CLEAN(REGEXREPLACE(A1, "[x00-x1Fx7F-x9F]", "")))この正規表現は「ASCIIおよびLatin-1の制御文字」を削除します。CLEANとREGEXREPLACEを組み合わせることで、より広い範囲の制御文字を除去できます。
正規表現が難しいと感じたら、無理に使う必要はありません。ほとんどの実務データは前述の =TRIM(CLEAN(SUBSTITUTE(...))) パターンで十分対処できます。
よくある質問（CLEAN関数）
Q. CLEAN関数でスペースは削除できますか？
できません。半角スペース（CHAR(32)）はCLEAN関数の対象外です。スペースの削除にはTRIM関数を使ってください。制御文字とスペースの両方を消したいなら =TRIM(CLEAN(A1)) がおすすめです。
Q. CLEAN関数で改行をスペースに置き換えられますか？
CLEAN関数は改行を「削除」するだけです。スペースに「置換」したい場合は、SUBSTITUTE関数で =SUBSTITUTE(A1, CHAR(10), " ") と書いてください。
Q. ExcelのCLEAN関数との違いはありますか？
基本的な動作は同じです。どちらもASCIIコード0〜31の制御文字を削除します。ただし、ExcelファイルをGoogleスプレッドシートで開いた場合、改行コードの扱いに違いが出ることがあります。スプレッドシートではCHAR(10)がセル内改行です。
Q. CLEAN関数を使っても文字が残る場合は？
CHAR(160)（ノーブレークスペース）やUnicode制御文字はCLEAN関数の対象外です。=CODE(MID(A1, N, 1)) で残っている文字のコードを調べ、SUBSTITUTE関数で個別に削除してください。
まとめ
CLEAN関数は、改行やタブなどの制御文字を削除するシンプルな関数です。
ポイントを整理します。
- 構文は
=CLEAN(テキスト)の1引数だけ。ASCIIコード0〜31の制御文字をまとめて削除する - CLEAN関数とTRIM関数は担当範囲が違う。
=TRIM(CLEAN(A1))のセット使いが定番 =LEN(A1)-LEN(CLEAN(A1))で制御文字の混入を素早く診断できる- 全角スペースやCHAR(160)はCLEAN関数では消えない。SUBSTITUTE関数で個別に対処する
- 完全クリーニングには
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1," "," "),CHAR(160)," ")))を使う
まずは =LEN(A1)-LEN(CLEAN(A1)) で、手元のデータに制御文字が潜んでいないかチェックしてみてください。
