【Googleスプレッドシート】CLEAN関数で印刷不可文字を除去!CSVインポート後の整形

CSVファイルをGoogleスプレッドシートにインポートすると、セル内に表示されない文字が混入してしまうことがあります。これらの印刷不可文字は、データの集計や検索で予期せぬエラーを引き起こす原因となります。この記事では、CLEAN関数を使って印刷不可文字を除去する方法を詳しく解説します。具体的な手順と応用例を通じて、スプレッドシート上のデータをすっきりと整形できるようになります。

CLEAN関数が印刷不可文字を除去する仕組み

スプレッドシートで扱うデータには、キーボードから直接入力できない制御文字が含まれることがあります。これらの文字は、CSVファイルのエクスポート時や外部システムからインポートした際に混入します。代表的なものに、改行(LF:10)、タブ(HT:9)、復帰(CR:13)などがあります。CLEAN関数は、Unicodeのカテゴリ「C」(制御文字)のうち、ASCIIコード0〜31の範囲を自動的に削除します。これにより、画面上で見えないノイズを除去し、データをきれいに整えられます。

CLEAN関数を使った印刷不可文字の除去手順

ここでは、実際にCLEAN関数を使ってセル内の制御文字を除去する方法を説明します。基本的な使い方から、他の関数との組み合わせまで、ステップごとに進めます。

基本的なCLEAN関数の使い方

1. 対象セルを指定する
   制御文字が含まれるセル(例: A2)をクリックします。データが複数ある場合は、最初のセルを選びます。
2. CLEAN関数を入力する
   数式バーに「=CLEAN(A2)」と入力します。これでA2の制御文字が除去された結果が表示されます。
3. 数式をコピーする
   セルの右下にあるフィルハンドルをドラッグして、他のセルにも適用します。範囲全体に数式をコピーできます。

この操作だけで、ほとんどの印刷不可文字が除去されます。ただし、CLEAN関数は文字コード0〜31のASCII制御文字のみを削除します。それ以外の特殊文字(例えば非改行スペース)は削除されません。

TRIM関数と組み合わせて余分なスペースも除去する

CLEAN関数は制御文字を除去しますが、余分なスペースや改行は残ることがあります。TRIM関数を併用すると、連続するスペースや先頭・末尾の空白を削除できます。

1. TRIMとCLEANを組み合わせる
   数式を「=TRIM(CLEAN(A2))」と入力します。CLEANで制御文字を除去した後、TRIMで余計な空白を削除します。
2. 結果を確認する
   表示が整然とし、セル内のデータが1行にまとまります。特にCSVインポート後のデータに有効です。

SUBSTITUTE関数で特定の制御文字を置き換える

改行文字だけを別の文字(スペースなど)に置き換えたい場合は、CLEANの代わりにSUBSTITUTE関数を使います。CLEANはすべての制御文字を削除しますが、SUBSTITUTEなら特定の文字だけを操作できます。

1. 改行をスペースに置き換える
   「=SUBSTITUTE(A2, CHAR(10), ” “)」と入力します。CHAR(10)が改行コードで、これを半角スペースに変更します。
2. タブを削除する
   「=SUBSTITUTE(A2, CHAR(9), “”)」と入力します。タブ文字(CHAR(9))を空文字に置き換えて削除します。
3. CLEANと併用する
   CLEANで残った文字をさらに整形する場合、例えば改行だけ残したいなら、先にCLEANを使い、その後SUBSTITUTEで改行を処理します。

CLEAN関数使用時の注意点と制限

CLEAN関数は便利ですが、いくつかの注意点があります。期待通りに動作しない場合の対処法も理解しておきましょう。

CLEAN関数では除去できない文字がある

CLEAN関数はUnicodeの制御文字の一部のみをカバーします。例えば、U+00A0(非改行スペース)やU+200B(ゼロ幅スペース)は除去できません。これらの文字は、データの見た目には影響しませんが、比較や検索で問題になることがあります。除去するにはSUBSTITUTE関数で個別に指定する必要があります。

改行やタブを意図的に残したい場合

CLEAN関数は改行やタブもすべて削除してしまいます。改行を残したい場合は、CLEANを使わずにSUBSTITUTEで不要な制御文字だけを置き換えてください。例えば、改行(CHAR(10))以外の制御文字を削除するには、CLEANではなくARRAYFORMULAとIFを組み合わせた複雑な式が必要ですが、実用的にはSUBSTITUTEを繰り返す方法が簡単です。

大量データでパフォーマンスが低下する場合

CLEAN関数は1セルごとに計算されるため、数千行を超えるデータでは再計算に時間がかかることがあります。その場合は、値として貼り付けて関数を削除するか、Google Apps Scriptを使った一括処理を検討してください。

CLEAN関数と他の方法の比較

正規表現を使うと、Unicodeのすべての制御文字を削除できます。ただし、高度な知識が必要なため、日常的な用途ではCLEANで十分です。

まとめ

この記事では、CLEAN関数を使ってCSVインポート後の印刷不可文字を除去する方法を解説しました。CLEAN関数は、ASCII制御文字を一括で削除できる便利な関数です。TRIMやSUBSTITUTEと組み合わせることで、より精密なデータ整形が可能になります。特定の制御文字だけを残したい場合はSUBSTITUTEを、すべての制御文字を除去したい場合はREGEXREPLACEを試してみてください。まずはCLEAN関数を基本として、用途に応じて適切な関数を選びましょう。