スプレッドシートでデータを整えていると、見た目では分からない空白のせいで並べ替えや検索、関数の結果が崩れることがありますよね。この記事では、TRIM関数の基本的な使い方から、消えない全角スペースや改行、CHAR160への対処法までを順番に解説します。コピペで使える数式もまとめているので、すぐ実務に使えます。
【結論】TRIM関数の基本構文とコピペで使える数式
結論から言うと、先頭・末尾・連続した半角スペースを整えるだけならTRIM関数で十分です。まずは =TRIM(A2) を使い、消えない場合だけCHAR160や全角スペース対策を追加すると、最短で問題を切り分けられます。 Google公式ヘルプ
TRIM関数の書き方と基本構文
TRIM関数の構文は =TRIM(text) です。textには文字列そのものか、文字列が入ったセル参照を指定します。実務では A2 や B3 のようなセル参照を入れる使い方が中心で、余分な半角スペースを自動で整理できます。 Google公式ヘルプ
ポイントは、削除ではなく整理だと理解することです。単語間の半角スペースは全消去されず、連続している場合に1つへ整えられます。氏名や商品名の前後だけを整えたい場面で特に相性がいい関数です。 Google公式ヘルプ
今すぐ使えるサンプル数式3選
=TRIM(A2) 先頭と末尾の半角スペース、連続した半角スペースを整理=ARRAYFORMULA(IF(LEN(A2:A),TRIM(A2:A),)) A列をまとめて整形=TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(A2),CHAR(160),LEFT(A2,0)),CHAR(12288),LEFT(A2,0))) 改行や特殊空白もまとめて対処
まず1つ目で通常の空白を確認し、消えない時だけ3つ目へ進むのが効率的です。いきなり複雑な式を使うより、原因ごとに段階的に試すほうがエラーも少なく、シートの保守もしやすくなります。
操作イメージを先に確認したい方は、こちらの動画も参考になります。 動画で確認する
TRIM関数とは？削除できる空白・できない空白の違い
TRIM関数は、文字列の見た目を整える基本関数です。ただし、あらゆる空白を消せる万能機能ではありません。削除できる空白とできない空白を先に理解すると、なぜ式が効かないのかをすぐ判断できます。 Google公式ヘルプ
TRIM関数が削除できる空白の種類
TRIM関数が得意なのは半角スペースです。具体的には、文字列の先頭にある半角スペース、末尾にある半角スペース、単語間に2つ以上連続した半角スペースを整理できます。データ入力ミスの修正には非常に有効です。 Google公式ヘルプ
対象TRIMの結果先頭の半角スペース削除できる末尾の半角スペース削除できる連続した半角スペース1つに整理される
TRIM関数では削除できない空白一覧
TRIM関数で残りやすいのは、全角スペース、ノーブレークスペース、改行、タブ、その他の不可視文字です。見た目は空白でも文字コードが異なるため、TRIMだけでは処理できないケースがあります。
残りやすい文字主な対処全角スペースSUBSTITUTEとCHAR12288ノーブレークスペースSUBSTITUTEとCHAR160改行SUBSTITUTEまたはCLEAN制御文字CLEAN
ExcelのTRIM関数との違い
大きな違いというより、GoogleスプレッドシートでもExcelでもTRIMは万能ではありません。GoogleスプレッドシートのTRIMは公式に「Whitespace or non-breaking space will not be trimmed」とされ、ExcelのTRIMもASCII 32の半角スペース向けです。そのため、全角スペースやノーブレークスペースは、どちらでもTRIM単体では残ることがあります。
そのため、Excel感覚でTRIMだけを入れるのは危険です。特にWebフォーム、CSV、コピペ元が混在するシートでは、全角スペースやCHAR160を含む前提で式を組むと再修正の手間を減らせます。
TRIM関数で空白が削除できない時の原因と対処法
TRIMで消えない時は、関数が壊れているのではなく、空白の種類が違う可能性が高いです。原因は大きく4つで、全角スペース、改行、CHAR160、制御文字に分けて考えると、最適な対処をすぐ選べます。
全角スペースが含まれている場合の削除方法
全角スペースはTRIMの対象外になりやすいため、SUBSTITUTEで先に消してからTRIMをかけます。実用的な式は =TRIM(SUBSTITUTE(A2,CHAR(12288),LEFT(A2,0))) です。先頭と末尾の全角スペース対策として最も使いやすい形です。
取引先名や住所録では、見た目では分からない全角スペースが混ざることが少なくありません。並べ替えや重複削除がずれる時は、まずこの式で整形してから次の処理へ進むと安定します。
セル内改行（改行コード）を削除する方法
セル内改行は空白ではなく改行コードなので、TRIMだけでは残ることがあります。削除するなら =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),LEFT(A2,0)),CHAR(13),LEFT(A2,0))) が分かりやすく、改行の有無を問わず整えられます。
問い合わせ内容やコピペした文章を1セルに詰めたデータでは、改行が原因で一致検索が失敗しやすいです。見た目が同じでも別文字扱いになるため、検索前に改行除去を入れておくと安心です。
ノーブレークスペース（CHAR160）の削除方法
CHAR160はWebサイトやPDF由来のデータに多い特殊空白です。これが混ざるとTRIMでは消えず、見えないのに一致しない厄介な状態になります。式は =TRIM(SUBSTITUTE(A2,CHAR(160),LEFT(A2,0))) を使うのが基本です。
商品説明やHTML由来の文字列を貼り付けた時に起こりやすく、半角スペースと見分けにくいのが難点です。TRIMで直らないのに目視では問題が見えない時は、まずCHAR160を疑うのが近道です。
その他の制御文字・不可視文字の削除方法
タブや一部の制御文字が原因なら、CLEAN関数を組み合わせます。基本形は =TRIM(CLEAN(A2)) です。さらに全角スペースやCHAR160も混ざるなら、CLEANの外側にSUBSTITUTEを重ねると、かなり広い範囲をカバーできます。
実務では1種類だけ混ざるより、複数の不可視文字が同時に入ることのほうが多いです。1回で直したい時は、CLEANで制御文字を除去し、その後にCHAR160と全角スペースを消す流れが失敗しにくいです。
スプレッドシートで空白を一括削除する3つの方法
空白削除を効率化する方法は3つです。元データを残したまま整えるならARRAYFORMULA、セルの値そのものを直すなら検索と置換、混在した特殊空白までまとめて消すなら複合数式が向いています。
ARRAYFORMULA関数で複数セルを一括処理する
複数行を一度に処理したいなら、=ARRAYFORMULA(IF(LEN(A2:A),TRIM(A2:A),)) が便利です。A列に新しいデータが増えても自動で整形されるため、毎回数式をコピーする手間を減らせます。
問い合わせ一覧、CSV取込結果、フォーム回答のように行が増え続けるシートでは特に効果的です。列を分けて整形結果を作れるので、元データを保持したままクリーンな値を参照できます。
検索と置換機能で元データを直接修正する
一度だけ大量修正したいなら、検索と置換のほうが速いです。元データを直接きれいにできるため、補助列を作りたくない時に向いています。半角スペースや全角スペースを手動で指定して一括削除する運用です。
対象範囲を選択する検索と置換を開く検索する文字に空白を入れる置換後を空欄にする一括置換する前に件数を確認する
ただし、この方法は元データを書き換えます。共有シートや履歴管理が必要な台帳では、いきなり実行せず、複製シートで確認してから本番に反映するのが安全です。
万能数式で全種類の空白をまとめて削除する
複数の空白が混在する時は、=TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(A2),CHAR(160),LEFT(A2,0)),CHAR(12288),LEFT(A2,0))) が実用的です。制御文字をCLEANで処理しつつ、CHAR160と全角スペースも同時に対策できます。
Webからのコピペ、外部システムCSV、他社作成ファイルをまとめる場面では、この式が最も安定します。式は少し長いですが、1回作ってしまえば再利用しやすく、トラブルの再発防止にも役立ちます。
シーン別：空白削除方法の選び方
最適な方法は、何を残したいかで決まります。元データを保持するか、今すぐ直接修正したいか、特殊空白まで含めて完全に整えたいかで選べば、必要以上に複雑な式を使わずに済みます。
TRIM関数が最適なケース
TRIMだけで十分なのは、半角スペースの入力ゆれが中心のケースです。たとえば氏名の前後の余白、商品コード入力時の余分なスペース、フォーム回答の軽い整形なら、=TRIM(A2) だけでほぼ解決できます。
また、関数が短く読みやすいため、他の担当者へ引き継ぐシートにも向いています。まずTRIMで試し、残る場合だけ追加処理へ進む流れが、最も管理しやすい運用です。
検索と置換が最適なケース
検索と置換が向くのは、過去データを一度だけ修正したいケースです。補助列を増やしたくない時や、完成済みの台帳の見た目をすぐ揃えたい時は、関数より早く終わることがあります。
一方で、自動化には不向きです。毎週同じ処理が必要な業務なら、手作業の置換よりARRAYFORMULAや複合数式のほうが再現性が高く、ミス防止にもつながります。
組み合わせ数式が最適なケース
組み合わせ数式は、空白の種類が読めないデータに最適です。Webコピペ、他システムCSV、外注先ファイルなど、入力ルールが揃っていないデータでは、TRIM単体よりCLEANやSUBSTITUTEを重ねたほうが確実です。
特に一致判定、JOIN、QUERY、VLOOKUPの前処理では効果が大きいです。事前に文字列を正規化しておくと、後続の関数エラーや重複判定のズレを大きく減らせます。
TRIM関数と一緒に覚えたい関連関数
空白削除の精度を上げるには、TRIMだけでなくCLEAN、SUBSTITUTE、REGEXREPLACEも知っておくと便利です。役割を分けて使えば、目に見えない文字が混ざったデータでもかなり高い精度で整形できます。
CLEAN関数：制御文字・改行を削除する
CLEAN関数は、印字されない制御文字を取り除くための関数です。基本は =CLEAN(A2) で、前後の半角スペースも整えたいなら =TRIM(CLEAN(A2)) と組み合わせます。改行やタブが混ざる文章列で特に有効です。
問い合わせ本文、住所、備考欄のような自由入力データでは、TRIMだけでは足りないことが多いです。まずCLEANで制御文字を除去し、その後にTRIMで見た目を整えると処理が安定します。
SUBSTITUTE関数：特定の文字を置換する
SUBSTITUTE関数は、特定の文字だけを狙って置換したい時に使います。全角スペースには CHAR(12288)、特殊空白には CHAR(160) を指定できるため、TRIMで消えない原因が分かっている時に最短で直せます。 Google公式ヘルプ
たとえば =SUBSTITUTE(A2,CHAR(160),LEFT(A2,0)) のように使えば、見えない特殊空白だけを除去できます。問題の文字をピンポイントで消せるので、元の文章構造をなるべく保ちたい時にも便利です。
REGEXREPLACE関数：正規表現で高度な置換をする
REGEXREPLACE関数は、文字の種類や位置を条件にまとめて置換したい時に向いています。先頭だけ、末尾だけ、複数種類の空白をまとめて処理したい場面で強力ですが、式が長くなりやすいため保守性とのバランスが重要です。
普段使いでは、まずTRIMとSUBSTITUTEで十分なことが多いです。正規表現は、定期的なデータ整形や複雑な前処理を自動化したい場合に採用すると、難しさに対して効果が見合いやすくなります。
まとめ
TRIM関数は、スプレッドシートの空白整理で最初に覚えるべき基本関数です。ただし、全角スペースやCHAR160、改行には別の対策が必要です。原因ごとに関数を使い分ければ、検索や集計が崩れないきれいなデータを作れます。
TRIM関数で空白削除をマスターする3ステップ
まず =TRIM(A2) で半角スペースを整理する消えなければ CHAR12288、CHAR160、改行を疑う複数原因がある時は CLEAN と SUBSTITUTE を重ねる
この3ステップで考えると、必要以上に複雑な数式を最初から組まずに済みます。まず簡単な式で確認し、残る文字だけを追加で対処する流れが、最も速くて再現性も高い方法です。
よくある質問（FAQ）
Q. TRIMで全部の空白は消せますか。 A: いいえ。主に半角スペースの整理向きで、全角スペースやCHAR160、改行は別関数が必要です。Q. 元データを残したい時はどうしますか。 A: 補助列にTRIMやARRAYFORMULAを入れて、整形後の列を参照する方法が安全です。Q. 一番失敗しにくい式は何ですか。 A: 原因不明なら、CLEANとSUBSTITUTEを組み合わせた複合式から試すと再修正が少なくなります。
基本の考え方を動画で復習したい方は、こちらも参考になります。 動画で確認する
コメント