Excelで氏名や商品コードを貼り付けたら、見えない空白のせいで検索・VLOOKUP・集計がうまくいかないことがありますよね。そんなときは、まずTRIM関数で余分な半角スペースを整えましょう。全角スペースや改行が混ざっている場合の対処も、順番に確認すればスッキリ解決できます。
結論：余分な半角スペースはTRIM関数で削除できます
TRIM関数は、セル内の文字を見やすく整えるための関数です。先頭と末尾の半角スペースを削除し、単語の間などに連続している半角スペースは1つだけ残します。たとえば「 半角スペース テスト 」のように余分な空白が入っている文字列を、読みやすい「半角スペース テスト」にできます。
TRIM関数の基本式
元データがA1セルにあるなら、空いているセルに次の式を入力します。
=TRIM(A1)
数式を入力したセルには、空白が整理された結果が表示されます。元のA1セルの内容は変わらないため、最初は隣の列で試せるのが安心ですね。
TRIM関数で余分なスペースを削除する手順
- 空白を削除したいデータが入っているセルを確認します。ここではA列のデータを整える例で進めます。
- A1の右隣など、結果を表示したい空いているセルをクリックします。たとえばB1セルを選びます。
- 数式バーまたはセルに=TRIM(A1)と入力します。
- キーボードのEnterキーを押します。
- 結果が正しければ、B1セル右下の小さな四角を下へドラッグするか、ダブルクリックして下の行にも数式をコピーします。
データ件数が多いときは、数式を入力したセルを選択してからコピーし、貼り付けたい範囲を選んで貼り付けても大丈夫です。表が途中で空白行なく続いているなら、フィルハンドルのダブルクリックが特に速いですよ。
数式の結果を元データに置き換える方法
TRIM関数の結果を作っただけでは、元データはそのまま残っています。提出用の表や取り込み用データとして使うときは、結果を値として貼り付けて置き換えましょう。
- TRIM関数の結果が表示されている範囲を選択します。
- Ctrl+Cを押してコピーします。
- 置き換えたい元データの先頭セルを右クリックします。
- 貼り付けオプションから値を選びます。キーボード操作なら、Ctrl+Alt+Vを押してからV、Enterでも値貼り付けできます。
- 数式ではなく整形後の文字だけが入ったことを確認して、不要になった作業列を削除します。
元データを直接上書きする前に、ファイルを保存するか、対象列を別シートへコピーしておくと安心です。
すべての半角スペースを消すならSUBSTITUTE関数を使う
TRIM関数は、単語の間にある半角スペースを1つ残します。そのため、電話番号の「090 1234 5678」を「09012345678」にしたい場合や、コード内の空白を完全になくしたい場合には向きません。
この場合は、SUBSTITUTE関数を使います。
=SUBSTITUTE(A1," ","")
1つ目の" "には半角スペースが1文字入っています。2つ目の""は空文字、つまり何もない文字です。これにより、A1セル内の半角スペースをすべて空文字に置き換えられます。
全角スペースも削除したいときの関数
日本語の氏名や住所をコピーしたときは、全角スペースが混ざることもあります。全角スペースはTRIM関数だけでは削除できません。全角スペースを消したいなら、SUBSTITUTE関数で「全角スペース」を指定します。
=SUBSTITUTE(A1," ","")
式の最初の引用符の間には、キーボードで入力した全角スペースが入っています。半角スペースと全角スペースの両方をすべて消すなら、関数を組み合わせましょう。
=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")
氏名の「山田 太郎」のように、意味のあるスペースまで消えてしまう点には注意してください。氏名の間の空白を残したいなら、全削除ではなく、データの用途に合わせてTRIM関数を使うのがおすすめです。
関数を使わずに空白を削除する：置換の手順
一度だけ特定範囲のスペースを消したい場合は、検索と置換でも対応できます。ショートカットキーはCtrl+Hです。ただし置換は元データを直接変更するため、必要なスペースまで消さないように範囲を限定して実行しましょう。
- 空白を削除したいセル範囲だけをドラッグして選択します。
- Ctrl+Hを押して「検索と置換」画面を開きます。
- 「検索する文字列」に半角スペースを1つ入力します。
- 「置換後の文字列」は空欄のままにします。
- 必要に応じて「オプション」を開き、「検索場所」がシートではなく選択範囲になっていることを確認します。
- まず「次を検索」で対象を確認してから、「すべて置換」をクリックします。
全角スペースを消すときは、「検索する文字列」に全角スペースを入力して同じ操作を行います。半角と全角は別の文字なので、両方ある場合はそれぞれ置換してください。
TRIMで空白が消えないときのチェックポイント
全角スペースが入っている
最も多い原因は、全角スペースです。TRIM関数は通常の半角スペースを対象にするため、全角スペースは残ります。セルをダブルクリックしてカーソルを動かすと、文字間が大きく空いて見えることがあります。全角スペースなら、SUBSTITUTE関数で削除するか、検索と置換で対応しましょう。
Webサイト由来の特殊な空白が混ざっている
Webページ、PDF、社内システムなどからコピーしたデータには、ノーブレークスペースと呼ばれる特殊な空白が含まれることがあります。この空白はCHAR(160)で指定できます。TRIM関数と組み合わせるなら、次の式を使ってください。
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))
特殊な空白を通常の半角スペースへ置き換えてから、TRIM関数で前後の空白や連続空白を整える仕組みです。
改行やタブが入っている
セル内改行やタブが混ざっていると、空白を削除しても見た目が整わないことがあります。改行はCHAR(10)、タブはCHAR(9)で指定できます。改行を半角スペースに置き換えて整えるなら、次の式が便利です。
=TRIM(SUBSTITUTE(A1,CHAR(10)," "))
タブも混ざる場合は、さらにSUBSTITUTE関数を重ねます。コピー元が不明なデータでは、見えない文字を疑うと解決が早いですよ。
数値として扱われている、または表示形式の空白がある
セルに余白があるように見えても、文字列内のスペースではない場合があります。たとえば表示形式、インデント、配置設定、列幅が原因なら、TRIM関数では変わりません。ホームタブの「配置」グループでインデントを減らす、またはセルの書式設定で表示形式を確認してください。
VLOOKUPやXLOOKUPのエラーが直らない
片方の表だけを整えても、検索値と検索範囲の両方に空白や表記ゆれが残っていると一致しません。検索する値の列と、照合される一覧の列の両方に同じ方法でTRIM関数を適用しましょう。英数字の全角・半角の違いもあるため、必要ならASC関数で半角へそろえる方法もあります。
覚えておくと便利な空白チェックと時短ワザ
LEN関数で文字数を比べる
見た目では空白が分かりにくいときは、LEN関数で文字数を確認できます。A1セルの文字数は=LEN(A1)で調べられます。TRIM後のセルと文字数が違えば、余分な文字が削除されたと分かります。
先頭・末尾だけを目視確認する
セルを選んでF2キーを押すと編集状態になり、カーソルで文字列の先頭や末尾を確認できます。余分なスペースがあるかを数件だけ確認したいときに便利です。確認後はEscキーで編集を取り消せます。
Ctrl+Eのフラッシュフィルを使う
規則的に不要な文字を取り除けるデータなら、隣の列に正しい形を1件入力し、下のセルでCtrl+Eを押すとフラッシュフィルが候補を自動入力してくれます。ただしデータによって判断がぶれることがあるため、大量データでは数件を必ず確認してください。
作業前に検索で件数を把握する
置換を実行する前にCtrl+Fで半角スペースや全角スペースを検索すると、どの程度混ざっているかを確認できます。いきなり「すべて置換」を押すよりも、対象範囲と件数を確認してから進めると、誤操作を防げます。
迷ったら、前後や連続した半角スペースを整えるならTRIM、半角スペースを完全に消すならSUBSTITUTE、全角スペースや特殊な空白があるならSUBSTITUTEを組み合わせる、と覚えておきましょう。空白を整えるだけで、検索エラーや集計ミスを防ぎやすくなります。
