Excelデータのクリーンアップ:不要なスペースを素早く簡単に削除する方法

Microsoft Excelにデータを取り込む際や、複数人で同じシートを編集していると、データが乱雑になりがちです。セルの内容の先頭、末尾、あるいは途中に余分なスペースが紛れ込んでしまうことも珍しくありません。

この記事では、Excelのセルから不要なスペースを削除してデータをきれいに整える方法をご紹介します。複数の方法があるので、状況に応じて最適なものを選んで活用できます。

TRIM関数で先頭・末尾・余分なスペースを削除する

Excelでスペースを削除する定番の方法が、TRIM関数を使うやり方です。この関数なら、たった1つの数式でテキストの先頭・末尾、そして途中の余分なスペースをまとめて除去できます。

数式の構文は「TRIM(文字列)」です。引数にはセル参照または実際のテキストを指定します。ただし、この関数が削除するのは余分なスペースのみで、単語間の半角スペース1つ分は残る点、また結果は別のセルに表示される点に注意してください。

まず、数式を入力して結果を表示させたいセルを選択します。ここでは例として、A1セルのテキストをトリムするためにB1セルを選びます。

以下の数式を入力し、セル参照はご自身の環境に合わせて変更してください。この例では次のように入力します。

=TRIM(A1)

Enterキーを押すと数式が適用され、結果が表示されます。

ご覧のとおり、TRIM関数によってA1セルのテキストの先頭・途中・末尾にあった余分なスペースがすべて削除されました。

SUBSTITUTE関数でスペースを置き換える

Excelで余分なスペースを除去するもう一つの方法が、SUBSTITUTE関数の活用です。この関数では、既存のスペースを別の数のスペースや空の文字列に置き換えられます。ただし、こちらも結果は別の場所に表示されます。

構文は「SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)」です。必須の引数は最初の3つだけで、第1引数にはセル参照または実際のテキストを指定できます。特定の出現箇所だけを置き換えたい場合は、任意で4つ目の引数を指定します。

数式を入力して結果を表示させるセルを選択します。例として、A1セルの内容のスペースを置き換えるためにB1セルを選びます。

この数式では、連続する2つのスペースを1つのスペースに置き換えます。1つ目の引用符の中に半角スペースを2つ、2つ目の引用符の中に半角スペースを1つ入力してください。

=SUBSTITUTE(A1," "," ")

Enterキーを押して数式を適用すると、更新された内容が表示されます。

スペースを空の文字列に置き換えて完全に削除したい場合は、2つ目の引用符を空のままにするだけです。

=SUBSTITUTE(A1," ","")

検索と置換機能でスペースを削除する

上記の関数は余分なスペースの除去にとても有効ですが、更新後の内容を別のセルに表示したくない場合もあるでしょう。そんなときは、Excelの「検索と置換」機能を使うのがおすすめです。

検索と置換を使えば、削除・置換したいスペースの数を指定できるほか、選択したセル範囲だけに適用することも、シート全体に適用することも可能です。しかも、結果が元のデータのセルに直接反映されるのが大きなメリットです。

特定のセルや範囲だけに変更を適用したい場合は、あらかじめその範囲を選択しておいてください。選択しないまま実行すると、スプレッドシート全体のスペースが検索・置換されます。

1. 「ホーム」タブを開き、「編集」グループの「検索と選択」メニューから「置換」を選択します。
   

1. ダイアログボックスが開いたら、「検索する文字列」フィールドに削除したいスペースの数を入力します。
   

1. 次に、「置換後の文字列」フィールドに置き換えるスペースの数を入力します。スペースを完全に削除したい場合は、下図のように「置換後の文字列」を空欄のままにします。
   

1. 以下のいずれかを選択します。
   
2. セルを確認せずにすべてのスペースを一括置換する場合は、「すべて置換」を選択します。
   
3. 置換前に該当セルを確認したい場合は、まず「すべて検索」を選択し、準備ができたら「すべて置換」をクリックします。
   

1. 置換された件数が表示されます。「検索と置換」ボックスの「閉じる」をクリックすれば、指定したスペースが削除されたデータを確認できます。
   

Power Queryでスペースを削除する

Excelでスペースを削除するさらなる方法がPower Queryです。すでにPower Queryを使ってデータのインポートや変換を行っている場合や、先頭と末尾のスペースだけを削除したい場合に特に便利です。

注意:前述のTRIM関数とは異なり、このトリム機能は先頭と末尾のスペースのみを削除し、途中のスペースは削除されません。

まだPower Queryを開いていない場合は、データを選択して「データ」タブに移動します。

1. 「データの取得と変換」グループの「テーブル/範囲から」を選択します。
   

1. ポップアップボックスでテーブルのデータ範囲を確認し、データに見出しが含まれている場合は必要に応じてチェックボックスをオンにして、「OK」を選択します。
   

1. データがPower Queryに読み込まれたら、複数の列がある場合は更新対象の列を選択します。Ctrlキーを押しながらクリックすると、複数の列をまとめて選択できます。
   

1. 「変換」タブを開き、「テキスト列」グループの「形式」メニューから「トリミング」を選択します。
   

1. 先頭と末尾のスペースがすべて削除され、データが更新されます。
   

1. データをすぐに使用するには、「ホーム」タブを開き、「閉じて読み込む」メニューから以下のいずれかを選択します。
   
2. 閉じて読み込む先…更新されたデータの配置場所を自分で選択できます。
   
3. 閉じて読み込む…既存のブックの新しいワークシートにデータが配置されます。
   

読み込み後は、必要に応じてデータの書式設定や移動、テーブルからセル範囲への変換なども自由に行えます。

Excelには不要なスペースを取り除くさまざまな方法があり、スペースの位置にかかわらず、データを素早く簡単にクリーンアップできます。

Excelのセルからスペースを削除する方法をマスターしたら、次はExcelで重複行を削除する方法もチェックしてみましょう。