インポートデータの整形
TRIM関数とSUBSTITUTE関数でインポートデータの余分な空白を除去する
CSVやWebから貼り付けたラベルに含まれる前後の空白やノーブレークスペースを除去し、検索不一致を解消します。
元のラベルのコピーを別列に残しておく
取引先リストをインポートしたところ、先頭や末尾に空白が入っているセルや、単語の間に複数のスペースが連続しているセルが混ざっていたとします。いきなり元の値を上書きせず、A列に元データ、B列に整形後の結果を並べて比較できるようにしましょう。
この例では、A2 に「空白2つ・Cedar・空白3つ・Workshop・空白2つ」が入っています。A3 には Oak と Studio の間にノーブレークスペース(改行なしスペース)が1つ入っています。A4 は通常の半角スペース1つだけの Maple Works、A5 は通常のスペース3つだけのセルです。
TRIM関数で通常の余分なスペースを取り除く
TRIM 関数は先頭と末尾のスペースを削除し、単語間の連続するスペースを1つだけに揃えます。A2 の結果は Cedar Workshop になります。もともと適切だった A4 の Maple Works のスペース1つはそのまま残され、スペースしか入っていなかったセルは空文字列("")になります。
=TRIM(A2)すべてのスペースを一括削除してしまわない
スペースをすべて空文字列に置換してしまうと、Cedar Workshop が CedarWorkshop に変わってしまいます。これは単なる整形ではなく別の名称への書き換えであり、後から正式な顧客名と照合できなくなる原因になります。
スペースの完全除去は、商品コードなど「空白を含めてはいけない識別子」専用のルールにとどめてください。社名・氏名・住所・商品名には正当な区切りスペースが含まれます。
Webページからコピーされたノーブレークスペースを処理する
Excelの TRIM 関数単体では、文字コード 160 のノーブレークスペースを除去できません。SUBSTITUTE 関数で CHAR(160) を通常の半角スペースに置き換えてから TRIM を適用します。これにより、A3 は単語間のスペース1つを保ったまま Oak Studio に整えられます。
演習データの A3 にある隙間は、見た目には普通のスペースとほとんど区別がつきませんが、内部的には別の文字コードです。目視確認だけでは検索不一致の原因を見逃してしまう理由がここにあります。
=TRIM(SUBSTITUTE(A3,CHAR(160)," "))クレンジングで何が変わったかを検証する
EXACT 関数を使うと、整形前後の文字列が完全一致するかどうかを確認できます。A4 ではもともと余分な空白がなかったため =EXACT(A4,TRIM(A4)) が TRUE を返し、空白が削られた A2 では FALSE を返します。
結果が空文字列になったからといって、元のセルが空欄だったとは限りません。A5 にはスペースが入っていたため TRIM の結果が空文字列になります。このような行を削除するか、補完するか、未入力扱いとして残すかを業務ルールに従って判断してください。
また、表記の揺れを整えても重複レコードが自動的に統合されるわけではありません。別々の2行がどちらも Cedar Workshop になった場合は、同一顧客と決めつける前に顧客IDを確認しましょう。
別の文字列を入力して動作を試す
A2 を「Pine・通常のスペース3つ・Repairs」に書き換えて計算すると、結果は Pine Repairs になります。その後リセットして、A3 に対する SUBSTITUTE と TRIM の組み合わせ数式も試してみてください。変更された行と新たに一致するようになったラベルの双方を確認し終えるまでは、元の生データを残しておきましょう。
サンプルを試す
初期値のまま計算するか、値を変更して動作を確認できます。