業務用の共有シートやテンプレートでは、元データを直接書き換える置換操作は敬遠される。データ監査や再計算の観点から、数式を用いた非破壊的なデータクレンジングが鉄則だ。ここで主役となるのが「CLEAN関数」と「SUBSTITUTE関数」である。
CLEAN関数は、ワークシート上の印刷不可能な制御文字（ASCII値0〜31）をすべて削除する目的で設計された関数だ。構文は極めて明快で、=CLEAN(A2)と記述するだけでセル内のLFコードが消去される。しかし、ここに大きな落とし穴が存在する。改行コードが単に「蒸発」するため、前後に入力されていた文字列が直接連結されてしまうのだ。例えば「John」と「Smith」がセル内で改行されていた場合、出力結果は「JohnSmith」となり、姓名の判別不能なデータ汚染を引き起こす。
この欠陥を克服するのがSUBSTITUTE関数とCHAR(10)の組み合わせだ。文字コード10番を指定して任意の文字に置き換えるため、以下のように記述する。
=SUBSTITUTE(A2, CHAR(10), " ")
この数式を適用すれば、改行位置に半角スペースが正確に挿入される。さらに、改行を読点（、）に置き換えて文章としての体裁を保つなど、出力の制御性を完全に掌握できる。WebからコピーしたデータなどでCR（復帰コード、CHAR(13)）が混在している疑いがある場合は、=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), ""), CHAR(10), " ")とネストさせることで、あらゆるOS由来の改行コードを無力化できる。
Tags:
