元データをそのまま残しつつ、隣の列に綺麗に整形されたテキストを出力したい場面では、ワークシート関数を用いるのが定石です。主に使われるのは「CLEAN関数」と「SUBSTITUTE関数」の2つですが、両者には明確な役割の違いが存在します。
| 手法・関数名 | 代表的な記述例 | メリット・適用シーン |
|---|---|---|
| 置換（Ctrl+J） | 検索窓で[Ctrl+J] ➔ 置換窓は空 | 数式セルを増やさず、元データをその場で瞬時に一括変更できる。 |
| CLEAN関数 | =CLEAN(A2) | 印刷不能な制御文字を全て除去。改行を一律で消去したい時に最速。 |
| SUBSTITUTE関数 | =SUBSTITUTE(A2, CHAR(10), " ") | 改行を空白や読点に置き換え可能。単語同士のくっつきを防止。 |
CLEAN関数は、テキストに含まれる「印刷できない制御文字（ASCIIコード0〜31）」をまとめて除去するための関数です。セル「A2」に改行混じりの文字列がある場合、別セルに=CLEAN(A2)と記述するだけで改行が綺麗に削ぎ落とされます。ただし、住所データの「東京都千代田区（改行）大手町」をCLEAN関数で処理すると「東京都千代田区大手町」と隙間なく結合されますが、英語の氏名や商品コードなどでは文字列同士が意図せず直結してしまうリスクがあります。
そこで真価を発揮するのがSUBSTITUTE関数とCHAR(10)の組み合わせです。CHAR(10)はエクセルの数式上でセル内改行コードを指定する記号です。例えば、改行を半角スペースに変換したい場合は=SUBSTITUTE(A2, CHAR(10), " ")、読点にしたい場合は=SUBSTITUTE(A2, CHAR(10), "、")と記述します。これにより、可読性を損なうことなく柔軟なデータクレンジングが実現します。
