バックオフィスから経営を強くする

新規登録
  1. 「マネーフォワード クラウド」
  2. 業務効率化の基礎知識
  3. CLEAN関数の使い方！エクセルで印刷できない文字を削除する方法
  • 作成日 : 2025年8月25日

CLEAN関数の使い方！エクセルで印刷できない文字を削除する方法

CLEAN関数は、テキストデータから印刷できない制御文字を削除するエクセルの文字列関数です。外部システムからのデータ取り込み、Webデータのコピー＆ペースト、CSVファイルの処理など、様々なデータクリーニング作業で活用されています。

本記事では、CLEAN関数の基本的な使い方から実務での応用例、TRIM関数やSUBSTITUTE関数との効果的な組み合わせ方、そしてよくある問題とその解決方法まで、初心者にも分かりやすく解説します。データの品質を保つために役立つCLEAN関数の活用方法をマスターしましょう。

CLEAN関数の使い方

CLEAN関数とは

CLEAN関数は、文字列に含まれる印刷できない文字を削除する関数です。印刷できない文字とは、ASCIIコードの0から31までの制御文字を指し、これらは画面表示や印刷時に正常に表示されない特殊な文字です。

データの見た目は正常でも、実際には目に見えない制御文字が含まれていることがあり、これが原因で検索や集計、データ連携で問題が発生することがあります。

基本構文

CLEAN関数の構文は非常にシンプルです。

=CLEAN (文字列)

文字列：クリーニングしたいテキストを指定します。セル参照、文字列の直接入力、他の関数の結果など、様々な形式で指定可能です。

削除される文字の詳細

CLEAN関数が削除する制御文字について理解することは重要です。ASCIIコード0から31までの32個の制御文字が対象となりますが、主なものを紹介します。

  • 改行文字（LF）：ASCIIコード10。Linuxやmacオペレーティングシステムで使用される改行コードです。
  • キャリッジリターン（CR）：ASCIIコード13。古いMacシステムやプリンターで使用される復帰コードです。
  • タブ文字：ASCIIコード9。表計算ソフトやテキストエディタでの位置揃えに使用されます。
  • ヌル文字：ASCIIコード0。文字列の終端を示す特殊な文字です。

これらの文字は通常の表示では見えませんが、データ処理において様々な問題を引き起こす可能性があります。

基本的な使用例

実際にCLEAN関数を使用してみましょう。A1セルに制御文字を含むテキストが入力されている場合、次のように記述します。

=CLEAN (A1)

直接文字列を指定する場合の例も見てみましょう。改行を含む文字列をクリーニングする場合：

=CLEAN (“エクセル” & CHAR (10) & “データ”)

この数式では、CHAR (10)で改行文字を挿入した文字列から、その改行文字を削除します。結果は「エクセルデータ」となります。

CLEAN関数の動作確認方法

制御文字は目に見えないため、CLEAN関数が正しく動作しているか確認する方法を知っておくことが重要です。

LEN関数を使用して、処理前後の文字数を比較する方法が効果的です。

=LEN(A1) – LEN (CLEAN (A1))

この数式で、削除された制御文字の数を確認できます。結果が0より大きければ、制御文字が削除されたことを意味します。

CLEAN関数の利用シーン

外部データのインポート時の処理

企業では様々なシステムからエクセルにデータをインポートすることがありますが、その際に制御文字が混入することがよくあります。

CSVではレコード区切りにCR/LFなどの制御文字が使われますが、フィールド区切りは通常カンマ（制御文字ではありません）です。TSVなどタブ区切りの場合はタブ（CHAR (9)）が区切り文字として用いられます。これらの文字が残っていると、VLOOKUPやMATCH関数での検索がうまく動作しないことがあります。

インポート直後にCLEAN関数で処理することで、後続の作業がスムーズに進められます。たとえば、顧客マスタデータの処理では次のような手順で行います。

=CLEAN (A2)  // 顧客名から制御文字を削除

Webサイトからのデータコピー時のクリーニング

インターネット上の表やテキストをコピーしてエクセルに貼り付ける際、Webページからコピーしたデータでは、HTMLタグ自体が貼り付けられるのではなく、改行（CHAR (10)）やノーブレークスペース（CHAR (160）などに変換されることがあります。

CLEANは0～31の制御文字のみを削除するため、CHAR (160)の除去にはSUBSTITUTEやTRIMとの併用が有効です。

特にオンラインフォームやWebアプリケーションからコピーしたデータには、見た目では分からない制御文字が含まれやすく、これが原因でデータの並べ替えやフィルタリングが期待通りに動作しない場合があります。

商品情報や価格表をWebサイトからコピーした場合の処理例：

=CLEAN (B2)  // 商品説明から不要な制御文字を削除

テキストファイルの処理

プログラムやスクリプトが生成したテキストファイルをエクセルで扱う場合、異なるオペレーティングシステム間での改行コードの違いが問題となることがあります。

CLEANはCR(13)やLF(10)を削除するため、「改行を消して1行にしたい」用途に有効です。改行を保持したまま統一したい場合は、たとえば

=SUBSTITUTE(SUBSTITUTE(A1,CHAR (13)&CHAR (10),CHAR (10)),CHAR (13),CHAR (10))

のようにSUBSTITUTEでCRLF/CRをLF(CHAR (10))に置換します。

ログファイルの処理例：

=CLEAN (C2)  // ログメッセージから改行コードを統一的に削除

メール本文の整形

メールソフトからコピーした本文には、様々な制御文字が含まれることがあります。特に、複数の人が返信を重ねたメールスレッドでは、引用記号や改行が複雑に入り混じります。

営業活動の記録や顧客対応履歴をエクセルで管理する際、メール本文をそのままコピーすると、セル内での表示が崩れたり、印刷時にレイアウトが乱れたりすることがあります。CLEAN関数で前処理を行うことで、これらの問題を防げます。

データベース連携での活用

文字コードの変換過程で実際の制御文字（CR/LF/TABなど）が混入する場合はCLEANで除去できます。一方、n や t が文字列として残っているだけの場合はCLEANでは消えないため、SUBSTITUTEで「n」を空文字に置換するなどの処理が必要です。

特に、複数行にわたるコメントフィールドや、特殊記号を含むデータを扱う場合、CLEAN関数での事前処理が重要になります。処理を怠ると、データベースへの再インポート時にエラーが発生する可能性があります。

CLEAN関数の応用・他関数との組み合わせ

TRIM関数との併用

CLEAN関数とTRIM関数を組み合わせることで、実務上必要なレベルまでテキストを整えることができます。CLEAN関数は制御文字を削除し、TRIM関数は前後の空白と文字列内の余分な空白を削除します。

最も効果的な組み合わせ方は、まずCLEAN関数で制御文字を削除し、その後TRIM関数で空白を整理する方法です。

=TRIM (CLEAN (A1))

この順序が重要な理由は、まずCLEANで制御文字（ASCII 0〜31）を削除し、その結果（または元から存在する）連続スペースをTRIMで正規化できるためです。CLEANが制御文字を空白に“変換”するわけではありません。

実務での応用例として、顧客名簿の整理では次のような処理を行います。

=TRIM (CLEAN (A2))  // 顧客名から制御文字と余分な空白を同時に削除

SUBSTITUTE関数での特定文字の置換

CLEAN関数では削除できない印刷可能な特殊文字（ASCIIコード32以降）を処理する場合、SUBSTITUTE関数と組み合わせます。

改行を空白として残したい場合は、CRLF→CR→LFの順に置換し、最後に余分な空白を整理します。

=TRIM (CLEAN (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, CHAR (13)&CHAR (10), ” “), CHAR (13), ” “), CHAR (10), ” “)))

（CR/LFは空白に変換して残し、それ以外の制御文字はCLEANで削除、TRIMで二重スペースを1つにします。）

全角スペース（U+3000）とNBSPをともに半角スペースへ変換してから整形します。

=TRIM (CLEAN (SUBSTITUTE(SUBSTITUTE(A1, UNICHAR (12288), ” “), CHAR (160), ” “)))

（UNICHAR (12288)は全角スペース、CHAR (160)はNBSP。両者を半角スペースへ置換後、CLEANで制御文字を削除し、TRIMで空白を整理します。）

LEN関数による処理前後の確認

データクリーニングの効果を定量的に把握するため、LEN関数と組み合わせて使用します。

処理前後の文字数差を別列に表示する実用的な例：

=LEN(A1) – LEN (CLEAN (A1)) & “文字削除”

この方法により、どの行でどれだけの制御文字が削除されたかを一目で確認できます。

IF関数による条件付き処理

すべてのセルに対してCLEAN関数を適用する必要がない場合、IF関数で条件判定を行います。

制御文字が含まれている場合のみ処理を実行する例：

=IF(LEN(A1) <> LEN (CLEAN (A1)), CLEAN (A1), A1)

この数式は「制御文字がある行ではCLEANした結果、ない行では元の値」を返します。ただしCLEANは判定に用いているため常に評価されます。重複評価を避けるには、たとえばMicrosoft 365/Excel 2021以降なら

=LET(t,CLEAN (A1), IF(LEN(A1)<>LEN(t), t, A1))

のように一度だけCLEANを評価します。

CONCATENATE関数での文字列結合

複数のセルを結合する際、各セルに含まれる制御文字を事前に削除することで、きれいな結合結果が得られます。

住所データの結合例：

=CONCATENATE(CLEAN (A1), ” “, CLEAN (B1), ” “, CLEAN (C1))

または、連結演算子「&」を使う場合：

=CLEAN (A1) & ” ” & CLEAN (B1) & ” ” & CLEAN (C1)

（多数のセルを結合する場合は TEXTJOIN/CONCAT の利用も有効です。）

配列数式での一括処理

大量のデータを効率的に処理する場合、配列数式としてCLEAN関数を使用できます。

範囲A1:A100のすべてのセルを一度に処理する配列数式：

=CLEAN (A1:A100)

この数式を選択した範囲に配列数式として入力（Ctrl+Shift+Enter）することで、複数のセルを一括処理できます。

CLEAN関数のよくあるエラーと対策

改行が削除されて文章がつながる問題

CLEAN関数の最も一般的な問題は、意図的に入力した改行まで削除されることです。セル内で意図的に改行（Alt+Enter）を使用している場合、CLEAN関数はこれも削除してしまいます。

この問題を解決するには、必要な改行を保護する処理を追加します。

=SUBSTITUTE(CLEAN (SUBSTITUTE(A1, CHAR (10), “【改行】”)), “【改行】”, CHAR (10))

この数式では、一時的に改行を別の文字列に置換してからCLEAN関数を適用し、最後に改行を復元します。

より簡潔に特定の制御文字だけを削除する例（改行 LF は残し、CR や US を除去する場合）：

=SUBSTITUTE(SUBSTITUTE (A1, CHAR (13), “”), CHAR (31), “”)

※ 文字コード 0（NUL）を削除したい場合は SUBSTITUTE ではなく CLEAN を併用してください（例：必要な改行を一時退避 → CLEAN → 復元）。

全角文字や特殊記号が削除されない

CLEAN関数はASCIIコード0-31の制御文字のみを削除するため、全角スペースや特殊な記号は削除されません。

これらの文字も処理する必要がある場合は、追加の処理が必要です。

=TRIM(CLEAN (SUBSTITUTE(SUBSTITUTE(A1, “　”, ” “), “※”, “”)))

この例では、全角スペースを半角スペースに変換し、特定の記号（※）を削除しています。

処理後もVLOOKUPが機能しない

CLEAN関数で処理してもVLOOKUPやMATCH関数が正常に動作しない場合、印刷可能な特殊文字が原因の可能性があります。

CODE関数を使用して、問題の文字を特定する診断方法：

=CODE(RIGHT(A1, 1))  // 末尾の文字コードを確認

=CODE(LEFT(A1, 1))   // 先頭の文字コードを確認

特定できた文字コードをSUBSTITUTE関数で個別に処理します。

=SUBSTITUTE(CLEAN (A1), CHAR (160), “”)

パフォーマンスの問題

大量のデータに対してCLEAN関数を適用すると、計算速度が低下することがあります。

パフォーマンスを改善する方法として、必要な場合のみ処理を実行する条件式の使用があります。

=IF(ISERROR(FIND(CHAR (10), A1)), A1, CLEAN (A1))

この数式は、改行文字が含まれている場合のみCLEAN関数を実行します。

また、処理結果を値として貼り付けることで、再計算の負荷を軽減できます。

エラー値の処理

元のセルにエラー値（#N/A、#VALUE!など）が含まれている場合、CLEAN関数もエラーを返します。

IFERROR関数でエラーハンドリングを行う方法：

=IFERROR(CLEAN (A1), A1)

この数式により、エラーが発生した場合は元の値をそのまま返します。

CLEAN関数で制御文字を削除しデータ品質を向上

CLEAN関数は、エクセルでテキストデータから印刷できない制御文字を削除する関数です。外部システムからのデータ取り込みやWebデータの整形など、さまざまなシーンでデータ品質を安定して保つのに役立ちます。

TRIM関数やSUBSTITUTE関数と組み合わせることで、不要な空白や特殊文字にも対応しやすくなり、テキスト整形の自由度が高まります。

改行の扱いや全角スペースの対応といった注意点も踏まえて、状況に応じて柔軟に使い分けることが、Excelにおける安定したデータ処理につながります。


※ 掲載している情報は記事更新時点のものです。

  • 【監修】マネーフォワード クラウド 業務効率化の基礎知識

    マネーフォワード クラウド 業務効率化の基礎知識 編集部では、業務効率化や生産性向上のノウハウ、クラウドツールの使い方など、ビジネスシーンで役立つ基礎知識をご紹介しています。

※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容（テンプレートを含む）の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。

関連記事

  1. 「マネーフォワード クラウド」
  2. 業務効率化の基礎知識
  3. CLEAN関数の使い方！エクセルで印刷できない文字を削除する方法