ExcelのTRIM関数は、文字列の先頭と末尾にある通常の半角スペースを除き、単語間の連続する半角スペースを一つに整えます。構文は=TRIM(文字列)です。CSVや他システムから取り込んだ氏名・商品名が検索や集計で一致しないとき、補助列へ=TRIM(A2)を入れて差分を確認できます。ただしTRIMが対象にするのは主に7ビットASCIIの空白文字32で、Web由来のノーブレークスペース160、全角空白、すべての制御文字を除く万能な正規化ではありません。元列を残し、TRIM、CLEAN、SUBSTITUTEの役割を分けて使います。
TRIMが行う三つの処理
Microsoftの定義では、TRIMはテキスト中の空白を、単語間の一つを除いて取り除きます。先頭の空白、末尾の空白を削除し、途中の連続する通常スペースを一つへ縮めます。=TRIM(" 東京 営業所 ")なら「東京 営業所」の形になります。
この動作は「前後だけを削る」とは異なります。途中の連続空白も変えるため、固定幅データ、詩、文章のインデント、コード、区切りの代わりに複数空白を使う値では意味を壊すことがあります。対象列ごとに、途中の空白を一つへ減らしてよいか決めます。
元列を残して補助列で試す
A列が取り込み原文なら、B2へ=TRIM(A2)を入れ、最終行までコピーします。C列へ=EXACT(A2,B2)、D列へ=LEN(A2)-LEN(B2)を置くと、変更の有無と減った文字数を確認できます。まず数十件をフィルターして目視します。
B列をA列へ値貼り付けする前に、空欄、氏名、住所、会社名、商品コード、自由記述を含む代表行を確認します。元データのバックアップと処理件数を記録し、値貼り付け後も元列を別シートまたは保管ファイルへ残します。共有ブックを無断で一括更新しません。
検索・照合前の余分な空白を整える
VLOOKUP、XLOOKUP、MATCH、COUNTIF、重複削除などは、見えにくい空白差で別値になることがあります。検索側とマスター側へ同じTRIMルールを適用した補助列を作ると、余分な通常スペースが原因かを切り分けられます。片側だけ整えると結果を説明しにくくなります。
TRIM後に一致しても、別人・別商品を同じにしてよい証明にはなりません。元値完全一致と整形後一致を別の列で残し、変更された行を承認対象にします。IDの中の空白が有効文字ならTRIMを使わず、システム仕様を確認します。
ノーブレークスペース160は別処理
Microsoftは、TRIMがASCII空白32を対象に設計され、Unicodeのノーブレークスペース160を単独では除去しないと明記しています。WebページやHTMLメールから貼り付けた値でTRIM後も差が残る場合、この文字が含まれる可能性があります。
確認した上で、=TRIM(SUBSTITUTE(A2,CHAR(160)," "))のように160を通常スペースへ置換してからTRIMできます。ただし環境や取り込み文字列によって文字の表現が異なる場合があります。式を全件へ適用する前にCODE、UNICODE、LENなどで実際の文字を確認します。
全角空白は自動で消えない
日本語データには全角空白「 」が含まれます。TRIMはこれを通常スペース32と同じようには処理しません。氏名の姓と名を全角空白で区切る運用では、それを削除・半角化すると表示規則が変わります。住所中の全角空白も意味ある区切りかもしれません。
全角空白を通常スペースへ統一する要件なら、SUBSTITUTEで明示的に置換してからTRIMします。すべて削除するのか一つへするのかを仕様化します。半角・全角の混在を発見しただけで自動修正せず、出力先システムの許容文字と利用者の検索要件を確認します。
CLEANとは役割が違う
CLEANは印刷できない文字を取り除くための関数で、Microsoftによると7ビットASCIIの0から31を対象に設計されています。タブや改行などが含まれる取り込み値では=TRIM(CLEAN(A2))が役立つ場合がありますが、意図したセル内改行まで消える点に注意します。
CLEANもUnicodeのすべての非表示文字を除去するわけではなく、Microsoftは127、129、141、143、144、157などを単独では除去しないと説明しています。「CLEANをかけたから安全で完全」と考えず、残る文字を特定して必要なものだけSUBSTITUTEします。
SUBSTITUTEで対象文字を明示する
SUBSTITUTEは、指定した文字列を別の文字列へ置き換えます。構文はSUBSTITUTE(text,old_text,new_text,[instance_num])です。出現番号を省略するとすべて、指定すると該当回だけを置換します。TRIMが扱わない特定空白や区切りを明示的に変えるときに使えます。
似たハイフン、引用符、改行、全角記号を一括置換すると、固有名詞やコードを壊すことがあります。対象列と文字コードを記録し、変更前後のユニーク値、文字数、件数を比較します。秘密情報や署名値など完全一致が必要な列は正規化対象から除外します。
数値・日付・数式へ安易に使わない
TRIMはテキスト処理です。セルに数値や日付が正しく保存されているなら、空白を除くよりデータ型と表示形式を確認します。文字列として入った「 00123 」をTRIMしても、結果を数値化すれば先頭ゼロが失われます。識別子は文字列のまま保持します。
数式セルへTRIMを使うと、数式が返した表示文字を整えることはできますが、元の計算エラーや型問題を直すわけではありません。#N/Aや#VALUE!を空欄へ隠さず、エラー原因を別に調べます。日付文字列の変換は地域設定と年月日順を確認します。
大量データではテーブルと検証列を使う
数千行へ式をコピーするならExcelテーブルに変換し、「原文」「TRIM後」「変更有無」「減少文字数」「確認状態」を列として持たせます。新しい行へ式が自動展開されたかを確認し、列全体参照の多用で計算が重くならないよう実データ範囲を使います。
複数列・大量ファイルを定期処理するならPower Queryで置換・トリム手順を再現可能にし、クエリのソース、型、処理順を記録します。Excel関数とPower QueryのTrimが同じ文字を同じように扱うと決めつけず、実データで比較します。
完了は件数整合と再照合で判断する
処理前後で行数、空欄数、ユニーク件数、重複件数、検索不一致件数を比較します。TRIMで同じ値へ統合された組み合わせを一覧にし、意図した統合か確認します。先頭・末尾・連続空白、NBSP、全角空白、タブ、改行を含むテスト行も用意します。
値へ確定した後に元の空白位置が必要になることもあるため、原本を保持します。作業日、式、対象列、変更件数、承認者を記録します。TRIMの目的は入力を「きれいに見せる」ことではなく、定義した比較・集計ルールへ安全に合わせることです。
確認チェックリスト
- 途中の連続空白を一つにしてよい列か確認する
- 元列を残しTRIM後と文字数差を補助列で確認する
- ASCII空白32、NBSP160、全角空白を区別する
- CLEANで意図した改行やタブを消さないか確認する
- IDの先頭ゼロと意味ある空白を保持する
- 処理前後の行数、重複、不一致、ユニーク件数を比較する
コメント