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

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

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

連載目次

対象：Excel 2016／2019／2021／365

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

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

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

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

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

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

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

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

▼

セル内の改行を置換で削除する（2）
シート内の改行が削除できる。ここでは、2つの改行が削除されている。

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

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

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

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

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

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

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

CLREAN関数で印刷できない文字を削除する（1）
セル範囲を選択し、「名前の定義」機能で「table1」と名前を付ける。次に、［シート］タブの［＋］アイコンをクリックして、新しいシートを開く。

▼

CLREAN関数で印刷できない文字を削除する（2）
新しいシートの［A1］セルに「=CLREAN(table1)」と入力して［Enter］キーを押す。

▼

CLREAN関数で印刷できない文字を削除する（3）
CLEAN関数で印刷されない文字が削除された表が、追加したシートに作成される。

数式を値に変換する

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

数式を値に変換する（1）
印刷されない文字を削除した表を選択し、［Ctrl］＋［C］キーでクリップボードにコピーする。選択したセル範囲で右クリックし、［形式を選択して貼り付け］を選択する。

▼

数式を値に変換する（2）
［形式を選択して貼り付け］ダイアログが表示されるので、「値」を選択して、［OK］ボタンをクリックする。

▼

数式を値に変換する（3）
数式が値に変換できる。ただし、数値や日付が文字列として保存されるため、セルに警告マークが表示される。

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

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

　そこで、まず「文字」を「数値」に変換しよう。「文字」になってしまったセルの数が少ない場合は、そのセルを選択（複数選択可能）し、セルの左側に表示される警告アイコンをクリックし、表示されたメニューで［数値に変換する］を選択すればよい。

警告アイコンで文字列を数値に変換する
左上に警告マークが表示されたセルを選択して、表示された警告アイコンをクリック、表示されたメニューで［数値に変換する］を選択する。

　ただ、「数値」が「文字」になってしまっているセルの数が多いと、この作業は面倒だ。その場合、空いているセルに「1」を入力し、［Ctrl］＋［C］キーでコピーしておく。表全体を選択した状態で、右クリックメニューの［形式を選択して貼り付け］を選択する。［形式を選択して貼り付け］ダイアログが表示されるので、「乗算」を選択して、［OK］ボタンをクリックすればよい。これで「文字」になっていた値を「数値」に変換できる。その後、不要になった「1」を入力したセルを削除すれば変換は完了だ。

文字を数値に変換する（1）
空いているセルに「1」を入力して、［Ctrl］＋［C］キーでコピーしておき、表全体を選択する。

▼

文字を数値に変換する（2）
右クリックメニューの［形式を選択して貼り付け］を選択する。［形式を選択して貼り付け］ダイアログが表示されるので、「乗算」を選択して、［OK］ボタンをクリックする。

▼

文字を数値に変換する（3）
文字が数値に変換され、セル左上の警告マークが消えるはずだ。後は、最初に入力した「1」を削除する。

セルの書式を元の表からコピーする

　さらに、セルの書式も反映されないので、これも元の表からコピーして反映しよう。セルの書式を反映することで、日付なども正しく表示されるようになる。

　それには、元の表を選択して、［Ctrl］＋［C］キーでクリップボードにコピーする。印刷されない文字を削除した表の左上のセルを選択し、右クリックメニューの［形式を選択して貼り付け］を選択、表示された［形式を選択して貼り付け］ダイアログで「書式」を選択して、［OK］ボタンをクリックすればよい。

　これでセルの書式やけい線などが反映できる。ただし、列幅や行の高さは反映できないので、これは手動で調整する必要がある。

セルの書式を元の表からコピーする（1）
元の表を選択して、［Ctrl］＋［C］キーでクリップボードにコピーする。

▼

セルの書式を元の表からコピーする（2）
印刷されない文字を削除した表の左上のセルを選択し、右クリックメニューの［形式を選択して貼り付け］を選択、表示された［形式を選択して貼り付け］ダイアログで「書式」を選択して、［OK］ボタンをクリックする。

▼

セルの書式を元の表からコピーする（3）
セルの書式がコピーされることで、日付も正しく表示される。