Excelで氏名、商品名、住所、コードなどを扱っていると、見た目では気付きにくい空白が混ざり、検索や照合、並べ替えがうまくいかないことがあります。
手入力された文字列には先頭や末尾のスペースが入りやすく、Webサイトや基幹システムから貼り付けたデータには全角空白や特殊な空白文字が含まれる場合もあります。
空白を削除する作業では、すべてのスペースを消すのか、余分なスペースだけを整えるのかを先に決めることが重要です。
よく使う方法は次の3つです。
・TRIM関数で先頭、末尾、連続した半角スペースを整える方法
・SUBSTITUTE関数で指定した空白を完全に削除する方法
・置換機能で範囲内の空白をまとめて消す方法
文字列の用途に合う方法を選べば、データの品質を保ちながら効率よく整形できます。
ここでは、半角スペース、全角スペース、改行、見えない空白への対処も含めて確認していきましょう。
エクセルで空白を削除する方法1【SUBSTITUTE関数による完全削除】
| A列 元データ | B列 結果 |
|---|---|
| 東京 営業部 | 東京営業部 |
| AB 123 45 | AB12345 |
それではまず、文字列の途中にある空白も含めて完全に消すSUBSTITUTE関数について解説していきます。
半角スペースをすべて削除する数式です。
=SUBSTITUTE(A2,” “,””)
この数式では、A2セル内の半角スペースを空の文字列へ置き換えます。
半角スペースをすべて消す数式
1行目が見出しで、A列に元データが入っている場合は、B2セルへSUBSTITUTE関数を入力します。
入力する式は、=SUBSTITUTE(A2,” “,””)です。
第1引数のA2は処理対象のセル、第2引数の半角スペースは探す文字、第3引数の空の引用符は置き換え後の文字を表します。
第3引数を空欄のように指定すると、見つかったスペースを削除できます。
たとえばA2セルが「AB 123 45」であれば、B2セルには「AB12345」と表示されます。
数式を入力したら、B2セル右下のフィルハンドルを下方向へドラッグするか、ダブルクリックして下の行にもコピーしましょう。
セル内のすべての半角スペースが対象になるため、商品コード、電話番号、会員番号のように空白が不要な項目に向く方法です。
【操作のポイント】氏名や部署名のように単語間の空白を残したいデータには、次に紹介するTRIM関数のほうが適しています。
全角スペースを削除する数式
日本語データでは、半角スペースではなく全角スペースが混ざっているケースも少なくありません。
全角スペースだけを削除する場合は、=SUBSTITUTE(A2,” ”,””)と入力します。
式の中央にある空白は、キーボードで入力した全角スペースです。
見た目だけでは半角と全角を判別しにくいため、削除できないと感じたときは数式内の検索文字を全角スペースへ変更することが大切です。
全角と半角の両方を消す式です。
=SUBSTITUTE(SUBSTITUTE(A2,” “,””),” ”,””)
内側のSUBSTITUTE関数が半角スペースを消し、外側のSUBSTITUTE関数が全角スペースを消します。
住所や文章から空白を完全に消すと読みにくくなる場合があるため、利用目的を確認してから適用しましょう。
【操作のポイント】全角スペースは式の引用符の間へ直接入力し、コピー元の文字列から貼り付けると入力ミスを減らせます。
数式の結果を値へ変換する手順
SUBSTITUTE関数で作成したB列は、A列を参照する数式の結果です。
元データを置き換えたい場合は、B列の結果をコピーし、貼り付け先で値として貼り付けを実行します。
値として貼り付けた後なら、元のA列や数式の入ったB列を整理しても、空白を除去した文字列は残ります。
数式をそのまま貼り付けると参照先が変わることがあるため、確定後は値貼り付けが安心です。
元データを残したいときは、別シートや別列で変換してから確認する運用が便利です。
特に顧客リストや管理番号は、一度削除した空白を元へ戻すことが難しい場合があります。
【操作のポイント】変換前の列を残したまま結果を確認し、問題がないことを確かめてから値貼り付けを行いましょう。
余分な空白を整えるTRIM関数
| A列 元データ | B列 TRIM後 |
|---|---|
| 田中 太郎 | 田中 太郎 |
| 営業 企画部 | 営業 企画部 |
続いては、単語の区切りを残しながら不要な半角スペースを整えるTRIM関数を確認していきます。
先頭と末尾の空白を削除し、連続する半角スペースを1つにする数式です。
=TRIM(A2)
TRIM関数で削除される空白
TRIM関数は、文字列の先頭と末尾にある半角スペースを削除します。
さらに、単語の間に半角スペースが複数続いている場合は、1つの半角スペースへ整えます。
「 田中 太郎 」という文字列なら、結果は「田中 太郎」です。
姓と名の間の区切りを残したい場合、すべてを削除するSUBSTITUTE関数よりTRIM関数が適しています。
Webフォームからコピーした氏名、部署名、会社名、メールアドレス周辺の余分な空白を整える場面で役立ちます。
ただし、TRIM関数は原則として半角スペースを対象にするため、全角スペースが原因の場合は別の処理が必要です。
【操作のポイント】途中のスペースを完全に消すのではなく、読みやすい1つの区切りとして残すかを確認しましょう。
全角スペースを含む文字列の整形
全角スペースを含む文字列をTRIM関数だけで処理しても、期待通りに整わないことがあります。
その場合は、まずSUBSTITUTE関数で全角スペースを半角スペースへ変換し、その結果をTRIM関数で整えます。
全角スペースを半角へ置き換えてから余分な空白を整える数式です。
=TRIM(SUBSTITUTE(A2,” ”,” “))
この式では、内側のSUBSTITUTE関数が全角スペースを半角スペースへ変換します。
外側のTRIM関数が、変換後に生じた先頭、末尾、連続部分の半角スペースを整理します。
日本語の氏名や住所で表記ゆれを減らしたいときに使いやすい組み合わせです。
ただし住所では、建物名や部屋番号との間隔に独自のルールがある会社もあります。
【操作のポイント】大量の住所データへ適用する前に、数件の結果を見て社内の表記ルールに合うか確認してください。
TRIM関数で整わない場合の確認
TRIM関数を使っても空白が残るなら、セルに含まれている文字が通常の半角スペースではない可能性があります。
代表例は、Webページからコピーしたときに混ざるノーブレークスペースです。
ノーブレークスペースは通常の空白と似ていますが、文字コードが異なるため、TRIM関数だけでは削除されないことがあります。
ノーブレークスペースを半角スペースへ変換して整える数式です。
=TRIM(SUBSTITUTE(A2,CHAR(160),” “))
CHAR(160)は、ノーブレークスペースを指定するための表現です。
コピー元がWebサイトやPDFの場合は、見えない特殊な空白を疑うと解決しやすくなります。
数式で処理しきれない場合は、次の置換機能やCLEAN関数も組み合わせて確認しましょう。
【操作のポイント】削除できない空白は種類が違うことが多いため、同じ式を繰り返す前にコピー元を確認することが近道です。
検索と置換によるスペースの一括削除
| A列 変換前 | 置換後のイメージ |
|---|---|
| 100 200 300 | 100200300 |
| XY 789 | XY789 |
続いては、数式を使わずに選択範囲内の空白をまとめて消せる検索と置換の機能を確認していきます。
置換は元のセル内容を書き換える操作なので、作業前にファイルを保存するか、対象範囲をコピーしておくと安心です。
半角スペースを置換で削除する手順
まず、空白を消したいセル範囲を選択します。
範囲を選択しない場合は、アクティブなシート全体が対象になることがあります。
次に、CtrlキーとHキーを同時に押して検索と置換の画面を開きます。
検索する文字列には半角スペースを1つ入力し、置換後の文字列は何も入力しません。
すべて置換を選ぶと、対象範囲の半角スペースが一括で削除されます。
置換後の文字列を空欄にすることが、文字を削除するための設定です。
処理件数が表示されたら、想定より多くのセルが変更されていないか確認してください。
【操作のポイント】最初はすべて置換ではなく、次を検索で対象を確認してから実行すると誤操作を防げます。
全角スペースを置換で削除する手順
全角スペースを消す手順も、基本的には半角スペースの場合と同じです。
検索する文字列の欄へ全角スペースを1つ入力し、置換後の文字列を空欄にします。
半角スペースを入力しても全角スペースは見つからないため、入力モードを確認することが必要です。
半角と全角を両方削除したい場合は、それぞれ別に置換を実行します。
画面上で空白の幅が広く見える文字列をコピーし、検索する文字列の欄へ貼り付ける方法も有効です。
置換の直後に結果が読みにくくなったときは、CtrlキーとZキーで元に戻せます。
【操作のポイント】半角用と全角用の置換を分けることで、どちらの空白が混ざっていたかも確認できます。
置換を使う際の注意点
検索と置換は便利ですが、セル内のすべての該当文字へ直接反映されます。
たとえば「東京都 千代田区」のように単語間の空白が意味を持つ文字列では、完全削除によって読みやすさが損なわれるかもしれません。
数字の桁区切りとして入力された空白や、氏名の区切りも同様です。
置換は元に戻せるうちに結果を確認し、保存後に問題へ気付かないよう注意しましょう。
列を限定して実行する、別シートへ複製してから実行するなど、作業対象を狭める工夫が役立ちます。
空白を残すべきデータが混在する場合は、数式で結果を確認できる方法のほうが適しています。
【操作のポイント】置換を実行する前に対象列を明確に選択し、元データを残す運用を基本にしましょう。
改行と見えない文字の削除
| A列 変換前 | B列 整形後 |
|---|---|
| 東京 営業部 |
東京営業部 |
| 商品名＋見えない制御文字 | 商品名 |
続いては、改行や印刷できない文字が原因で文字列が整わない場合の処理を確認していきます。
この見出しでは、セル内改行を削除する操作画面のイメージもあわせて確認します。
セル内改行を数式で削除する方法
セル内の改行は、AltキーとEnterキーで入力されることが多い文字です。
見た目では行が分かれているだけでも、照合やCSV出力では意図しない不具合につながる場合があります。
セル内改行を削除する数式です。
=SUBSTITUTE(A2,CHAR(10),””)
CHAR(10)は、Windows版Excelで一般的にセル内改行として扱われる改行コードです。
改行の位置へ半角スペースを残したい場合は、最後の空の引用符を半角スペースに変更します。
改行を半角スペースへ置き換える数式です。
=SUBSTITUTE(A2,CHAR(10),” “)
単語が連結して困る場合は削除ではなく、半角スペースへの置換が安全です。
変換後にTRIM関数を重ねれば、余分な空白も整えられます。
【操作のポイント】改行を消した後の単語のつながりを確認し、必要なら空白へ置き換えましょう。
CLEAN関数による制御文字の除去
CLEAN関数は、文字列に含まれる印刷できない制御文字を削除するための関数です。
外部システム、テキストファイル、コピーしたデータでは、画面上では見えない制御文字が入り込むことがあります。
印刷できない文字を削除する基本の数式です。
=CLEAN(A2)
空白と制御文字が同時に含まれる場合は、CLEAN関数とTRIM関数を組み合わせると整形しやすくなります。
制御文字を消してから余分な空白を整える数式です。
=TRIM(CLEAN(A2))
CLEAN関数は通常のスペースを削除する関数ではないため、空白の処理にはTRIM関数やSUBSTITUTE関数を組み合わせます。
文字数が合わない、VLOOKUP関数やXLOOKUP関数で一致しないときにも、見えない文字が原因となることがあります。
【操作のポイント】見た目が同じなのに一致しない文字列は、CLEAN関数とTRIM関数で別列に整形して比較しましょう。
セル内改行を置換で削除する画面操作
数式を残さずにセル内改行を削除したい場合は、検索と置換を使う方法もあります。
対象範囲を選択してからCtrlキーとHキーを押し、検索する文字列の欄をクリックします。
その状態でCtrlキーとJキーを押すと、欄内には見えませんが改行文字を指定できます。
置換後の文字列を空欄にしてすべて置換を実行すると、選択範囲内のセル内改行を削除できます。
改行は入力欄で見えないため、処理後の件数メッセージとセルの表示を必ず確認してください。
【操作のポイント】置換後の文字列へ半角スペースを入力すれば、改行を削除せず横並びの文字列へ整形できます。
空白削除後のデータ確認
| A列 元データ | B列 整形結果 | C列 確認 |
|---|---|---|
| AB 123 | AB123 | 文字数を比較 |
| 田中 太郎 | 田中 太郎 | 表記を確認 |
続いては、空白を削除した後にデータの欠落や意図しない連結がないかを確認する方法を解説していきます。
LEN関数で文字数を比較する方法
LEN関数は、セル内の文字数を数える関数です。
空白削除の前後で文字数を比較すると、想定通りに処理できたか判断しやすくなります。
A2セルの文字数を数える数式です。
=LEN(A2)
たとえば半角スペースを2つ削除しただけなら、整形後の文字数は元データより2文字少なくなるはずです。
削除数が予想と大きく異なる場合は、空白以外の文字まで変換していないか確認しましょう。
数式結果を別列へ表示しておけば、大量のデータでも異常な行を見つけやすくなります。
【操作のポイント】変換前後の文字数を並べ、極端に差が大きい行だけを重点的に確認すると効率的です。
照合関数で整形結果を活用する方法
空白が混ざっていると、同じ内容に見える文字列でも一致判定が失敗することがあります。
たとえば、商品コードの照合にXLOOKUP関数やVLOOKUP関数を使う場合、途中のスペースが違うだけで検索結果が取得できません。
検索前に両方の表で空白を削除した補助列を作れば、照合の精度を上げられます。
照合用のキーは表記ゆれをなくした補助列で統一すると、検索式を安定させやすくなります。
特に複数人が入力する台帳では、入力規則だけでは防ぎきれない余分な空白が発生します。
補助列の整形結果をキーとして利用する仕組みは、日常的なデータ管理にも便利です。
【操作のポイント】元の表示用データは残し、検索や突合には整形後の補助列を使い分けると安全です。
データの種類ごとに方法を選ぶ考え方
空白の削除方法は、データの種類によって使い分ける必要があります。
コードやメールアドレスのように空白が不要なデータでは、SUBSTITUTE関数や置換による完全削除が向いています。
氏名、部署名、住所のように単語の区切りを残したいデータでは、TRIM関数による整形が適しています。
見た目を整える目的と、検索用のキーを作る目的では、残すべき空白が異なる場合があります。
改行や特殊文字が多い外部データでは、CLEAN関数、SUBSTITUTE関数、TRIM関数を順に組み合わせる方法も検討しましょう。
どの方法でも、元データを保管しながら変換結果を確認することが、誤った更新を避ける基本です。
【操作のポイント】削除対象を空白の種類とデータの用途で判断し、最初から一律の処理を行わないようにしましょう。
まとめ エクセルで文字列を整えて空白を削除する方法
Excelで空白を削除する方法は、文字列のどの空白を消したいかによって選ぶことが重要です。
途中を含む半角スペースをすべて消したい場合は、=SUBSTITUTE(A2,” “,””)を使います。
全角スペースも対象にする場合は、SUBSTITUTE関数を重ねるか、全角スペースを指定した置換を実行します。
先頭と末尾の空白を取り除き、単語間の区切りを1つ残したい場合は、TRIM関数が便利です。
改行や見えない制御文字が混ざるデータでは、CHAR(10)、CLEAN関数、CHAR(160)を使った処理が役立ちます。
検索で一致しない、文字数が合わない、並べ替えが不自然といった問題は、見えない空白が原因になっていることがあります。
まずは別列で数式を試し、結果を確認してから値として貼り付ける流れを習慣にしましょう。
元データを残しておけば、空白を消しすぎた場合にも落ち着いて見直せます。
