概要
「見えない“ゴミ文字”のせいで、並び替え・検索・VLOOKUP がうまくいかない」
「Web やシステムからコピペした値を使うと、なぜか挙動がおかしい」
こういうとき、犯人になりやすいのが 制御文字（改行・タブなどの目に見えないコード） です。
この“見えないノイズ”を一気に掃除してくれるのが CLEAN 関数です。
CLEAN は、文字列に紛れ込んだ 印字不可能な制御文字（ASCII 0〜31）を削除 してくれる「クリーニング専用関数」です。
CLEAN 関数の基本
CLEAN の構文と意味
=CLEAN(文字列)
文字列
制御文字を取り除きたい元の文字列（セル参照でも、”abc” のような直接入力でもOK）を指定します。
CLEAN が削除する主な制御文字の例は次のようなものです。
- 改行（LF：
CHAR(10)） - タブ（
CHAR(9)） - その他、ASCII 0〜31 の印字できない制御文字（ベル、フォームフィードなど）
見た目には「よく分からない変な動き」しか見えないことが多いですが、
CLEAN をかけると、これらの“見えないゴミ”が一掃されます。
基本パターン：制御文字をまとめて削除する
コピペしたデータを一度 CLEAN に通す
A2 に、外部システムからコピペした文字列が入っているとします。
見た目は普通なのに、なぜか関数がうまく動かない、というケースです。
まずはシンプルに CLEAN をかけてみます。
=CLEAN(A2)
これだけで、A2 に紛れ込んでいた 改行・タブ・制御コード が削除され、
Excel 内で扱いやすい“きれいな文字列”になります。
「とりあえず CLEAN を通してから使う」という前処理は、実務ではかなり有効です。
改行やタブを含む文字列の具体例
改行＋タブ入り文字列のクリーニング
A2 に、実はこんな文字列が入っているとします（見えにくいですがイメージとして）。
- 行の途中に改行（
CHAR(10)） - 項目の区切りにタブ（
CHAR(9)）
CLEAN をかけると、
=CLEAN(A2)
結果として
- 改行 → 削除
- タブ → 削除
され、1行に詰まったプレーンな文字列になります。
もし「改行を残したい」「タブをスペースに変えたい」などの“置き換え”が目的なら、
CLEAN ではなく SUBSTITUTE を使いますが、
「いったん全部消したい」「見えない制御文字は要らない」という場合は CLEAN 一発でOKです。
CLEAN と他の関数の組み合わせ
CLEAN ＋ TRIM で「制御文字＋余計な空白」を一掃
制御文字だけでなく、前後の空白や連続スペースも気になる場合は、
CLEAN と TRIM を組み合わせるのが定番です。
=TRIM(CLEAN(A2))
動きは次の通りです。
- CLEAN(A2)
→ 改行・タブなどの制御文字を削除 - TRIM(その結果)
→ 先頭・末尾の半角スペース削除、途中の連続スペースを1つに整理
これで、「制御文字＋余計な空白」のほとんどが一度にきれいになります。
注意点：CLEAN だけでは消えないもの
CLEAN が削除してくれるのは「制御文字」だけです。
逆に言うと、次のようなものは CLEAN では消えません。
- 半角スペース
" " - 全角スペース
" " - 見た目には空白に見える特殊な幅のスペース（ノーブレークスペースなど）
- 場合によっては CR（
CHAR(13)）が残ることもある
Windows の改行は「CR＋LF（CHAR(13)&CHAR(10)）」の組み合わせなので、
CLEAN で LF（CHAR(10)）は消えても、CR（CHAR(13)）が残るケースがあります。
この場合は、SUBSTITUTE を併用します。
=SUBSTITUTE(CLEAN(A2), CHAR(13), "")
さらに全角スペースも消したいなら、
=TRIM(SUBSTITUTE(CLEAN(A2), " ", ""))
のように「CLEAN → SUBSTITUTE → TRIM」と重ねていくイメージです。
実務での使いどころ
外部システムやWebからのデータ取り込み時
- CSV やテキストをコピペしたら、セル内に謎の改行やタブが含まれていた
- Web からコピーした文章を分析したいが、そのままでは関数や検索がうまく動かない
こういうときは、「元データはまず CLEAN してから使う」というルールにすると安定します。
VLOOKUP・XLOOKUP・一致判定の前処理
「文字は合っているのに一致しない」
「一部の行だけなぜか検索にヒットしない」
この原因が「制御文字」だった、ということもよくあります。
検索値側やキー列側を事前に CLEAN しておくと、
見えない制御文字が原因の不一致を防げます。
例：
=VLOOKUP(CLEAN(A2), 範囲, 列番号, FALSE)
例題
問題1
A2 に、改行やタブなどの制御文字が含まれている可能性のある文字列が入っています。
この制御文字をすべて削除した“きれいな文字列”を返す式を書いてください。
=CLEAN(A2)
問題2
A2 に複数行のテキストが入っており、改行を含む制御文字を削除した上で、先頭と末尾の余計な空白も取り除きたいです。
CLEAN と TRIM を組み合わせた式を書いてください。
=TRIM(CLEAN(A2))
問題3
A2 の文字列には、CLEAN で削除される制御文字に加えて、CR（CHAR(13)）も混ざっています。
CLEAN と SUBSTITUTE を組み合わせて、CR も削除する式を書いてください。
=SUBSTITUTE(CLEAN(A2), CHAR(13), "")
問題4
A2 の値を VLOOKUP の検索値として使いたいのですが、見えない制御文字が混じっていて一致しません。
検索値側で制御文字を削除してから VLOOKUP する式を書いてください。
（検索範囲・列番号・FALSE はそのままとします）
=VLOOKUP(CLEAN(A2), 検索範囲, 列番号, FALSE)
問題5
A2 に Web からコピペしたテキストが入っています。
制御文字を CLEAN で削除し、さらに全角スペースもすべて削除してから最終的な文字列を得たいです。
CLEAN と SUBSTITUTE を組み合わせた式を書いてください。
=SUBSTITUTE(CLEAN(A2), " ", "")
CLEAN の役割は、とてもシンプルに言うと
「目に見えない制御文字をまとめて掃除する」
ことです。
まずは CLEAN で制御文字を削除 → 必要に応じて TRIM や SUBSTITUTE を追加
という“クリーニング三段活用”を型として覚えておくと、
外部データやコピペ文字列を扱うときのトラブルをかなり減らせます。