Webサイトや基幹システムからコピペしたデータをExcelに貼り付けたとき、セル内に不要な改行が入ってレイアウトが崩れる経験はありませんか。VLOOKUPの検索条件にしたい列に改行が混じっていて、いくら数式を見直してもヒットしない、というのもよくあるトラブルです。
セル内の改行は「見えない文字」なので、手作業で消そうとすると1セルずつダブルクリックしてBackSpaceを押す羽目になり、件数が多いと現実的ではありません。
この記事では、Excelのセル内改行を一括で削除する3つの方法を、状況別の使い分けまで含めて解説します。「Ctrl+J」を使った検索と置換、SUBSTITUTE関数、CLEAN関数を覚えれば、どんなデータが来ても数秒で片付けられます。
セル内の改行はなぜ問題になるのか
Excelのセル内改行は、入力中に「Alt+Enter」を押すことで挿入される改行コードです。実体はCHAR(10)、つまりラインフィード（LF）と呼ばれる制御文字です。画面上は単なる改行に見えますが、Excelの内部では1文字としてカウントされます。
この見えない1文字が悪さをするのは、主に次のような場面です。
- VLOOKUPやXLOOKUPで検索値が一致しない（末尾に改行が混入）
- 文字列連結したときに意図しない位置で改行が入る
- CSV書き出し時に行がずれて出力される
- フィルターで「同じ値のはず」が別グループに分かれる
WindowsとMacの現行版Excel（Excel 2016以降）では、どちらもセル内改行はCHAR(10)に統一されています。ただし古いMacファイルやUNIX系システムのデータには、CHAR(13)（キャリッジリターン）が混ざっていることもあるので注意が必要です。
文字コードの基本についてはExcelのCHAR関数の使い方で詳しく解説しています。
それでは、3つの一括削除方法を順番に見ていきましょう。
方法1: 検索と置換（Ctrl+H + Ctrl+J）で一括削除する
一番手軽で、覚えてしまえば数秒で終わる方法が「検索と置換」を使うやり方です。Excelの検索ダイアログでは、Ctrl+Jを押すと改行コード（CHAR(10)）を検索文字列として入力できます。
元のデータをそのまま書き換えたいときに最適で、関数も新しい列も不要です。
手順
操作はとてもシンプルです。次の順番で進めてください。
- 改行を削除したい範囲を選択する（列全体でもOK）
- Ctrl+H を押して「検索と置換」ダイアログを開く
- 「検索する文字列」の欄をクリックして、Ctrl+J を押す（見た目は何も入らないが内部に改行コードが入る）
- 「置換後の文字列」は空欄のままにする
- 「すべて置換」をクリックする
Ctrl+Jを押した直後、検索欄には何も表示されません。「本当に入力されたのか？」と不安になりますが、ちゃんと改行コードが入っています。心配な場合は検索欄をクリックしてから矢印キーを押すと、カーソルが微妙に動いて入力済みとわかります。
改行をスペースに変えたい場合
改行を完全に消すのではなく、半角スペースに置き換えたい場合もあります。たとえば「東京都千代田区(改行)永田町1-1-1」という住所を「東京都千代田区 永田町1-1-1」のように一行に整えるケースです。
その場合は手順4の「置換後の文字列」欄に半角スペースを1文字入力してから「すべて置換」を押します。
検索する文字列: Ctrl+J（改行コード）
置換後の文字列: （半角スペース1文字）注意点として、検索欄に一度Ctrl+Jを押すと、Excelを閉じるまでその状態が記憶されます。次の検索でうまく動かないと感じたら、検索欄をクリックしてBackSpaceで一度クリアしてから再入力してください。
方法2: SUBSTITUTE関数で改行を削除する（元データを残したいとき）
元データはそのままにして、別の列に「改行を削除したバージョン」を作りたい場合は、SUBSTITUTE関数を使います。検証ログや原本を残しつつクリーニング結果を確認したいときに便利です。
基本の使い方
SUBSTITUTE関数は「文字列の中の特定の文字を別の文字に置き換える」関数です。改行コードはCHAR(10)で指定します。
=SUBSTITUTE(A1,CHAR(10),"")これでA1セルの改行がすべて削除された文字列が返ります。第3引数を空文字列（””）にしているので、改行が「何もない」に置き換わります。結果として削除されたように見える、という仕組みです。
改行を半角スペースに変換したい場合は、第3引数を変更します。
=SUBSTITUTE(A1,CHAR(10)," ")連続した改行をまとめて1つのスペースにしたい場合は、TRIM関数と組み合わせると余分な空白も整理されます。
=TRIM(SUBSTITUTE(A1,CHAR(10)," "))SUBSTITUTE関数のさらに詳しい使い方はExcelのSUBSTITUTE関数の使い方を参考にしてください。
古いMacファイルに対応する場合
前述のとおり、古いMacや一部のシステムから持ち込んだデータにはCHAR(13)（キャリッジリターン）が含まれていることがあります。CHAR(10)だけを消してもうまくクリーニングできないときは、SUBSTITUTEを入れ子にして両方を処理しましょう。
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),""),CHAR(13),"")内側のSUBSTITUTEでCHAR(10)を消し、外側のSUBSTITUTEでCHAR(13)を消す流れです。WindowsのCRLF（CHAR(13)+CHAR(10)）形式の改行もこれで両方処理できます。
方法3: CLEAN関数で制御文字ごとまとめて消す
外部システムから取得したデータには、改行以外にもタブ文字や非表示の制御文字が紛れ込んでいることがあります。何が混ざっているか分からないけれど、とにかく「印刷できない文字」をまとめて消したい、というときに使うのがCLEAN関数です。
=CLEAN(A1)CLEAN関数は、ASCII文字コードの0〜31番（印刷不可能な制御文字）を一括で削除します。CHAR(10)もCHAR(13)もこの範囲に含まれるので、改行は当然削除されます。
CLEAN関数の挙動や対応範囲についてはExcelのCLEAN関数の使い方で詳しく解説しています。
注意点として、CLEAN関数は「削除」しかできず、スペースへの変換はできません。「改行をスペースに置き換えたい」という用途にはSUBSTITUTE関数を使ってください。
CLEAN+TRIMの組み合わせ
実務でCLEAN関数を使うときは、TRIM関数と組み合わせるのが定番です。
=TRIM(CLEAN(A1))CLEANで制御文字を消したあと、TRIMが前後の余分なスペースを除去します。文字列中の連続スペース（2つ以上）も1つに整えてくれます。
ExcelにインポートしたCSVデータの全列クリーニングなど、「とりあえずキレイにしたい」場面で最も汎用的な数式です。迷ったらこれを最初に試すと、たいていのデータが整います。
3つの方法の使い分け早見表
ここまで紹介した3つの方法を、状況別にまとめると次のようになります。
| 状況 | 推奨される方法 | 理由 |
|---|---|---|
| 元データを書き換えてOK | 検索と置換（Ctrl+H + Ctrl+J） | 関数不要、最も手早い |
| 元データを残したい | SUBSTITUTE または CLEAN | 別列に結果を出せる |
| 改行だけを正確に削除したい | SUBSTITUTE | 改行コードだけを狙い撃ち |
| 改行を半角スペースに変換したい | SUBSTITUTE | 第3引数で柔軟に変換可能 |
| 複数の制御文字をまとめて除去 | CLEAN | タブや非表示文字も一括処理 |
| インポートデータ全体クリーニング | TRIM(CLEAN()) | スペース整理も同時に行える |
| 古いMacのCHAR(13)が混在 | SUBSTITUTE入れ子 | CHAR(10)とCHAR(13)を両方処理 |
迷ったときの判断基準はシンプルです。「元データを残す必要があるかどうか」「改行以外の制御文字も気になるか」の2点で決めれば大丈夫です。
その場限りの作業なら検索と置換、定期的に更新されるデータなら関数を使って自動処理にしておくと、運用がぐっと楽になります。
CLEANで消えないケースへの対処法
「CLEANもSUBSTITUTEもCHAR(10)もCHAR(13)も試したのに、なぜか改行のような空白が消えない」という状況に遭遇することがあります。
これはたいてい、CHAR(160)（ノーブレークスペース、NBSP）が原因です。HTMLの「 」由来の文字で、Webからコピペしたデータによく紛れ込みます。見た目はただのスペースですが、通常のスペース（CHAR(32)）とは別の文字コードなので、TRIMでもCLEANでも除去できません。
まず、本当にCHAR(160)が原因かを確認してみましょう。次の数式で先頭文字のコードを調べられます。
=CODE(MID(A1,1,1))これが160を返したら、原因はノーブレークスペースで確定です。MID関数の第2引数（開始位置）を変えれば、文字列の途中もチェックできます。
CHAR(160)を削除するには、CLEAN関数の結果にさらにSUBSTITUTEをかけるのが定番です。
=SUBSTITUTE(CLEAN(A1),CHAR(160),"")CHAR(160)を半角スペースに変換したい場合は、第3引数をスペースにします。
=SUBSTITUTE(CLEAN(A1),CHAR(160)," ")TRIMとも組み合わせれば、ほぼあらゆる「見えない文字」を整理した、完全にきれいな文字列が手に入ります。
=TRIM(SUBSTITUTE(CLEAN(A1),CHAR(160)," "))データクリーニングの最終形として覚えておくと、Webデータや海外システムからのインポートでハマることがなくなります。
まとめ
Excelのセル内改行を一括削除する3つの方法と、それぞれの使い分けを紹介してきました。最後にポイントを整理しておきます。
- 改行の正体はCHAR(10)。Alt+Enterで入力される見えない1文字
- 元データを書き換えてOKなら「検索と置換（Ctrl+H → Ctrl+J）」が最速
- 元データを残したいなら「SUBSTITUTE(A1,CHAR(10),””)」を使う
- 制御文字をまとめて消したいなら「CLEAN(A1)」、定番は「TRIM(CLEAN(A1))」
- それでも消えないときはCHAR(160)を疑い、CODE関数で確認
- 古いMacデータ対策にはCHAR(13)も同時に処理する
データクリーニングは地味な作業ですが、ここで時間を取られると後工程のすべてが遅れます。今回の3つの方法をパターンとして覚えておけば、「見えない改行」に振り回されることはなくなるはずです。
普段の作業で改行混入に困ったときは、まずTRIM(CLEAN())を試して、それでもダメならCHAR(160)を疑う、という順番でチェックしてみてください。
