【Excel】TRIMで消えない空白をUNICODEで調べて置換する方法

TRIMを使っても空白が残る場合、通常の半角スペースとは違う文字が入っていることがあります。Webページからコピーしたノーブレークスペース、全角スペースなどが例です。ただし「消えない空白は必ず160番」と断定せず、文字の位置とUnicode番号を調べてから処理しましょう。

この記事では、元データを残し、UNICODEで正体を確認し、SUBSTITUTEで必要な文字だけを置き換える方法を説明します。通常スペースへそろえる方法と、空白を完全に消す方法は結果が異なります。人名や建物名など、区切りを残したいデータでは特に注意が必要です。

1. TRIMが対象にするのは通常の半角スペース

TRIMはコード32の半角スペースを対象とし、文字列の前後にあるものを除去し、途中で連続するものを一つにします。語と語の間に一つだけある半角スペースは残ります。すべての空白を取り除く関数ではありません。

=TRIM("  東京  営業所  ")

この式は「東京 営業所」を返します。前後と二重の半角スペースを整理しても、東京と営業所の間は一つのスペースで区切られたままです。ここを「東京営業所」にしたい場合には、TRIMだけではなく、空白の削除が必要かを別に判断します。

Unicode番号160のノーブレークスペースはU+00A0、全角スペースはU+3000です。どちらも通常の半角スペースとは別の文字です。160を「ASCIIの160番」と表現すると、0〜127の7ビットASCIIと混同するため、この記事ではUnicodeの十進数で区別します。

2. UNICODEで先頭・末尾・途中を調べる

A2を原文、B2を調査用とします。末尾の一文字を調べるなら、B2に次の式を入力します。空欄では一文字を取り出せないので、IFで空欄を分けています。

=IF(A2="","",UNICODE(RIGHT(A2,1)))

先頭を調べるならRIGHTをLEFTに変えます。途中の特定位置なら、たとえば3文字目に=UNICODE(MID(A2,3,1))を使います。UNICODE(A2)だけでは先頭しか分からないため、空白がある場所を切り出す必要があります。

32なら通常スペース、160ならノーブレークスペース、12288なら全角スペースです。見た目の幅が似ていても番号は異なります。対象の文字を数式で再現するときには、Unicode番号を指定するUNICHARを使います。CODEとCHARはコンピューターの文字セットに関わるため、Unicodeの検査と混ぜて扱わないようにします。

位置が分からない場合は、別の列に1、2、3と文字位置を並べ、=UNICODE(MID($A$2,C2,1))で調べます。C2には位置を入れ、LEN(A2)までの行だけを対象にします。原文が長い場合も、先頭だけを見て空白がないと結論づけないことが大切です。

3. 160を通常スペースへ置き換えてからTRIMする

練習用A2に次の数式を入力すると、前後と語間にノーブレークスペースを持つデータを作れます。記事の表示から見えない空白をコピーするより、対象が明確な方法です。

=UNICHAR(160)&"東京"&UNICHAR(160)&"営業所"&UNICHAR(160)

B2へ次の式を入力します。SUBSTITUTEで160を通常スペースへ変え、その後にTRIMで前後を取り除きます。結果は「東京 営業所」で、語間の区切りは残ります。

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

同じA2へ=SUBSTITUTE(A2,UNICHAR(160),"")を適用すると、すべての160が消えて「東京営業所」になります。前後だけでなく語間も消えるため、どちらの結果が欲しいのかを先に決めましょう。SUBSTITUTEで置換回数を省略すると、対象文字列のすべての出現が置換されます。

練習例は原文8文字、区切りを残した結果6文字、全削除なら5文字です。LENで数えたうえで、残った語間の一文字が32になったことをUNICODEで確認します。見た目がすっきりしただけでなく、意図した文字列になったかを検査できます。

4. 全角スペースなどは確認した文字だけ追加する

全角スペース12288も通常スペースへ統一する方針なら、変換をもう一段追加します。たとえばB2で160を置き換え、C2で全角スペースを置き換え、D2でTRIMを使うと、どこで変化したか追いやすくなります。

=SUBSTITUTE(A2,UNICHAR(160)," ")
=SUBSTITUTE(B2,UNICHAR(12288)," ")
=TRIM(C2)

全角スペースを人名の姓と名の区切りとして使っている場合などは、半角へ変えてよいかを提出先の仕様で判断します。幅をそろえたいだけなのに区切りごと削除する必要はありません。

ゼロ幅スペースU+200Bは十進数8203で、32や160とは別です。調査で8203と分かり、不要だと確認できたときだけ=SUBSTITUTE(A2,UNICHAR(8203),"")を使います。見えない文字をすべて同じものとみなし、長い置換式へ次々追加するのは避けます。文字が持つ役割を知らずに消すと、言語によっては表記を壊す原因になります。

5. 改行や取り込み処理と区別して整理する

番号が9ならタブ、10や13なら改行に関わる制御文字です。これはTRIMの対象スペースとは別で、CLEANの範囲に含まれます。ただしCLEANは削除するので、「東京+改行+営業所」を区切り付きで一行にしたい場合は、改行を通常スペースへ置き換える処理を先に行います。

毎回同じCSVを受け取るなら、原本シートと変換用シートを分け、どの文字番号を置き換えるか記録しておきます。更新時には、新しい原本を入れた後に処理範囲の最終行を確認します。行数が増えたのに数式が途中までしか入っていないと、一部だけ未処理のままになります。

Power Queryを使う運用でも、ワークシートのTRIMとクエリのトリミングを同じ挙動だと決めつけないでください。前後だけを整えるのか、途中の連続も減らすのか、特定文字を置き換えるのかを分け、取り込み後の結果で確認します。特殊文字の項目を選ぶだけで、任意のUnicode空白が自動除去されるとは扱いません。

6. LENと照合で変換の副作用を点検する

原文A2と変換後B2を並べ、=LEN(A2)と=LEN(B2)で長さを比較します。差が出た行は確認対象ですが、文字数が同じなら文字も同じ、とは言えません。通常スペースへの置換は一文字を一文字に置き換えるため、長さが変わらない場合もあります。

検索で使うキーなら、検索側とマスター側を同じ規則で整えてから照合します。一方だけを処理して解決しない場合に、さらに無関係な文字を削除しないようにしてください。商品コードのハイフンや氏名内の中点は、空白ではない必要な文字かもしれません。

空白を減らした結果、別々だったキーが同じ文字列へそろう場合もあります。重複が増えていないか、該当する行の原文が同じ意味なのかを確認します。特に「AB」と「A B」を区別するシステムでは、スペース全削除は不適切です。

7. 原文と変換ルールを残して確定する

提出用の列だけをコピーして値貼り付けし、元の原文と作業用の式は別に残します。何番の文字を何に変えたか、内部の区切りを残したかを記録しておけば、後日取り込み先から修正を求められてもやり直せます。

誤って原文へ貼り付けた直後は元に戻す操作で復旧します。保存後の結果から削除前の空白を完全に復元することはできないので、元ファイルの保管が必要です。TRIMが効かないときは、関数の重ねがけよりも「対象の一文字を取り出す」「番号を調べる」「意味を保つ置換を選ぶ」という順序で進めましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】