外部システムからコピペしたデータで、VLOOKUPが「なぜか一致しない」経験はありませんか？
目視では同じ文字列なのに#N/Aが返る。COUNTIFの集計結果も合わない。原因を調べると、先頭や末尾に見えないスペースが紛れ込んでいた――そんなトラブル、意外と多いですよね。
そんなときに使うのがTRIM関数です。余分なスペースを自動で削除してくれます。コピペデータの整形には欠かせない関数です。
この記事では、スプレッドシートのTRIM関数の基本から、VLOOKUP・COUNTIFとの組み合わせ、全角スペースへの対処法まで紹介します。
スプレッドシートのTRIM関数とは？余分なスペースを自動削除する関数
TRIM関数（読み方：トリム関数）は、テキストから余分なスペースを削除する関数です。
名前は英語の「trim（刈り込む・整える）」が語源です。文字列の前後についたスペースを刈り取るイメージですね。
TRIM関数にできることをまとめると、次のとおりです。
- 先頭のスペースを削除する
- 末尾のスペースを削除する
- 単語間の連続スペースを1つに圧縮する
Googleスプレッドシートには「データ → データクリーンアップ → 空白文字を削除」というメニューもあります。ただし、数式で処理するほうが再現性が高く、大量データにも対応しやすいですよ。
TRIM関数の構文と引数
=TRIM(テキスト)カッコの中に「スペースを削除したい文字列」を入れるだけです。
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| テキスト | 必須 | スペースを削除したい文字列やセル参照 |
引数はひとつだけ。シンプルな構文なので、すぐに使い始められます。
削除されるスペース・されないスペースの違い
TRIM関数が削除するのはASCIIコード32の半角スペースだけです。ここが重要なポイントです。
全角スペースやWebコピペで混入するCHAR(160)は削除できません。ExcelのTRIM関数は全角スペースも削除しますが、Googleスプレッドシートでは残ったままになります。
削除できる文字・できない文字を表にまとめました。
| スペース種別 | TRIM | SUBSTITUTE | CLEAN | 主な発生源 |
|---|---|---|---|---|
| 半角スペース（前後・連続） | 削除できる | 全削除可 | 不可 | CSVインポート・手入力ミス |
| 全角スペース | 削除できない | 削除可 | 不可 | 日本語入力切り替え時のミス |
| CHAR(160) | 削除できない | CHAR(160)指定で可 | 不可 | Webコピペ・HTMLデータ |
| 改行・制御文字 | 不可 | 一部可 | 削除可 | Excelコピペ・APIデータ |
CLEAN関数はASCII 0〜31の制御文字（改行やタブなど）を削除する関数です。TRIM関数と組み合わせれば、制御文字とスペースの両方を一度に除去できます。
=TRIM(CLEAN(A1))全角スペースやCHAR(160)への対処法は、後半のセクションで詳しく解説しますね。
基本の使い方：前後・連続スペースをまとめて消す
先頭・末尾スペースの削除（コピペデータの定番整形）
CSVや外部システムからコピペしたデータには、先頭や末尾にスペースが入りがちです。TRIM関数で一発で取り除けます。
A1に「 東京都 」（前後にスペース付き）が入っているとします。
=TRIM(A1)結果は「東京都」です。前後のスペースがきれいに消えました。
セル1つだけでなく、列全体に適用するのが実務的な使い方です。B1に数式を入れて下方向にコピーすれば、一括で整形できますよ。
単語間の連続スペースを1つに圧縮する
TRIM関数は前後のスペースだけでなく、単語の間にある連続スペースも整理してくれます。
A1に「田中 太郎」（スペース3つ）が入っているとします。
=TRIM(A1)結果は「田中 太郎」です。3つあったスペースが1つに圧縮されました。
先頭・末尾の削除と連続スペースの圧縮。この2つがTRIM関数の基本動作です。
VLOOKUPが一致しない？スペースが原因の3パターン
VLOOKUPで#N/Aが返るとき、まず疑いたいのがスペースの混入です。目視では同じ文字列でも、スペースが1つ入るだけで「別の文字列」と判定されます。
3大スペース原因の比較表
VLOOKUPが一致しない原因になるスペースは、大きく3種類あります。
| 原因 | 見た目 | 発生源 | 対処法 |
|---|---|---|---|
| 半角スペース（前後） | 目視でわかりにくい | CSV・手入力 | TRIM関数 |
| 全角スペース | 目視でわかりにくい | 日本語入力の切り替えミス | SUBSTITUTE関数で半角に変換してからTRIM |
| CHAR(160) | 完全に見えない | Webページのコピペ | SUBSTITUTEでCHAR(160)を指定して置換 |
どのスペースも目視では見つけにくいのがやっかいです。次に紹介するLEN関数の診断コードで、スペースが入っているかどうかをチェックしてみてください。
LEN関数でスペースを診断するコード
スペースが混入しているかどうかは、LEN関数で簡単に判定できます。
LEN関数は文字数を数える関数です。元の文字列とTRIM後の文字列の文字数を比較すれば、スペースが何文字あるか一目瞭然です。
=LEN(A1)-LEN(TRIM(A1))結果が「0」なら余分なスペースはありません。「0」以外ならスペースが混入しています。
たとえばA1が「 東京都 」（前後にスペース各1つ）なら、LEN(A1)は5、LEN(TRIM(A1))は3です。差は「2」。スペースが2文字入っているとわかります。
この診断コードを補助列に入れておくと、「どのセルにスペースが入っているか」をすぐに特定できますよ。
VLOOKUP+TRIMのネストで検索値を自動整形
VLOOKUPの検索値にTRIM関数をネスト（入れ子）すれば、スペースが入っていても正しくマッチします。
=VLOOKUP(TRIM(A2), D:E, 2, FALSE)A2に「 東京都」のようにスペース付きの値が入っていても、TRIM関数が先にスペースを削除します。そのあとでVLOOKUPが検索するので、#N/Aを回避できます。
検索範囲のほう（D列）にもスペースが入っている場合は、範囲側にもTRIM済みの補助列を用意しましょう。検索値と検索範囲の両方を整形するのが確実です。
TIP
VLOOKUPの検索値にTRIMをネストするのは、外部データを扱うときの定番テクニックです。「とりあえずTRIM」の習慣をつけておくと、スペース起因のトラブルを未然に防げます。
COUNTIF+TRIM応用｜スペース混じりのデータを正確にカウント
COUNTIF関数の使い方でデータを集計するとき、スペース混じりだと正しくカウントされません。「東京都」と「東京都 」は別の文字列として扱われるからです。
COUNTA関数の使い方でセル数を数える場面でも、スペースだけのセルが「データあり」と判定されてしまうことがあります。
この問題はTRIM済みの補助列を作ることで解決できます。
手順：
- 元データの隣の列にTRIM関数を入れる
- TRIM済みの列に対してCOUNTIFで集計する
=TRIM(A2)B列にこの数式を入れて下方向にコピーします。これでスペースなしのデータ列ができます。
=COUNTIF(B2:B100, "東京都")TRIM済みのB列に対してCOUNTIFを使えば、スペースの有無に関係なく正確にカウントできます。
元データを直接上書きしたい場合は、TRIM済みの列をコピーして「値のみ貼り付け」で元の列に戻しましょう。数式が消えて、整形済みのテキストだけが残ります。
全角スペースはTRIMで消えない｜SUBSTITUTE組み合わせ技
TRIM関数が削除するのは半角スペースだけです。全角スペースは削除できません。日本語データでは全角スペースが混入しやすいので、別の対処が必要です。
SUBSTITUTE+TRIMで全角・半角を一括処理
SUBSTITUTE関数は、指定した文字列を別の文字列に置き換える関数です。全角スペースを半角スペースに変換してから、TRIM関数で整形する2段構えが定番です。
=TRIM(SUBSTITUTE(A1," "," "))数式の中の “ ” は全角スペース、” ” は半角スペースです。SUBSTITUTE関数で全角→半角に変換し、そのあとTRIM関数で前後・連続スペースを削除します。
CHAR(160)（ノーブレークスペース。Webページのコピペで混入する見えないスペース）も同時に処理したい場合は、SUBSTITUTEをもう1段ネストします。
=TRIM(SUBSTITUTE(SUBSTITUTE(A1," "," "),CHAR(160)," "))ちょっと長く見えますが、やっていることはシンプルです。内側のSUBSTITUTEで全角スペースを半角に変換し、外側のSUBSTITUTEでCHAR(160)を半角に変換。最後にTRIMで整形しています。
REGEXREPLACEでまとめてクリーニングする方法
Googleスプレッドシートには、正規表現で文字列を置換するREGEXREPLACE関数があります。複数種類のスペースを1つの数式でまとめて処理できます。
=TRIM(REGEXREPLACE(A1,"[s ]+"," "))[s ]+ は「半角スペース・タブ・改行・全角スペースが1文字以上連続したもの」を意味する正規表現パターンです。これらをすべて半角スペース1つに置き換えてから、TRIMで仕上げます。
正規表現が苦手な方はSUBSTITUTE版で十分です。ただ、いろいろな種類のスペースが混在するデータを扱うなら、REGEXREPLACE版のほうがスッキリ書けますよ。
よくある質問（TRIM関数）
Q. TRIM関数で元データは変わりますか？
変わりません。TRIM関数は別のセルに結果を返す関数です。元データを上書きしたい場合は、TRIM済みの結果をコピーして「値のみ貼り付け」で戻してください。
Q. ExcelのTRIM関数との違いはありますか？
ExcelのTRIM関数の使い方は全角スペースも削除します。一方、GoogleスプレッドシートのTRIM関数は半角スペースしか削除しません。Excelファイルをスプレッドシートで開いたとき、全角スペースの処理結果が変わる可能性があるので注意してください。
Q. TRIM関数で改行は削除できますか？
できません。改行やタブなどの制御文字にはCLEAN関数を使います。=TRIM(CLEAN(A1)) のように組み合わせれば、制御文字とスペースの両方を除去できます。
Q. スペースをすべて削除したい場合は？
TRIM関数は単語間のスペースを1つ残します。すべてのスペースを完全に削除したい場合は、SUBSTITUTE関数で半角スペースを空文字に置換してください。=SUBSTITUTE(A1," ","") ですべての半角スペースが消えます。
まとめ
TRIM関数は、コピペデータの余分なスペースを削除するシンプルな関数です。
ポイントを整理します。
- 構文は
=TRIM(テキスト)の1引数だけ。前後のスペース削除と連続スペースの圧縮ができる - VLOOKUPの#N/Aエラーはスペース混入が原因のことが多い。検索値に
TRIM()をネストするのが定番の対処法 =LEN(A1)-LEN(TRIM(A1))でスペースの混入を素早く診断できる- COUNTIF関数でスペース混じりデータを集計するときは、TRIM済み補助列を作る
- 全角スペースはTRIMでは消えない。SUBSTITUTE関数で半角に変換してからTRIMで仕上げる
- CHAR(160)やWebコピペの見えないスペースもSUBSTITUTE+TRIMで対処できる
まずは =LEN(A1)-LEN(TRIM(A1)) で、手元のデータにスペースが潜んでいないかチェックしてみてください。
