連載
» 2022年01月17日 05時00分 公開

構造化Excelテク:外部から取り込んだデータの不要なタブや改行などをまるっと削除するTech TIPS

Webサイトなどの外部ソースから取り込んだデータには、Excelで印刷できない文字が含まれていることがある。こうした文字は、データ処理の邪魔になることがあるので削除しよう。ただ、置換などでは簡単に削除できないので、CLEAN関数を使って削除する手順を紹介する。

[小林章彦,デジタルアドバンテージ]

この記事は会員限定です。会員登録(無料)すると全てご覧いただけます。

「Tech TIPS」のインデックス

連載目次

対象:Excel 2016/2019/2021/365


Excelの表に含まれるタブや改行を削除しよう Excelの表に含まれるタブや改行を削除しよう
セル内に余計なタブや改行があると、検索や並べ替えが正しく行えない。そこで不要なこうした文字を削除しよう。

 Tech TIPS「構造化Excelテク:整形のために挿入されたセル内の余計な空白や改行を削除する」でも解説しているように、「Microsoft Excel(エクセル)」で表を作成する場合、セル内に余計な空白や改行があると、検索や並べ替えが正しく行えなくなってしまう。

 また、外部ソースから入手したデータには、Excelでは印刷されない文字が含まれてしまうことがある。こうした文字は、Excelに読み込む前に処理して削除してしまうのが望ましい。だが、それにはプログラミングが必要になるなどハードルが高い。

 そこで、Excel内で印刷されない文字を削除する方法を紹介しよう。

セル内の改行を置換で削除する

 セル内の改行のみを削除したい場合は、置換機能を使うのが手っ取り早い。[ホーム]タブの[編集]グループにある[検索と選択]をクリックし、メニューで[置換]を選択する([Ctrl]+[H]キーを押してもよい)。特定のセル範囲を対象に改行を削除したい場合は、対象となる範囲を選択してから[置換]を選択すること。

 [検索と置換]ダイアログの[置換]タブが開くので、「検索する文字列」に[Ctrl]+[J]キーを入力、「置換後の文字列」には何も入力しない状態で[すべて置換]ボタンをクリックすればよい。「検索する文字列」に[Enter]キー(改行)を入力しても、改行として認識されないので注意してほしい。

セル内の改行を置換で削除する(1) セル内の改行を置換で削除する(1)
[ホーム]タブの[編集]グループにある[検索と選択]をクリックし、メニューで[置換]を選択する。[検索と置換]ダイアログの[置換]タブが開くので、「検索する文字列」に[Ctrl]+[J]キーを入力する。「置換後の文字列」には何も入力しない状態で[すべて置換]ボタンをクリックすればよい。
セル内の改行を置換で削除する(2) セル内の改行を置換で削除する(2)
シート内の改行が削除できる。ここでは、2つの改行が削除されている。

 ただし、1つのセルに改行で複数のデータが含まれているような場合は、単純に削除せず、「,(コンマ)」などに変換し、[区切り位置指定ウィザード]を使ってデータを分割するとよい。データを分割する方法は、Tech TIPS「構造化Excelテク:複数のデータが含まれたセルを1セル1データに分割する」を参照してほしい。

タブなどの他の印刷されない文字も含めて削除する

 セルに「タブ」や印刷されない文字が含まれている場合、[検索と置換]ダイアログでは削除できない(「検索する文字列」に文字が指定できないため)。この場合、CLEAN関数を使うのがよい。

 CLEAN関数では、文字コード「0x00」から「0x1F」の印刷できない文字の削除が行える(タブや改行、エスケープなどが含まれている)。改行も含まれているので、CLEAN関数を使えば、不要な改行やタブなどを一度に処理できる。

 まず、印刷されない文字を含む表(セルの範囲)に名前を付けて、一括で指定できるようにしよう。それには、表内のセル範囲を選択して、[名前ボックス](通常はセル番号が表示されている、表左上のボックス)に付けたい名前を入力して[Enter]キーを押す(セル範囲に名前を付ける方法は、Tech TIPS「Excelの『名前の定義』でセルの範囲に名前を付ける」参照のこと)。例えば、「table1」などと付けておく。

 次に、印刷されない文字を削除した表を作成するためのシートを追加する。それには、[シート]タブで[+]アイコンをクリックする。

 作成したシートの「A1」セルに「=CLREAN(table1)」と入力すると、元の表から印刷されない文字が削除された状態の表が作成できる。

CLREAN関数で印刷できない文字を削除する(1) CLREAN関数で印刷できない文字を削除する(1)
セル範囲を選択し、「名前の定義」機能で「table1」と名前を付ける。次に、[シート]タブの[+]アイコンをクリックして、新しいシートを開く。
CLREAN関数で印刷できない文字を削除する(2) CLREAN関数で印刷できない文字を削除する(2)
新しいシートの[A1]セルに「=CLREAN(table1)」と入力して[Enter]キーを押す。
CLREAN関数で印刷できない文字を削除する(3) CLREAN関数で印刷できない文字を削除する(3)
CLEAN関数で印刷されない文字が削除された表が、追加したシートに作成される。

数式を値に変換する

 ただし、このままでは各セルが関数の入った「数式」の状態なので、これを「値」に変換しよう(日付もシリアル値になってしまう。この修正は後述)。それには、印刷されない文字を削除した表を選択し、[Ctrl]+[C]キーでクリップボードにコピーする。選択したセル範囲で右クリックし、[形式を選択して貼り付け]を選択する。[形式を選択して貼り付け]ダイアログが表示されるので、「値」を選択して、[OK]ボタンをクリックする。これで、数式が値に変換できる。

数式を値に変換する(1) 数式を値に変換する(1)
印刷されない文字を削除した表を選択し、[Ctrl]+[C]キーでクリップボードにコピーする。選択したセル範囲で右クリックし、[形式を選択して貼り付け]を選択する。
数式を値に変換する(2) 数式を値に変換する(2)
[形式を選択して貼り付け]ダイアログが表示されるので、「値」を選択して、[OK]ボタンをクリックする。
数式を値に変換する(3) 数式を値に変換する(3)
数式が値に変換できる。ただし、数値や日付が文字列として保存されるため、セルに警告マークが表示される。

「文字」を「数値」に変換する

 ただ、元の表が「数値」や「日付」だったセルは、CLEAN関数を使うことで「文字」として保存されてしまう。

Copyright© Digital Advantage Corp. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。