TRIM・CLEAN関数の使い方｜不要な文字を削除
TRIM関数は余分なスペースを削除し、CLEAN関数は印刷できない制御文字を除去する関数です。外部システムからのデータ取り込みやCSVファイルのインポートでは、見えない文字が混入していることがあります。この記事では、2つの関数を使ったデータクレンジングの方法を実例とともに解説します。
TRIM関数の基本構文
=TRIM(文字列)引数の説明
| 引数 | 説明 | 必須/任意 |
|---|---|---|
| 文字列 | スペースを除去する対象の文字列やセル参照 | 必須 |
TRIM関数の動作
TRIM関数は以下の処理を行います。
- 文字列の先頭にあるスペースを削除する
- 文字列の末尾にあるスペースを削除する
- 文字列の途中にある連続したスペースを1つのスペースに置換する
単語間の1つのスペースは保持されます。
| 元の文字列 | TRIM関数の結果 | 説明 |
|---|---|---|
| ” 田中 太郎 " | "田中 太郎” | 前後のスペースを削除 |
| ”東京都 渋谷区" | "東京都 渋谷区” | 連続スペースを1つに |
| ” ABC DEF " | "ABC DEF” | 前後削除+連続スペース圧縮 |
注意：全角スペースへの対応
TRIM関数が除去するのは半角スペース（文字コード32）のみです。全角スペース（文字コード12288）は除去されません。全角スペースも含めて除去したい場合は、SUBSTITUTE関数と組み合わせます。
=TRIM(SUBSTITUTE(A2, " ", " "))この数式は、まず全角スペースを半角スペースに置換してからTRIM関数で整理します。
CLEAN関数の基本構文
=CLEAN(文字列)引数の説明
| 引数 | 説明 | 必須/任意 |
|---|---|---|
| 文字列 | 制御文字を除去する対象の文字列やセル参照 | 必須 |
CLEAN関数が除去する文字
CLEAN関数は、ASCIIコードの0から31に該当する制御文字を除去します。具体的には以下のような文字です。
| 文字コード | 説明 | 混入しやすい場面 |
|---|---|---|
| 0 (NULL) | ヌル文字 | データベースからのエクスポート |
| 9 (TAB) | タブ文字 | テキストファイルの貼り付け |
| 10 (LF) | 改行（ラインフィード） | Web上のデータコピー |
| 13 (CR) | 復帰（キャリッジリターン） | 他システムからのインポート |
CLEAN関数の制限
CLEAN関数はASCIIコード0～31の制御文字のみを除去します。ノーブレークスペース（文字コード160）など、32以上の非表示文字は除去できません。これらの文字を除去するにはSUBSTITUTE関数とCHAR関数を組み合わせます。
=SUBSTITUTE(CLEAN(A2), CHAR(160), "")実践例1：外部データの一括クレンジング
CSVインポート後のデータ整理
外部システムから顧客データをCSV形式でインポートした場合、以下のような問題が起きることがあります。
| A列（インポートデータ） | B列（整理後） | |
|---|---|---|
| 2 | ” 田中 商事 " | "田中 商事” |
| 3 | ”鈴木工業(改行文字あり)" | "鈴木工業” |
| 4 | ” 佐藤 物産 " | "佐藤 物産” |
B列の数式は、TRIM関数とCLEAN関数を組み合わせます。
=TRIM(CLEAN(SUBSTITUTE(A2, " ", " ")))この数式は3段階で処理します。まずSUBSTITUTE関数で全角スペースを半角スペースに変換し、次にCLEAN関数で制御文字を除去し、最後にTRIM関数で余分なスペースを整理します。
住所データのクレンジング
住所データは入力者によって全角スペース・半角スペースの使い方がばらつきがちです。
| A列（入力された住所） | B列（統一後） | |
|---|---|---|
| 2 | 東京都 渋谷区 神南 1-2-3 | 東京都 渋谷区 神南 1-2-3 |
| 3 | 大阪府 大阪市 北区 梅田 | 大阪府 大阪市 北区 梅田 |
| 4 | 愛知県 名古屋市 中区 栄 | 愛知県 名古屋市 中区 栄 |
=TRIM(SUBSTITUTE(A2, " ", " "))実践例2：セル内改行の除去
備考欄の改行を除去する
Excelではセル内でAlt+Enterで改行を入力できます。この改行文字（LF: 文字コード10）を除去するには、CLEAN関数またはSUBSTITUTE関数を使います。
=CLEAN(A2)改行を除去してスペースに置き換えたい場合は、SUBSTITUTE関数とCHAR関数を使います。
=SUBSTITUTE(A2, CHAR(10), " ")改行をカンマに置き換えて一行にする
複数行にわたる入力をカンマ区切りの一行に変換する場合です。
=SUBSTITUTE(A2, CHAR(10), ", ")例えば、セル内に改行で区切られた「りんご」「みかん」「ぶどう」が入力されている場合、結果は「りんご, みかん, ぶどう」になります。
実践例3：VLOOKUP検索の不一致を解消
見えない文字が原因のVLOOKUPエラー
VLOOKUP関数で検索値が一致しない場合、見えない文字が原因であることがあります。
セルの見た目は同じ「A001」でも、一方に制御文字やスペースが含まれていると一致しません。LEN関数で文字数を比較すると原因がわかります。
=LEN(A2)検索側の文字数が4ではなく5以上であれば、不要な文字が含まれています。
対処法として、VLOOKUP関数の検索値にTRIM関数とCLEAN関数を適用します。
=VLOOKUP(TRIM(CLEAN(A2)), B:C, 2, FALSE)INDEX MATCH関数でも同様に対応
=INDEX(C:C, MATCH(TRIM(CLEAN(A2)), TRIM(CLEAN(B:B)), 0))検索範囲側にも不要な文字が含まれている可能性がある場合は、上記のように検索範囲にもTRIM関数とCLEAN関数を適用します。ただし配列処理になるため、データ量が多い場合はパフォーマンスに注意が必要です。
名簿データの空白統一
姓名間のスペース統一
社員名簿で姓と名の間のスペースが統一されていない場合に整理します。
| A列（氏名） | B列（統一後） | |
|---|---|---|
| 2 | 田中 太郎 | 田中 太郎 |
| 3 | 鈴木 花子 | 鈴木 花子 |
| 4 | 佐藤 次郎 | 佐藤 次郎 |
=TRIM(SUBSTITUTE(A2, " ", " "))メールアドレスの前後スペース除去
メールアドレスにスペースが含まれると送信エラーの原因になります。
=TRIM(LOWER(A2))TRIM関数で前後のスペースを除去し、LOWER関数で小文字に統一しています。
データクレンジングの定番パターン
汎用クレンジング数式
多くの場面で使える汎用的なクレンジング数式をまとめます。
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, " ", " "), CHAR(160), " ")))この数式は以下の処理を行います。
- SUBSTITUTE関数で全角スペースを半角スペースに変換する
- SUBSTITUTE関数でノーブレークスペース（CHAR(160)）を半角スペースに変換する
- CLEAN関数で制御文字を除去する
- TRIM関数で余分なスペースを整理する
クレンジング前後の確認方法
クレンジングが正しく行われたかを確認するには、LEN関数で文字数を比較します。
=IF(LEN(A2)=LEN(B2), "変化なし", "修正あり("&LEN(A2)-LEN(B2)&"文字削除)")Google スプレッドシートでの互換性
TRIM関数とCLEAN関数はGoogle スプレッドシートでも利用可能です。ただし、Google スプレッドシートのTRIM関数は全角スペースも除去対象に含む場合があり、Excelとは動作が異なることがあります。互換性が必要な場合は事前に動作を確認してください。
まとめ
TRIM関数とCLEAN関数は、データクレンジングに欠かせない関数です。
- TRIM関数は前後のスペース削除と連続スペースの圧縮を行う
- CLEAN関数はASCIIコード0～31の制御文字を除去する
- 全角スペースはTRIM関数の対象外なので、SUBSTITUTE関数との併用が必要
- VLOOKUPの検索不一致は見えない文字が原因であることが多い
- 汎用クレンジング数式を一つ覚えておけば多くの場面に対応できる
外部データの取り込みや複数人が入力するデータの整理で活用してください。