Excelで不要な空白を削除する

A列に「元データ」、B列に「用途」を入力します。A2:A9には、半角スペース、全角スペース、連続空白、改行、ノーブレークスペースなど、実務で遭遇しやすい問題を意図的に含めます。

※「␠」は半角スペース、「□」は全角スペース、「↵」はセル内改行を可視化した記号です。8行目の空白はWebページなどに含まれるノーブレークスペースを想定しています。

実際にExcel上で再現する場合は、次の数式をA2:A9へ入力します。文字として見えない空白や改行を、関数で確実に作れるためです。

以降は、このA2:A9を固定して使用します。処理結果はC列以降へ順番に追加し、最後に同じ表の中で比較します。

A2:A4はいずれも「愛知県」に見えます。しかし、A2には先頭の半角スペース、A3には末尾の半角スペース、A4には先頭の全角スペースが含まれています。

• ピボットテーブルで同じカテゴリが複数に分かれる
• COUNTIF・SUMIF・SUMIFSの件数や合計が合わない
• XLOOKUP・VLOOKUP・MATCHで一致しない
• UNIQUEで実質的に同じ値が複数表示される
• 重複削除や名寄せで同一候補を見落とす

したがって、空白処理は集計後の体裁調整ではなく、集計・照合・名寄せを始める前に行う前処理です。

C1に「TRIM後」と入力し、C2へ次の数式を入力してC9までコピーします。

日本語版ExcelのTRIM関数は、半角スペースだけでなく全角スペースも対象にします。前後の空白を削除し、文字列の途中に連続する空白は1個だけ残します。

A2～A6はTRIMだけで改善しました。先頭・末尾の半角空白だけでなく、A4の先頭にある全角空白も削除され、A5の連続する全角空白は1個に整理されます。一方、改行を含むA7とノーブレークスペースを含むA8は残ります。A9の内部空白も1個ずつなので、そのまま残ります。

D1に「標準クレンジング後」と入力し、D2へ次の数式を入力してD9までコピーします。

数式は内側から、次の順番で処理します。

1. CLEAN関数で改行・タブなど一部の制御文字を取り除く
2. CHAR(160)のノーブレークスペースを半角スペースへ変換する
3. 内部に残る全角スペースを半角スペースへ統一する
4. TRIM関数で前後と連続する半角スペースを整える

D列ではA2:A8までの表記が実務上扱いやすい形へそろいました。ただし、A9の電話番号は内部空白が意味のない区切りなので、別の処理が必要です。ここで「空白を1個残して統一する処理」と「すべて削除する処理」を分ける必要が見えてきます。

D列の数式は十分実用的ですが、関数が入れ子になっているため処理内容を読み取りにくくなります。LET関数を使うと、中間結果に英語名を付けられます。ここでは source、cleaned、nbspNormalized、spaceNormalized に統一します。

E2をE9までコピーすると、結果はD列と同じになります。変数名はすべて英語に統一しているため、後続のREGEXREPLACE版やMAP版とも対応関係を追いやすくなります。LET関数の利点は結果を変えることではなく、何をどの順序で処理したかを数式内に残せることです。

REGEXREPLACE関数を利用できるExcelでは、同じ処理を次のように記述できます。

正規表現の + は「半角スペースが1個以上連続する箇所」を意味します。今回の結果はD列・E列とほぼ同じですが、将来タブや複数種類の区切り文字をまとめて扱う場合に拡張しやすくなります。

※REGEXREPLACE関数を利用できないExcelでは、D列のTRIM・CLEAN・SUBSTITUTEによる式を使用してください。

ここまでは各行へ数式をコピーしました。MAP関数を使うと、A2:A9全体を一つの数式で処理できます。G1に「MAP一括処理」と入力し、G2へ次の数式を入力します。

結果はG2:G9へスピルします。同じクレンジングロジックを一か所で管理できるため、対象範囲が増えても数式を行ごとに修正する必要がありません。

A5の氏名「山田　　太郎」やA8の会社名「ABC SERVICE」では、内部空白は語の区切りです。したがって、D列のように半角1個へ統一するのが妥当です。

一方、A9の電話番号「090 1234 5678」は、比較・検索用データとしては空白を含まない方が扱いやすくなります。H1に「用途別の最終結果」と入力し、H2へ次の数式を入力してH9までコピーします。

ここで重要なのは、同じ空白でも一律に消さないことです。列の用途に応じて、内部空白を1個残すか、完全に削除するかを決めます。

I1に「判定」、J1に「削除・統一文字数」と入力します。

それぞれI9、J9までコピーすると、最初から使用してきた同じサンプル表が完成します。

A8はノーブレークスペースを通常の半角スペースへ置換していますが、どちらも1文字なので文字数差は0です。したがって、LENの差だけでは修正の有無を判定できません。A列とH列を直接比較するI列も併用する必要があります。

本稿ではA2:A9の同じサンプルデータを使い、TRIMだけの結果、複合クレンジング、LET・REGEXREPLACE・MAPによる書き換え、用途別の完全削除、最後の差分検証まで進めました。

1. 通常の半角・全角スペース：TRIMで前後を削除し、連続部分を1個へまとめる。
2. 内部空白の表記統一：全角を半角へそろえる場合はSUBSTITUTEを組み合わせる。
3. 改行・特殊空白：CLEANとCHAR(160)を指定したSUBSTITUTEを組み合わせる。
4. 新関数：LETで読みやすくし、REGEXREPLACEで拡張し、MAPで範囲全体へ一括適用する。
5. 内部空白：氏名・会社名では残し、電話番号などでは完全削除を検討する。
6. 検証：元データを上書きせず、修正判定と文字数差を同じ表で確認する。

空白処理では、関数を知るだけでなく、各処理が同じデータをどう変えるかを比較することが重要です。一つの表を最後まで使うことで、技術的な処理と、列の意味に基づく判断を切り離さずに学べます。