概要
CLEAN関数は「文字列から印刷できない制御文字（不可視文字）を削除する」ための関数です。外部システムから取り込んだデータやコピー＆ペーストしたテキストには、改行コードやタブ、制御文字が紛れ込むことがあります。これらは見た目では分かりにくいですが、検索や一致判定、数値変換の邪魔になります。CLEANを使うと、そうした余分な文字を取り除き、クリーンな文字列に整えられます。
基本の使い方
書式
=CLEAN(文字列)
例
=CLEAN(A2) // A2セルの文字列から不可視文字を削除
=CLEAN("東京"&CHAR(10)&"江東区") // 改行コードを削除 → "東京江東区"
具体例
改行コードを削除して検索可能にする
外部データに改行が混じっている場合：
=CLEAN(B2)
「東京都[改行]江東区」が「東京都江東区」に整えられます。
タブや制御文字を削除して一致判定を安定化
=CLEAN(C2)
タブが混じっていても削除されるため、検索や一致判定が正しく動作します。
TRIMと組み合わせて空白も整理
=TRIM(CLEAN(D2))
CLEANで不可視文字を削除し、TRIMで余分なスペースを整えると、最もクリーンな文字列になります。
応用テンプレート
数値変換前にクリーン化
=VALUE(CLEAN(E2))
不可視文字を削除してから数値に変換することで、エラーを防ぎます。
データベースから取り込んだ文字列を整える
=TRIM(CLEAN(F2))
改行やタブ、余分なスペースをすべて削除して、検索や集計に使いやすくします。
改行を削除して1行にまとめる
=CLEAN(G2)
複数行の住所を1行に整えられます。
よくあるつまずきと対策
全角スペースは削除されない
CLEANは制御文字を削除しますが、全角スペースは残ります。必要ならSUBSTITUTEで対応します。
=SUBSTITUTE(CLEAN(A2)," ","")
改行を「残したい」場合は不向き
CLEANは改行も削除します。改行を残したい場合はSUBSTITUTEで別の文字に置き換える方法を検討しましょう。
見た目では分からない文字が削除される
CLEANは不可視文字を対象にするため、結果が変わっているのに気づきにくいことがあります。LEN関数で文字数を比較すると効果が確認できます。
=LEN(A2) - LEN(CLEAN(A2))
例題
問題1: A2セルの文字列から不可視文字を削除してB2に表示してください。
解答例:
=CLEAN(A2)
問題2: C2セルの文字列に含まれる改行を削除してD2に表示してください。
解答例:
=CLEAN(C2)
問題3: E2セルの文字列から不可視文字を削除し、さらに余分なスペースも取り除いてF2に表示してください。
解答例:
=TRIM(CLEAN(E2))
問題4: G2セルの文字列をクリーン化して数値に変換し、H2に表示してください。
解答例:
=VALUE(CLEAN(G2))
問題5: I2セルの文字列から全角スペースと不可視文字を削除してJ2に表示してください。
解答例:
=SUBSTITUTE(CLEAN(I2)," ","")
まとめ
CLEANは「不可視文字を削除して文字列をクリーンにする」関数です。改行やタブ、制御文字を取り除くことで、検索・一致判定・数値変換が安定します。TRIMやSUBSTITUTEと組み合わせると、全角スペースや余分な空白も整理でき、実務でのデータクレンジングに非常に役立ちます。講師として強調したいのは「CLEANは見えない文字を消すための関数」。これを習慣的に使えば、データ処理のトラブルを大幅に減らせます。