【Excel】改行コードがCR+LFとLFで混在する!Excelのセル内改行を統一するCLEAN関数の応用

Excelのセル内で改行コードが混在していると、データの表示が崩れたり、後続の処理で問題が発生したりします。特に、異なるシステムからインポートしたデータでは、CR+LF(キャリッジリターン+ラインフィード)とLF(ラインフィード)の改行コードが混在することがあります。この状態を統一しないと、データの見栄えが悪くなるだけでなく、検索や抽出、別のシステムへの連携などの際に予期せぬエラーを引き起こす可能性があります。本記事では、Excelのセル内改行コードをCLEAN関数とSUBSTITUTE関数を組み合わせて統一し、データをきれいに整形する方法を解説します。

CLEAN関数は、印刷できない文字を削除する関数ですが、改行コードそのものは削除しません。しかし、SUBSTITUTE関数と組み合わせることで、改行コードの統一が可能になります。本記事を読めば、混在する改行コードをきれいに統一し、Excelデータをより扱いやすくする方法が理解できます。

改行コードの種類と混在による影響

Excelのセル内改行は、通常、文字コードの組み合わせによって表現されます。一般的に、Windows環境ではCR+LF(ASCIIコード13と10)が、macOSやLinuxなどのUnix系環境ではLF(ASCIIコード10)が改行コードとして使用されます。

これらの改行コードが混在すると、Excel上では見た目上は改行されているように見えても、内部的には異なるコードで認識されています。この違いは、データのエクスポート・インポート時や、他のアプリケーションとの連携時に問題を引き起こします。例えば、CSVファイルとしてエクスポートした際に、改行コードの解釈の違いから意図しない行分割が発生することがあります。

CLEAN関数とSUBSTITUTE関数による改行コード統一の手順

Excelのセル内にあるCR+LFとLFの改行コードを統一するには、CLEAN関数とSUBSTITUTE関数を組み合わせて使用します。CLEAN関数は印刷できない文字を削除する機能がありますが、改行コード自体は削除しないため、SUBSTITUTE関数で改行コードを別の文字に置換してからCLEAN関数で不要な文字を削除し、最後に目的の改行コードに置換するという手順を踏みます。

1. 元のデータを確認する
   改行コードが混在しているセル範囲を確認します。ここでは、A1セルからA10セルにデータがあると仮定します。
2. 一時的な置換用の列を作成する
   元のデータを保持するため、隣接する列(例えばB列)に数式を入力します。B1セルに以下の数式を入力してください。
   =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10))
   この数式は、まずCR+LF(CHAR(13)&CHAR(10))をLF(CHAR(10))に置換し、次に残っている可能性のあるCR(CHAR(13))もLFに置換します。これにより、すべての改行コードがLFに統一されます。
3. CLEAN関数で不要な文字を削除する
   上記数式の結果には、念のためCLEAN関数を適用し、印刷できない制御文字を削除します。B1セルに以下の数式を入力してください。
   =CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10)))
   CLEAN関数は、ASCIIコード1〜31の制御文字を削除します。改行コード(CR+LFやLF)は制御文字ではありませんが、他の不要な制御文字が混入している場合に削除する目的で追加します。
4. 数式をコピーする
   B1セルの数式を、データがある範囲(B10セルまで)にコピーします。フィルハンドルをダブルクリックするか、ドラッグしてコピーしてください。
5. 値を貼り付けて数式を確定する
   B列の数式の結果を、元のA列に「値」として貼り付けます。B列を選択し、コピー(Ctrl+C)します。次に、A列の先頭セル(A1)を選択し、右クリックメニューから「形式を選択して貼り付け」を選び、「値」を選択してOKをクリックします。これにより、数式が実際の値に置き換わります。
6. 一時列を削除する
   数式が値に置き換わったA列を確認し、問題がなければ、一時的に使用したB列を削除します。

改行コードをCR+LFに統一する場合

一般的にWindows環境ではCR+LFが標準的な改行コードであるため、これを統一したい場合もあります。その場合は、上記のLFへの統一手順の最後に、LFをCR+LFに置換する処理を追加します。

1. CR+LFをLFに統一する
   まず、前述の手順で、すべての改行コードをLF(CHAR(10))に統一します。B1セルには以下の数式を入力し、A1〜A10セルまでコピーします。
   =CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10)))
2. LFをCR+LFに置換する
   次に、LF(CHAR(10))をCR+LF(CHAR(13)&CHAR(10))に置換します。B1セルに以下の数式を入力してください。
   =SUBSTITUTE(B1,CHAR(10),CHAR(13)&CHAR(10))
   この数式は、LF(CHAR(10))をCR+LF(CHAR(13)&CHAR(10))に置換します。
3. 数式をコピーして値を貼り付ける
   上記数式をA10セルまでコピーし、結果をA列に値として貼り付けます。
4. 一時列を削除する
   不要になったB列を削除します。

Power Queryを使用した改行コード統一

大量のデータを扱う場合や、定期的にこの作業を行う必要がある場合は、Power Queryを使用するとより効率的です。Power Queryは、Excel 2016以降およびMicrosoft 365で利用可能です。Excel 2010、2013ではアドインとして追加できます。

Power Queryで改行コードをLFに統一する手順

以下の手順で、Power Queryを使用して改行コードをLFに統一します。

1. データをPower Queryに読み込む
   Excelシートのデータ範囲を選択し、「データ」タブから「テーブルまたは範囲から」をクリックします。
2. Power Queryエディターを開く
   「テーブルの作成」ダイアログが表示されたら、範囲が正しいか確認し、「OK」をクリックします。Power Queryエディターが開きます。
3. 改行コードを置換する
   改行コードを統一したい列を選択します。列ヘッダーを右クリックし、「列の置換」を選択します。
4. 置換ダイアログの設定
   「列の置換」ダイアログで、以下のように設定します。
   • 検索する値: #(cr)#(lf) (CR+LFを表します)
   • 置換後の値: #(lf) (LFを表します)

   「OK」をクリックします。

5. 再度置換する
   再度、同じ列を選択し、「列の置換」を選択します。
   • 検索する値: #(lf) (LFを表します)
   • 置換後の値: #(lf) (LFを表します)

   「OK」をクリックします。この手順は、CR+LFをLFに置換した後に、単独で残っているLFをLFに置換することで、すべての改行コードがLFであることを保証するためです。

6. 不要な制御文字を削除する
   「列の置換」機能だけでは、CLEAN関数のようにすべての印刷できない文字を削除するわけではありません。必要に応じて、カスタム列を追加して`Text.Clean`関数を使用するか、Power Queryの「変換」タブにある「書式」→「クリーン」機能を使用することも検討してください。ここでは、改行コードの統一に焦点を当てます。
7. データをExcelに読み込む
   「ホーム」タブの「閉じて読み込む」をクリックし、「閉じて次に読み込む…」を選択します。
8. 読み込み先の指定
   「データのインポート」ダイアログで、データの読み込み場所(既存のシートまたは新規シート)を指定し、「OK」をクリックします。

Power Queryで改行コードをCR+LFに統一する手順

CR+LFに統一したい場合は、上記のLFへの統一手順の後、LFをCR+LFに置換するステップを追加します。

1. LFをCR+LFに置換する
   Power Queryエディターで、LFに統一された列を選択します。列ヘッダーを右クリックし、「列の置換」を選択します。
   • 検索する値: #(lf)
   • 置換後の値: #(cr)#(lf)

   「OK」をクリックします。

2. データをExcelに読み込む
   「ホーム」タブの「閉じて読み込む」から、データをExcelシートに読み込みます。

CLEAN関数とSUBSTITUTE関数を使う上での注意点

CLEAN関数とSUBSTITUTE関数を組み合わせて改行コードを統一する際に、いくつか注意すべき点があります。

CLEAN関数で削除される文字

CLEAN関数は、ASCIIコード1から31までの制御文字を削除します。これには、タブ文字(ASCIIコード9)や、一部の特殊な記号などが含まれることがあります。改行コード(CR+LFやLF)は制御文字ではありませんが、もしデータ中にそれ以外の不要な制御文字が混入している場合は、CLEAN関数によって削除されます。意図しない文字が削除されないか、事前に確認することが重要です。

SUBSTITUTE関数で置換する文字コード

CHAR(13)はCR(キャリッジリターン)、CHAR(10)はLF(ラインフィード)を表します。Windowsの標準的な改行はCR+LF(CHAR(13)&CHAR(10))ですが、環境によってはCRのみ、LFのみが存在する場合があります。そのため、SUBSTITUTE(SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10))のように、まずCR+LFをLFに統一し、次に残ったCRをLFに置換することで、すべての改行コードをLFに統一するのが確実です。

数式の結果を値として貼り付ける重要性

数式の結果を元のセルに「値」として貼り付ける作業は非常に重要です。これを怠ると、元のセルには数式が残ったままになり、データが更新された際に意図しない結果になる可能性があります。必ず「形式を選択して貼り付け」機能で「値」を選択し、数式を確定させてください。

大量データの場合のパフォーマンス

非常に大量のセルにこの数式を適用すると、Excelの計算に時間がかかることがあります。そのような場合は、Power Queryの使用を検討してください。Power Queryは、大量データの処理に特化しており、パフォーマンスが優れています。

CLEAN関数とSUBSTITUTE関数応用例

改行コードの統一以外にも、CLEAN関数とSUBSTITUTE関数は様々なテキスト処理に応用できます。以下にその例を挙げます。

不要なスペースの削除

CLEAN関数は印刷できない文字を削除しますが、SUBSTITUTE関数を使えば、セル内の不要なスペース(全角スペースや、単語間の連続する半角スペースなど)を削除することも可能です。

1. 全角スペースを半角スペースに置換する
   =SUBSTITUTE(A1,"　"," ")
   A1セルの全角スペースを半角スペースに置換します。
2. 連続する半角スペースを1つにまとめる
   =SUBSTITUTE(A1," "," ")
   この数式を繰り返し適用するか、TRIM関数と組み合わせることで、連続する半角スペースを1つにまとめることができます。TRIM関数は、文字列の先頭、末尾、および単語間の不要なスペースを削除します。

特定の文字列の置換

SUBSTITUTE関数は、特定の文字列を別の文字列に置換する際にも非常に便利です。例えば、商品コードのプレフィックスを削除したり、特定の記号を別の記号に置き換えたりする際に使用できます。

1. 不要なプレフィックスの削除
   =SUBSTITUTE(A1,"PRE_","")
   A1セルの文字列の先頭にある「PRE_」という文字列を削除します。
2. 記号の置換
   =SUBSTITUTE(A1,"/","-")
   A1セルの「/」を「-」に置換します。

これらの関数を組み合わせることで、Excelでのデータクリーニング作業の幅が大きく広がります。

まとめ

ExcelでCR+LFとLFの改行コードが混在する問題は、CLEAN関数とSUBSTITUTE関数を組み合わせることで解決できます。本記事では、改行コードをLFまたはCR+LFに統一する具体的な数式と手順を解説しました。また、大量データに対応するためのPower Queryを用いた方法も紹介しました。

これらの手法を習得することで、データの表示崩れや後続処理でのエラーを防ぎ、Excelデータをより正確かつ効率的に扱うことが可能になります。今後は、これらの関数を応用して、不要なスペースの削除や特定の文字列の置換など、さらなるデータクリーニングに挑戦してみてください。

📊

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

🏆

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