【Excel】CLEAN関数で制御文字を削除する方法と、改行・空白を残す注意点

取り込んだ商品コードが見た目では同じなのに一致しないとき、末尾のタブや改行が原因になることがあります。ExcelのCLEAN関数は、そのような特定の制御文字を文字列から取り除きます。ただし、文字化けやすべての見えない文字を修復する機能ではありません。

使う前に確認したいのは、消す文字が不要なのか、それとも単語や項目を区切っているのかです。住所の改行や備考のタブを無条件に消すと、別々の語がつながります。原本を残し、変換の前後と長さを比較する手順で進めましょう。

1. CLEANが削除する文字と対象外を知る

CLEANが対象とするのは、7ビットASCIIの先頭32文字、コード0〜31の非印刷文字です。たとえばタブは9、改行のLFは10、CRは13です。セルの折り返し表示で自動的に改行されて見えるだけの場合と、文字として改行コードが入っている場合は異なります。

通常の半角スペース32や、ノーブレークスペース160はCLEANだけでは消えません。全角スペースやゼロ幅スペースなど、Unicodeの別の文字も一律には除去しません。Microsoftも127、129、141、143、144、157などの追加の非印刷文字がCLEAN単独では削除されないことを説明しています。

「文字数が多い」「余白に見える」という理由だけでは、制御文字が原因だとは言い切れません。取り込み時に日本語が別の記号へ変わった文字化けは、CSVの文字コードなどを確認して再取り込みする問題です。CLEANを重ねても、失われた元の文字は復元できません。

2. 作業列で削除結果と文字数を確認する

練習用のシートでA1を「原文」、B1を「CLEAN後」、C1を「削除文字数」とします。A2には次の式を入力します。前後に制御文字を持つ商品コードを作る例です。

=CHAR(9)&"AB-12"&CHAR(10)

B2とC2には、それぞれ次の式を入れます。

=CLEAN(A2)
=LEN(A2)-LEN(B2)

A2の長さは7、B2の長さは5、差は2です。B2には「AB-12」が残り、前後のタブと改行が除去されます。表示だけでは分かりにくい変化も、文字数の差と実際に残ったコードを合わせて確かめられます。

実データではA列を原本、B列を変換後とし、まず数行だけで試します。問題がなければB2・C2の式を必要な行までコピーし、削除文字数が0でない行をフィルターで取り出して確認します。差が2だから正しい、と決めず、商品コードの途中の区切りまで消えていないかも確認してください。

空欄がある列や数式エラーを含む列も、処理の前に確認します。CLEANはエラー値そのものを正しいコードに直す機能ではありません。作業途中からIFERRORで空欄にしてしまうと、変換できなかった行を見失うため、原因が分かるまでは区別して残します。

3. 改行やタブを区切りとして残したい場合

「東京」と「営業所」の間に改行が入ったセルをCLEANで処理すると「東京営業所」になります。住所の二行を一行にする用途では、この連結が目的どおりかを先に決めます。語の境界を残したいなら、削除の前に改行やタブを通常の半角スペースへ変換します。

=SUBSTITUTE(A2,CHAR(13)&CHAR(10)," ")

CRとLFが組になった改行は、まず組全体をスペース一つへ置き換えます。次に別の作業列で、残るCR・LF・タブを必要に応じて処理します。B2に前の式を置いた場合の例は次のとおりです。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13)," "),CHAR(10)," "),CHAR(9)," ")

この結果をC2に置き、D2で=TRIM(CLEAN(C2))とすれば、残る対象制御文字を除き、半角スペースの前後と連続を整理できます。ここでTRIMは語間の半角スペースを一つ残すので、東京と営業所は離れたままです。

ただし、二重スペースの数そのものに意味がある固定幅データや、改行を保持すべき文章には、この処理を使いません。表示用の文章と検索用コードを別列にすれば、読みやすさを保った原文と、照合用に整えた文字列を両方残せます。

4. TRIMやSUBSTITUTEとは役割を分ける

方法	主に行う処理
CLEAN	コード0〜31の非印刷文字を削除
TRIM	半角スペースの前後を除去し、語間の連続を一つに整理
SUBSTITUTE	指定した文字や文字列を別の文字へ置換

=TRIM(CLEAN(A2))は便利な組み合わせですが、すべての空白や不要文字を取り除くという意味ではありません。また、結果を計算できる数値へ変換する保証もありません。数値への変換が必要かどうかは、対象が数量なのか、先頭0を持つコードなのかで判断します。

検索キーを整える場合は、検索する側とマスター側に同じルールを適用します。一方だけからタブを取り除き、もう一方に同じタブが残っていれば一致しないままです。列を直接一括置換する前に、二つの作業列で処理結果を比較し、マスターを修正する権限や運用ルールも確認してください。

CLEANはデータ内の意味や安全性を判定するものではありません。外部入力を「CLEAN済みだから安全」と扱うのではなく、必要な桁数・許可する文字種・必須項目などは別の条件で検証します。

5. 残った見えない文字を一文字ずつ調べる

末尾が疑わしい場合は、空のセルでないことを確認して=UNICODE(RIGHT(A2,1))を使います。先頭ならLEFT、途中ならMIDで一文字を取り出します。UNICODEが調べるのは渡した文字列の最初の一文字なので、A2全体を指定しても末尾まで一括検査するわけではありません。

たとえば末尾が160だった場合はノーブレークスペースです。コード32の通常スペースへ置き換えてから整えるなら、次のようにします。日本語環境でも対象のUnicode文字を明確にするため、CHARではなくUNICHARを使います。

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

これは確認できた160だけを置き換える式です。全角スペースやゼロ幅スペースまで自動的に解決するものではありません。未知の文字が見つかったら、入力元の仕様を調べ、削除してよいかを決めてから対象を追加します。

LENの差が0でも、異なる文字が同じ文字数だけ入っている場合はあります。逆に文字数が多くても、必要な記号や濁点である可能性があります。長さの差は調査の入口にとどめ、文字の種類を確かめて判断してください。

6. 確定前の検品と元に戻す方法

変換後は、処理件数、空欄、削除が多い行、検索で一致しなかった行を確認します。別々の商品コードが同じ文字列へ変わっていないかも重要です。たとえば本来区別すべき「AB」と「A+タブ+B」が両方ABになるなら、機械的な削除を確定せずデータの管理者へ意味を確認します。

確認が済んだ結果を提出用シートへコピーし、[貼り付け]の[値]で固定します。元の列を消すことを必須手順にはせず、原本と変換式を別の作業ブックに残します。値貼り付け後は元データが変わっても更新されないため、処理日と対象ファイルを記録しておきます。

誤って上書きした直後は元に戻す操作を使い、すでに閉じた場合は保存しておいた原本からやり直します。CLEANの結果から、どこにどの制御文字があったかを完全には再現できません。「削除する範囲を知る」「区切りを残すか決める」「原本と照合する」の順で進めれば、見えない文字を整理しながら必要な内容も守れます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】