【Excel整形】「スペース削除が面倒…」汚いデータを一発でキレイにする文字列操作3選

【Excel整形】「スペース削除が面倒…」汚いデータを一発でキレイにする文字列操作3選

VLOOKUPでエラーが出ると思ったら、後ろに見えないスペースが入っていた…。そんな経験はありませんか。「汚いデータ」の手修正は、最も生産性の低い作業の一つです。しかし、Excelの文字列操作関数を使えば、何千行あるデータでも一瞬でルール通りに整えることができます。データ分析や集計の前処理として欠かせない、プロ御用達のデータ整形テクニックをご紹介します（最新のExcel（Microsoft 365 / 2021以降）の場合）。

1：余計な空白を削除「TRIM」

文字列から余分なスペースを削除するのがTRIM関数です。単語間のスペースは1つだけ残し、文字列の先頭や末尾にある不要なスペースはすべて削除してくれます。Webからコピーしたデータや、システムからCSVで落としたデータには見えない空白が含まれていることが多く、これが集計ミスの原因になります。TRIM関数を通すだけで、これらを一掃できます。

2：謎の文字化けを消す「CLEAN」

文字列から印刷できない制御文字（改行コードなど）を削除するのがCLEAN関数です。ただし、Webサイトからコピーした際によく混入する「全角スペース」や「不透明な特殊な空白」はCLEANだけでは消せません。実務では =TRIM(CLEAN(SUBSTITUTE(A1, "　", ""))) のように、SUBSTITUTEで全角スペースを消す処理と組み合わせることで、どんなに汚いデータも完璧にクリーンアップできます。

3：文字を自在に置き換える「SUBSTITUTE」

文字列中の指定した文字列を、他の文字列に置き換えて返すのがSUBSTITUTE関数です。例えば、電話番号のハイフンを一括で削除したり、表記ゆれ（「(株)」を「株式会社」になど）を統一したりする際に便利です。何番目の文字だけを置換するかという指定も可能で、非常に柔軟なデータ加工が可能です。オートフィルの置換機能とは異なり、元データを残したまま修正結果を表示できるのが強みです。 

データの汚れは、仕事の遅れに直結します。整形関数セットを使いこなし、どんなデータが来ても動じない「処理の達人」を目指しましょう。

＜出典＞

Excel 関数 (機能別)（Microsoft）

【Excel神業】リボンメニューの奥までキーボードで！タブとグループを自在に巡る3選

イチオシ編集部　ガジェット部

この記事を読む

※記事内における情報は原稿執筆時のものです。