Excelで余分なスペースを削除する方法！TRIMとCLEANの使い分けと全角空白の対処法！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

上の画像が完成形です。「仕上げ」の列に、余分な半角スペース・全角スペース・改行がすべて取り除かれた文字列が入りました。

余分なスペースや改行が残っていると、見た目は同じなのに検索・並べ替え・VLOOKUPでの照合がうまくいかない原因になります。
今回は
・TRIM関数で前後の半角空白と連続する空白を1つにまとめる
・SUBSTITUTE関数で全角空白や改行を置き換える
・CLEAN関数で改行などの制御文字を削除する
この3つを組み合わせて、氏名と商品名をきれいに整える方法をゴールにします。

なお、全角空白がTRIMで消えない理由や、うまくいかないときの対処法（トラブルシュート）、VBAで一括処理する方法もあわせてご紹介します。
早速実装して試してみましょう！

上の画像が完成形です。「仕上げ」の列に、余分な半角スペース・全角スペース・改行がすべて取り除かれた文字列が入りました。

STEP1. 元データを用意する

まずは整形したいデータを用意します。
今回はシステムから取り込んだ名簿と商品リストを使います。
A列がNo、B列が氏名、C列が商品名です。

一見ふつうに見えますが、
B列の氏名には前後の半角スペースや名前の間の全角スペースが混ざっています。
C列の商品名には、Alt+Enterで入ったセル内の改行が残っているものがあります。
まずはこの汚れた状態を確認しておきましょう。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ Webの仕様上半角スペースや改行が入らない場合がありますので、Excelへ張り付けた後手動入力してください。。

空白の種類と使う関数

ひと口に「空白」といっても4種類あり、消せる関数が違います。

STEP2. TRIM関数で前後の半角空白と連続空白を削除する

もっとも基本になるのがTRIM関数です。
TRIM関数は、文字列の前後にある半角スペースを取り除き、間にある連続した半角スペースを1つにまとめる関数です。

D列に「元の文字数」、E列に「TRIM後」、F列に「TRIM後の文字数」を用意しました。

TRIMの効果は「文字数」で確認する（LEN関数）

TRIMで空白が消えたかどうかは、見た目だけでは分かりにくいことがあります。
そこで文字数を数えるLEN関数を使って、数字で確認しましょう。

D2セルを選んで、次の式をそのままコピー＆貼り付けしてください。

D2セルに入れましょう。

入力できたら、D2セルの右下をD11までドラッグしてコピーします。
すると元データの文字数が全員分表示されます。

2行目の佐藤さんは7文字です（「 佐藤 太郎 」＝前後に半角スペース）。
次にTRIM後の文字数（F列）と見比べると、何文字減ったかが数字で分かります。

2行目（佐藤太郎さん）を見てください。
元データは前後に半角スペースが入っていましたが、E列のTRIM後では前後の空白が消えています。
D列とF列の文字数を見比べると、7文字から5文字に減っていることが分かりますね。

6行目（伊藤健さん）は、名の間に半角スペースが2つ入っていました。
TRIM後は1つにまとまっているので、こちらも確認してみてください。
なお、サンプルの氏名は左詰めにしてあるので、前後の空白がそのまま見えます。
空白の数はD列（元）とF列（TRIM後）の文字数で確認してください。

「削減」の列を見ると、何文字の空白が消えたかが一目で分かります。

STEP3. TRIM関数を入力する画面

E2セルに入れましょう。
入力中は関数の引数のヒントが表示されるので、引数の順番を確認しながら進められます。
あとはE2セルを下のE11セルまでコピーすれば、氏名の列すべてに適用できます。

STEP4. TRIMでは全角スペースが整わない

ここがつまずきやすいポイントです。
TRIM関数が取り除けるのは半角スペースだけです。
日本語入力で入ってしまった全角スペース「　」を半角スペースに変えることはできません。

9行目（中村翔太さん）を見てください。
B列は「中村　翔太」で、名の間に全角スペースが1つ入っています。
E列のTRIM後も「中村　翔太」のままで、D列とF列の文字数はどちらも5で変わりません。
前後に余分な空白がないため、TRIMでは何も起きないのです。

このように、名前と名前の間に入った全角スペースはTRIMでは整いません。
これを解決するのが、次のSUBSTITUTE関数です。

STEP5. SUBSTITUTE関数で全角スペースを置き換える

全角スペースを整えるにはSUBSTITUTE関数を使います。

G2セルに入れましょう。
SUBSTITUTEで全角スペースを半角スペースに置き換えてから、TRIMで前後と連続を整える流れです。

するとG列の「仕上げ（氏名）」は、全角スペースもきれいに整います。
9行目（中村翔太さん）は、E列のTRIM後が「中村　翔太」でしたが、G列では「中村 翔太」になりました。
3行目（鈴木花子さん）も同じように整っていますね。

なお、名前の間の全角スペースを完全に削除したいときは、置換後の文字を半角スペースではなく空文字にします。

このように書きます。

STEP6. CLEAN関数で改行などの制御文字を削除する

次は商品名の改行です。
C列の商品名には、Alt+Enterで入ったセル内改行が残っています。
4行目の商品名は「キーボード」の下に「（日本語配列）」が続いており、セルの中で2行に分かれています。

改行の正体は制御文字のCHAR(10)で、目に見えないため厄介です。
こうした制御文字を取り除くのがCLEAN関数です。
CLEAN関数は、改行やタブなど印刷できない制御文字（CHAR 1〜31）をまとめて削除します。

ただし、CLEAN関数は半角スペースや全角スペースは削除できません。
スペースはTRIMとSUBSTITUTE、制御文字はCLEANと、役割を分けて覚えておきましょう。

CLEAN関数を入力する画面

H2セルに、3つの関数を組み合わせた数式を入力します。

内側からCLEAN → SUBSTITUTE → TRIMの順に処理されます。
1本の数式にまとめておくと、あとから元データを修正しても結果が自動で追従します。

STEP7. 3つの関数を組み合わせて完成させる

最後に、TRIM・SUBSTITUTE・CLEANの3つを組み合わせます。
G列（仕上げ・氏名）とH列（仕上げ・商品名）に、仕上げの数式を入れてあります。
処理される順番は内側から次のとおりです。

①CLEANで改行などの制御文字を削除する
②SUBSTITUTEで全角スペースを半角スペースに置換する
③TRIMで前後の空白を削除し、連続する空白を1つにまとめる

この順番にすると、余分な半角スペース・全角スペース・改行をまとめて整えられます。
G列の氏名も、H列の商品名も、きれいにそろいましたね。

TRIM・SUBSTITUTE・CLEANの使い分け

3つの関数はそれぞれ役割が違います。
消せるもの・消せないものを表にまとめました。

VBAで選択範囲を一括クリーニングする

件数が多く、毎回同じ作業をするなら、VBAでまとめて処理するとラクです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub CleanText()
    Dim r As Long, lastRow As Long
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    For r = 2 To lastRow
        Cells(r, 2).Value = Application.Trim( _
            WorksheetFunction.Substitute( _
                Application.Clean(Cells(r, 2).Value), "　", " "))
    Next r
End Sub

タカヒロ

「Cells(r, 2)」の 2 はB列という意味です。
処理したい列に合わせて数字を変えてみてくださいね。
実行すると、数式ではなく結果の文字列がそのまま書き込まれます。
元のデータを残しておきたいときは、先にシートをコピーしてから実行しましょう。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったようにいかないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う4つの関数の概要です。
文字の整形でよく使う関数なので、違いを押さえておきましょう。

TRIM関数（トリム）

SUBSTITUTE関数（サブスティチュート）

CLEAN関数（クリーン）

LEN関数（レン）

完成

「仕上げ（氏名）」と「仕上げ（商品名）」の列に、余分な半角スペース・全角スペース・改行がすべて取り除かれた文字列が入りました。
D列の元の文字数とF列のTRIM後文字数を見比べると、余分な空白が減っていることも確認できます。
文字の並びがそろったので、検索や並べ替え、VLOOKUPでの照合も正確になります。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
TRIMで整わない全角スペースと改行は、SUBSTITUTEとCLEANで対応するのがポイントです。
この3つを組み合わせておけば、取り込んだデータの整形がぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。