今回は、ExcelのTRIM関数を使って、文字列に含まれる余分なスペースを削除する方法と、データ整理で役立つ組み合わせのテクニックを紹介します。対象はExcel for Microsoft 365、Excel 2024/2021/2019などのデスクトップ版を想定しています。TRIM関数はWeb版やMac版でも同じ書き方で利用できます。
TRIM関数とは
TRIM関数は、文字列の先頭と末尾にあるスペースを取り除き、単語と単語の間にあるスペースを1つだけ残す関数です。単語の間にスペースが2つ以上続いている場合も、1つにまとめてくれます。
ほかのシステムから書き出したデータや、Webページからコピーした文字列には、見た目ではわからないスペースが紛れ込んでいることがあります。こうしたスペースがあると、同じ内容に見えるデータが別物として扱われ、検索や集計がうまくいかない原因になります。TRIM関数は、このような不要なスペースを整理するための基本的な道具です。
TRIM関数の構文
TRIM関数の書き方は次のとおりです。
- =TRIM(文字列)
引数には、スペースを削除したい文字列を指定します。指定方法は次の2通りです。
- セル参照：=TRIM(A2)のように、対象の文字列が入っているセルを指定する
- 文字列を直接入力：=TRIM(” 売上 報告 “)のように、文字列をダブルクォーテーションで囲んで指定する
実務では、セル参照で指定して、隣の列に整えた結果を表示する使い方が中心になります。
TRIM関数の基本的な使い方
たとえば、A列に取引先名が入力されていて、先頭や末尾にスペースが混ざっているとします。B列を作業列として使い、次の手順で整えます。
- セルB2に=TRIM(A2)と入力する
- Enterキーを押して結果を確認する
- セルB2のフィルハンドルをダブルクリックするか、下方向へドラッグして、残りの行にも数式をコピーする
これで、B列にはスペースが整理された取引先名が表示されます。元のA列はそのまま残るため、結果を見比べながら確認できるのが作業列を使う利点です。
削除されるスペースと残るスペース
TRIM関数の動作を整理すると、次のようになります。
- 先頭のスペース：すべて削除される
- 末尾のスペース：すべて削除される
- 単語間に連続するスペース：1つだけ残して削除される
- 単語間の1つだけのスペース：そのまま残る
単語間のスペースを完全になくすわけではない点に注意しましょう。姓と名の間のスペースなど、必要な区切りは残したまま整えられるのがTRIM関数の特長です。
元のデータをTRIM関数の結果に置き換える方法
作業列で整えたデータを元の列に戻したい場合、数式のままでは元の列を削除できません。値として貼り付けてから差し替えるのが安全な方法です。
- TRIM関数を入力した作業列の範囲を選択してコピーする
- 元のデータがある列の先頭セルを右クリックする
- 貼り付けのオプションから[値]を選ぶ
- 元の列の内容が整えられたことを確認してから、作業列を削除する
値として貼り付けておけば、作業列を削除しても結果が消えません。念のため、差し替える前にブックのコピーを保存しておくと、やり直しが必要になったときも安心です。
TRIM関数と組み合わせると便利な関数
TRIM関数だけでは取り除けない文字もあります。ほかの関数と組み合わせることで、より幅広いデータを整えられます。
CLEAN関数で印刷されない文字も取り除く
ほかのアプリケーションから取り込んだデータには、画面に表示されない制御文字が含まれていることがあります。こうした文字はCLEAN関数で削除できます。TRIM関数と組み合わせると、スペースと制御文字をまとめて処理できます。
- =TRIM(CLEAN(A2))
内側のCLEAN関数で印刷されない文字を取り除き、外側のTRIM関数でスペースを整えるという流れです。
SUBSTITUTE関数で改行しないスペースを置き換える
TRIM関数は、キーボードで入力する通常の半角スペースを対象にした関数です。Webページからコピーしたデータには、見た目は同じでも別の種類のスペースである改行しないスペース（文字コード160）が含まれていることがあり、これはTRIM関数だけでは削除されません。
この場合は、SUBSTITUTE関数とCHAR関数を使って、改行しないスペースを通常のスペースに置き換えてからTRIM関数で整えます。
- =TRIM(SUBSTITUTE(A2,CHAR(160),” “))
Webからコピーした文字列で、TRIM関数を使ってもスペースが消えないと感じたときは、この組み合わせを試してみてください。
全角スペースを整理したいとき
日本語のデータでは、全角スペースと半角スペースが混在していることもよくあります。全角と半角をどちらかにそろえたい場合は、SUBSTITUTE関数で全角スペースを半角スペースに置き換えてから、TRIM関数で整える方法が考えられます。
- =TRIM(SUBSTITUTE(A2,” ”,” “))
2つ目の引数には全角スペースを、3つ目の引数には半角スペースを入力します。数式の見た目では違いがわかりにくいため、入力時に全角と半角を取り違えないよう注意しましょう。氏名のように全角スペースで区切ることが決まっているデータでは、置き換えによって表記ルールが変わってしまうため、置き換えるかどうかを事前に判断することが大切です。
TRIM関数が役立つ場面
TRIM関数は、ほかの機能と組み合わせたときに効果がわかりやすい関数です。代表的な場面を紹介します。
検索関数で一致しないとき
XLOOKUP関数やVLOOKUP関数で、見た目は同じ値なのに見つからないというエラーが出ることがあります。原因の1つが、検索値や検索範囲に紛れ込んだスペースです。検索する前に両方のデータをTRIM関数で整えておくと、一致しない問題を減らせます。
重複データを整理するとき
末尾にスペースがある「東京支店 」と、スペースがない「東京支店」は、Excel上では別の値として扱われます。重複の削除やUNIQUE関数を使う前にTRIM関数で整えておくと、同じ内容のデータを正しくまとめられます。
数値が文字列として扱われているとき
数字の前後にスペースが入っていると、数値ではなく文字列として扱われ、合計などの計算に含まれないことがあります。TRIM関数でスペースを取り除いた結果は文字列のままなので、計算に使う場合はVALUE関数と組み合わせるか、値として貼り付けたあとに数値へ変換する操作を行います。
- =VALUE(TRIM(A2))
TRIM関数を使うときの注意点
便利な関数ですが、使う前に次の点を確認しておくと、意図しない結果を防げます。
- 単語間のスペースは1つ残る：スペースをすべて削除したい場合は、SUBSTITUTE関数でスペースを空の文字列に置き換える
- 結果は文字列になる：数値や日付として使う場合は変換が必要になる
- 元データは変わらない：関数は別のセルに結果を表示するだけなので、差し替えは値の貼り付けで行う
- スペース以外の見えない文字は対象外：制御文字にはCLEAN関数、改行しないスペースにはSUBSTITUTE関数を組み合わせる
スペースをすべて削除したい場合は、次のように書きます。
- =SUBSTITUTE(A2,” “,””)
この方法では単語間の区切りもなくなるため、商品コードや電話番号など、スペースが不要なデータに限って使うのが無難です。
まとめ
ExcelのTRIM関数は、文字列の前後にある余分なスペースや、単語間で連続するスペースを整理できる関数です。要点を振り返ります。
- =TRIM(文字列)で、先頭と末尾のスペースを削除し、単語間のスペースを1つにそろえられる
- 作業列で結果を確認し、値として貼り付けてから元のデータと差し替える
- 制御文字にはCLEAN関数、改行しないスペースや全角スペースにはSUBSTITUTE関数を組み合わせる
- 検索関数の不一致や重複データの整理、文字列になった数値の修正などで役立つ
- 結果は文字列になるため、計算に使う場合はVALUE関数などで数値に変換する
取り込んだデータを集計や検索に使う前に、TRIM関数でひと手間かけて整えておくと、その後の作業がスムーズになります。