Excel（エクセル）のCLEAN関数の使い方とトラブル対策

Excelでデータを扱っていると、他のソフトからコピーしたテキストに改行や制御文字が混入してしまい、思わぬトラブルの原因となることがあります。

これらの不要な文字を手作業で削除するのは非常に手間がかかります。

そこで役立つのが、ExcelのCLEAN関数です！

CLEAN関数を使えば、セル内の改行や印刷できない制御文字を一括で削除し、データをクリーンな状態に整えることができ、業務効率化に貢献します。

当サイトでは、Excel（エクセル）のテクニックを覚え、業務効率化や転職を通じて年収アップを目的としています！

この記事では、CLEAN関数の基本的な使い方から応用例、トラブルシューティングまでを詳しく解説します。

CLEAN関数の使い方

CLEAN関数は、指定した文字列から印刷できない文字（制御文字）を削除するための関数です。

これにより、他のアプリケーションからインポートしたデータに含まれる不要な文字を取り除くことができます。

構文:

=CLEAN(文字列)

例えば、セルA1に以下のようなデータがあるとします。

=CHAR(9)&"月次レポート"&CHAR(10)

このセルには、タブ（CHAR(9)）と改行（CHAR(10)）が含まれています。これらの制御文字を削除するには、以下のようにCLEAN関数を使用します。

=CLEAN(A1)

結果として、”月次レポート”という文字列のみが得られます。

CLEAN関数は、以下のような場面で活用できます。

CLEAN関数は、他の関数と組み合わせて使用することで、より効果的にデータを整形できます。

TRIM関数は、文字列の先頭と末尾のスペースを削除します。

CLEAN関数と組み合わせることで、不要なスペースと制御文字を同時に削除できます。

=TRIM(CLEAN(A1))

SUBSTITUTE関数は、指定した文字列を別の文字列に置き換えます。

CLEAN関数で削除できない特定の文字を置き換える際に有効です。

=SUBSTITUTE(CLEAN(A1),CHAR(160),"")

CLEAN関数は、ASCIIコードの制御文字（0～31）を削除しますが、すべての非表示文字を削除できるわけではありません。例えば、Unicodeの制御文字（127、129、141、143、144、157など）は削除されない場合があります。
特定の文字を削除したい場合は、SUBSTITUTE関数を併用することで対応できます。

CLEAN関数では削除できない制御文字が存在する場合があります。

例えば、Unicodeの制御文字（CHAR(160)など）はCLEAN関数で削除されません。

このような場合は、SUBSTITUTE関数を使用して特定の文字を削除することができます。

=SUBSTITUTE(A1,CHAR(160),"")

CLEAN関数を使用しても期待通りに文字が削除されない場合、以下の点を確認してください。

対象のセルに制御文字が含まれているか: セルに制御文字が含まれていない場合、CLEAN関数を使用しても変化はありません。
他の関数との併用: TRIM関数やSUBSTITUTE関数と併用することで、より効果的に不要な文字を削除できます。
データの形式: データが数値や日付形式の場合、CLEAN関数の効果が適用されないことがあります。文字列形式に変換してからCLEAN関数を適用してください。