Aさん 手入力していくデータで余計な改行が入ってしまう場合、いちいち置換などで直すのが面倒です・・・。
こうした場合、もっと楽に修正することができないですかね？
森田 その場合は、関数の「CLEAN」を活用すると良いですよ！
では、CLEANの使い方について解説していきますね。
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要については以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
元データの文字列から余計な改行コードを削除し、表記を統一したい場合は「CLEAN」が有効
手入力するデータは「表記ゆれ」が起きてしまうもの。
表記ゆれとは、人から見れば実質同じデータなのに、PC（Excel）から見ると別の表記になってしまっていることを指します。
その一例が、改行コードの有無です。
改行コードとは、改行の指示を出しているコード（文字）のことです。
このコードは目には見えませんが、このコードの次の文字列が改行されます。
改行コードの有無での表記ゆれがあると、これをキーにした場合の検索や集計ができない、あるいは結果が狂うというリスクがあります。
よって、事前に余計な改行コードは削除し、表記を統一することが必要です。
こんな場合、関数の「CLEAN」を使うことで、文字列内の改行コード等の印刷できない文字を自動的に削除することが可能となります。
ちなみに、CLEANは「クリーン」と呼びます。
よって、余計な改行コードを削除し、表記を統一したい場合はCLEANを使用していきましょう。
余計なスペースを削除したい場合は「TRIM」を使用してください（詳細は以下の記事を参照）。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要については以下の記事をご参照 …
CLEANの構文
CLEANの構文は以下の通りです。
=CLEAN(文字列)
印刷できない文字を文字列から削除します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 文字列 | ○ | 文字列 | 削除対象のセル（値が文字列）を指定します。 |
文字列に改行コード等の印刷できない文字が含まれない場合は、引数「文字列」に指定した値がそのまま表示されます。
【参考】CLEANは「文字列操作関数」
あくまで参考情報となりますが、CLEANはリボン「数式」タブの関数ライブラリの「文字列操作」に分類されています。
実際にCLEANを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
【参考】「印刷できない文字」とは
「印刷できない文字」とは、7ビットのASCIIコードの印刷できない32文字（値0~31）のことです。
基本的に手入力前提のデータの場合、改行（値10）以外は含まれません。
考えられるとしたら、他システムからエクスポートされたデータの場合、改行以外の印刷できない文字が混入する可能性がわずかながらある程度です。
そのため、実務ではCLEANは改行コードを取り除くものだと思っておけば問題ないでしょう。
7ビットのASCIIコードの印刷できない32文字（値0~31）の詳細を知りたい方は、以下をご参照ください（ASCII制御文字の「10進法」列で値0~31を識別できます）。
CLEANの使用結果イメージ
CLEANで余計な改行コードを削除したイメージは以下の通りです。
今回は「担当者名」列の余計な改行コードを削除しました。
なお、CLEANは原則一つの関数で一つのセルのみが変換対象です。
ベースの数式をセットしたら、他のセルへペーストしましょう。
CLEANの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はI2セル - 「=cl」と入力
- サジェストから「CLEAN」を選択し、「Tab」キーで確定
- 削除対象のセルを選択
※今回はH2セル - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はI3~I21セルへペースト
手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑥は不要（全レコードへ数式が自動的にコピーされる）。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はI2セル - 「=cl」と入力
- サジェストから「CLEAN」を選択し、「Tab」キーで確定
- 削除対象のセルを選択
※今回はH2セル - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はI3~I21セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
CLEANは表記ゆれを修正する際に役立つ関数のうちの一つです。
定期的に余計な改行コードを削除する機会があるなら、ぜひ覚えておいた方が良いですね。
なお、CLEAN以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 CLEANとセットで覚えておいた方が良い関数は、余計なスペースを削除するTRIMです。
なお、印刷できない文字が含まれているかどうかを検知するため、文字数をカウントできるLENもセットで覚えておくこともおすすめします。
