【Excel】TRIMで余計なスペースを除く方法|全角空白・特殊な空白との違い

見た目が同じ商品コードなのに検索が一致しない場合は、先頭や末尾の空白を確認します。まずTRIM関数で処理した結果を見て、残った空白の種類を調べます。全角空白を半角へそろえたい場合や、Web由来の特殊な空白を除く場合は、置換する対象を指定します。元データを残した作業列で試し、削ってよい文字だけを整えましょう。

空白を消す前に、元データと比較できる列を用意する

まず検索する値と検索先の両方を確認します。一方の末尾だけに空白が残っていれば、検索側だけを直しても一致しません。外部から届いた名簿や商品一覧は、原本を保存したうえで別の列へ処理結果を出します。既存の計算列を上書きしない場所を選んでください。

A2に元の文字列、B2に処理結果を置く形なら、C2に=LEN(A2)、D2に=LEN(B2)を入れると文字数を比較できます。LENには空白も含まれます。ただし、文字数が同じでも内容が同じとは限らないため、数値だけで一致を判断しません。

氏名の姓と名を分ける空白、商品名に含まれる空白、固定桁のデータで位置を示す空白には意味があります。「スペースはすべて不要」という前提で一括削除すると、元とは別の表記になります。検索に使う照合用の列だけ整える方法なら、正式名称を保ったまま比較できます。

特定の数件だけ一致しないのか、全行で一致しないのかも確認します。全行なら検索範囲や文字列・数値の違い、検索方法そのものも調べる必要があります。

TRIMで半角スペースを整理する

まず通常の半角スペースを使ってTRIMの働きを確認します。先頭と末尾の半角スペースを除き、文字列の途中に連続する半角スペースがあれば1個へまとめます。途中の空白を全部消す関数ではありません。

1. A列に元の値を残し、B2へ=TRIM(A2)を入力します。
2. 代表的な数行の結果を確認します。
3. 問題がなければ必要な最終行まで数式をコピーします。
4. 変換前後を並べて、必要な区切りが残っているか確認します。

たとえば元の文字列が「半角空白2個+AB+半角空白2個+12+末尾空白1個」なら、元は9文字、結果の「AB 12」は5文字です。前後の3個が消え、途中の2個が1個になります。商品コードが本来「AB12」なら、この結果にはまだ区切りの空白が残っています。

コード内の半角スペースをすべて取り除く仕様が確認できている場合に限り、=SUBSTITUTE(A2," ","")のような置換を検討します。氏名や英語の商品名にも同じ式を流用せず、列ごとの決まりに合わせます。

全角スペースと改行しない空白を個別に置き換える

全角スペースを半角へそろえてからTRIMを使うなら、=TRIM(SUBSTITUTE(A2,"　"," "))とします。最初の引用符内は全角スペース、次は半角スペースです。この式は全角空白を区切りとして残しつつ、前後や連続部分を整理します。

Webページからコピーした文字には、改行しない空白と呼ばれる文字が含まれる場合があります。Unicodeの値160に当たる空白は、TRIMだけでは除去されません。それが含まれると確認できた場合は、=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))で通常の半角空白へ置き換えてから処理します。

文字を調べるには、空でないセルの先頭を=UNICODE(LEFT(A2,1))、末尾を=UNICODE(RIGHT(A2,1))で確認できます。32なら通常の半角空白、160なら改行しない空白、12288なら全角空白です。空のセルには調査式を入れず、問題のある具体的な行で試します。

全角空白を数式で指定したい場合はUNICHAR(12288)を使えます。CHAR関数へ12288を渡して全角空白を作る方法ではありません。見分けにくい文字ほど、対象を特定してから置換してください。

CLEANとASCを無条件に組み合わせない

CLEANは一部の制御文字を取り除く関数です。対象は基本的にASCIIの0~31で、あらゆる不可視文字を取り除けるわけではありません。改行やタブも対象に含まれるため、文章の段落や項目区切りが必要な列へ使うと意味が変わります。

たとえば「東京」と「大阪」が改行で区切られたセルにCLEANを使うと、「東京大阪」とつながります。2つの語を分けて残すなら、改行を半角スペースへ置き換える=SUBSTITUTE(A2,CHAR(10)," ")など、目的に合う処理を選びます。複数種類の改行が含まれるデータなら、その違いも元の取り込み元で確認します。

ASCは全角の英字やカタカナなどを半角へ変換する関数です。空白だけを直したいのにASCまで使うと、会社名や商品名の表記も変わります。空白の置換にはSUBSTITUTE、文字幅の統一が必要な列にはASC、と処理の目的を分けます。

TRIM・CLEAN・ASCを重ねれば必ず正しいデータになる、という共通の式はありません。原本を残し、「何を何へ変えたか」を説明できる処理に絞ってください。

検索の両側をそろえ、重複も確認する

取引先コードの前後空白を除いて照合するなら、入力側とマスター側に同じ基準の照合用列を作ります。片側だけ全角空白を半角にし、もう片側に残すと、表記がそろいません。正式名称は原列へ残し、検索に使う列を明確に分けると扱いやすくなります。

整理の結果、別々だった値が同じになることもあります。「AB 12」と「AB 12」はTRIMで同じ「AB 12」になりますが、元のシステムでは別コードとして使われている可能性もあります。重複が生じたら先に意味を確認し、検索で最初の1件を返しただけで正しい相手と判断しないでください。

完全一致で探すつもりなら、VLOOKUPの検索方法なども確認します。空白の処理は検索式の設定ミスまで直すものではありません。また、文字列の「0012」と数値の12は、見た目の空白とは別の問題です。桁数や先頭ゼロに意味があるコードを、合計用の数値と同じ基準で変換しないようにします。

少なくとも、一致した行、一致しない行、処理で重複した行をそれぞれ見て、使うべきマスターと対応しているかを確認します。

確認済みの結果を値として保存する

B列の式がA列を参照している状態でA列を削除すると、結果を失う原因になります。元データが不要になっても、まず処理結果を値として独立させます。原本の保管は別に続けてください。

1. B2から処理した最終行までを選んでコピーします。
2. 別の出力列、または出力用シートを選びます。
3. 「ホーム」→「貼り付け」→「値」を実行します。
4. 数式バーにTRIMなどの式がなく、文字列そのものが表示されることを確認します。

フィルターで一部の行だけを表示している場合は、行の対応を取り違えないよう、作業用コピーで全件を確認して処理します。1行ずれて貼り付くと名前とコードの組み合わせが変わるため、先頭・末尾・途中の代表行を照合します。

元の列へ上書きする場合は、その前に元の値を別名のブックへ保存します。TRIMで何個の空白を取り除いたかは、処理済みの文字列だけから復元できません。直後ならCtrl+Zで戻せますが、作業を終えたあとの復旧は原本を使います。

空白を直しても一致しないときの次の確認

結果が改善しなければ、削除する文字の種類をむやみに増やさず、問題の1行を詳しく比べます。ハイフンの種類、全角・半角の英数字、文字列と数値、検索範囲に対象行があるかなどを確認します。「見た目が同じ」を根拠に、すべて空白の問題と考えないことが大切です。

LENで文字数が変わった行は、先頭・末尾・単語間のどこが変化したかを見ます。文字数が減ったこと自体は成功の基準になりません。必要な区切りが残り、元の相手や商品を正しく特定できることが成功の基準です。

定期的に取り込むデータなら、次回も同じ作業列を使えるよう、対象の空白と使用した数式を列見出しや運用メモで共有します。元データ、照合用の結果、確定した出力を分けておけば、問題のある行だけ調べ直せます。

📊

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

🏆

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