名前や商品名の前後に、余計なスペースが入っていて、VLOOKUP関数でうまく検索できない。同じ名前なのに、別のデータとして集計される。そんな原因の多くは、目に見えにくい「スペース」です。
この記事では、エクセルでスペースを削除する3つの方法（TRIM関数、置換、SUBSTITUTE関数）と、それぞれの使い分け、削除できないときの原因を、図解で説明します。
スペースが残っていると起こる困りごと
- VLOOKUP関数や、COUNTIF関数で、同じ名前なのに、一致しない
- 重複の削除や、フィルターで、別々の項目として扱われる
- 並べ替えたときに、スペースのあるデータが、先頭にまとまる
- 文字数が、見た目と合わない
3つの方法の使い分け
| 方法 | 削除されるスペース | 向いている場面 |
|---|---|---|
| TRIM関数 | 前後のスペースと、単語間の余分なスペース（1つは残す） | 名前や住所の、余計なスペースをそろえたい |
| 置換（Ctrl＋H） | 指定したスペースを、すべて削除 | すべてのスペースを、一括で消したい |
| SUBSTITUTE関数 | 指定したスペースを、すべて削除（元データは残る） | 元データを残して、別のセルに結果を出したい |
TRIM関数で、余分なスペースを削除する
=TRIM(文字列)TRIM（トリム）関数は、文字列の前後にあるスペースを削除し、単語の間のスペースが2つ以上ある場合は、1つにまとめます。全角のスペースも、半角のスペースも、対象になります。
結果は、関数のセルに表示されます。元のデータを置き換えたいときは、結果をコピーして、元のセルに「値」として貼り付けます。
置換で、スペースを一括削除する
手順1｜スペースを削除したい範囲を選ぶ
手順2｜Ctrl＋H を押して、「検索と置換」ダイアログを開く
手順3｜「検索する文字列」に、スペースを1つ入力する（全角と半角は、別々に行う）
手順4｜「置換後の文字列」は、空欄のままにする
手順5｜「すべて置換」をクリックする
全角のスペースと、半角のスペースは、別の文字として扱われます。両方を消したいときは、それぞれ置換を行います。単語の間のスペースも、すべて消えるので、「田中 太郎」が「田中太郎」になります。
SUBSTITUTE関数で、すべてのスペースを削除する
=SUBSTITUTE(A2," ","")「” “」の部分に、削除したいスペースを入れて、「””」（空の文字）に置き換えます。半角スペースを削除する場合は、「” “」の中に、半角スペースを入力します。全角スペースを消したいときは、中を全角スペースにします。
両方を削除したいときは、SUBSTITUTE関数を重ねて、それぞれを指定します。TRIM関数と組み合わせると、前後と、間の両方を整えられます。
スペースを削除できないときの原因
| 症状 | 原因 | 対処 |
|---|---|---|
| TRIMで、スペースが消えない | スペースに見えて、実は別の文字（ノーブレークスペースなど） | 別のセルに、その文字をコピーして、SUBSTITUTE関数や置換で指定する |
| 置換で、見つからない | 全角と半角が、違う | 全角と半角を、それぞれ指定する |
| 数値の前後のスペースが、消せない | 数値が、文字列になっている | TRIM関数のあとに、VALUE関数で数値に変換する |
| 改行が残る | 改行は、スペースではない | SUBSTITUTE関数で、改行（Ctrl＋Jで入力）を、空に置き換える |
もう一歩進める｜スペースの削除の応用的な使い方
名前の姓と名を、スペースで分ける
「田中 太郎」のように、スペースで区切られている名前は、「データ」タブの「区切り位置」で、スペースを区切り文字にすると、姓と名を、別々の列に分けられます。
スペースを削除した結果を、元のセルに戻す
関数の結果を、そのままコピーして、元のセルに貼り付けると、数式が壊れます。「形式を選択して貼り付け」で「値」を選んで貼り付けると、結果の文字だけが、元のセルに入ります。
スペースが、いくつ入っているかを数える
「=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))」と入力すると、セルの中の、半角スペースの個数を数えられます。データをきれいにする前に、問題の大きさを確認したいときに使えます。
スペース削除のよくある質問
データの量が多くても、TRIM関数を使えますか？
使えます。1つのセルに、TRIM関数を入力して、下へコピーすれば、何千行でも、一度に処理できます。元のデータは、別の列に残るので、安心して試せます。
TRIM関数は、日本語の文字列にも使えますか？
使えます。全角のスペースも、半角のスペースも、対象になります。単語の間のスペースは、1つ残るため、日本語の名前などでは、残った1つのスペースが気になる場合は、置換やSUBSTITUTE関数で、取り除きます。
名簿や住所録を整えるときの順番
名簿や住所録のデータを整えるときは、次の順番で行うと、トラブルが起きにくくなります。
- 元のデータを、別のシートにコピーしておく
- TRIM関数で、前後と、間の余分なスペースをそろえる
- 結果を「値」として貼り付けて、元の列に戻す
- 全角と半角が混ざっているときは、そろえてから、重複チェックをする
この順番で整えておくと、VLOOKUP関数や、重複の削除、ピボットテーブルの結果が、安定します。
まとめ
- 前後や、余分なスペースだけを整えるなら、TRIM関数
- すべてのスペースを、一括で消すなら、置換（Ctrl＋H）
- 元データを残して、すべて消すなら、SUBSTITUTE関数
- 全角と半角のスペースは、別々に指定する
- 消えないときは、別の文字や、改行が入っていないかを確認する
コメント