TRIMで余分な空白を削除する

ウェブサイトやPDF、ほかのシステムからコピーしたデータには、余分なスペースが入っていることがよくあります。名前の前にスペースがあったり、単語の間に2つのスペースがあったり、見ただけでは気づけない末尾のスペースがあったりします。

こうした見えないスペースは、検索、並べ替え、照合を妨げます。TRIM関数を使えばスペースを整理でき、テキストを意図どおりに扱えるようになります。

TRIMは、次の単純なルールに従ってテキストからスペースを削除します。

• 先頭と末尾にあるすべてのスペースを削除します
• 単語の間に連続するスペースを1つのスペースに減らします

そのため、TRIM(" Hello world ")は、中央にちょうど1つのスペースがあり、両端にスペースのないHello worldを返します。

実際には、TRIMに余分なスペースのあるセルを指定します。A2に  John Smith  のように不要なスペースが入っている場合、この数式は整えられた値を返します。

結果は、先頭と末尾にスペースのないJohn Smithになります。数式を新しい列に入力してから、元の列に値をコピーして貼り付ければ、データをきれいな状態で保持できます。

よくある疑問は、TRIMがスペースをすべて削除するのかということです。いいえ。単語の間にある単一のスペースは意味があるため残します。

TRIM("New York")はNew Yorkを返します — 3つのスペースが1つにまとまります。文章や名前の読みやすさは保たれ、余分なスペースだけが削除されます。

末尾のスペースは見えませんが、実際には存在します。AppleとApple （末尾にスペースがあるもの）は見た目が同じでも、スプレッドシートでは異なる値として扱われます。

その結果、検索に失敗したり、重複が見逃されたりします。比較する前に両方の列へTRIMを適用すれば、この隠れた不一致を取り除けます。

TRIMの前後で長さを比較すれば、セルに余分なスペースがあることを確認できます。

LEN(A2)がLEN(TRIM(A2))より大きい場合、そのセルにはTRIMで削除されるスペースが含まれています。この数式は、セルに隠れていた余分なスペース文字の数を返します。

よく使われる対処法は、検索値をTRIMで囲み、余分なスペースによって検索に失敗しないようにすることです。

ここでは、VLOOKUPが表から検索する前にA2の検索語からスペースを取り除きます。A2に末尾のスペースがあっても、表内の整えられた値と一致するようになります。

TRIMが処理できるのは通常のスペース文字だけです。ウェブから取得したテキストには、ノーブレークスペースが含まれていることがあります。これは別の見えない文字で、TRIMでは無視されます。

削除するには、まず文字コード160を使ったSUBSTITUTEで通常のスペースに置き換え、その後にTRIMを適用します。この2つを組み合わせれば、頑固なスペースのほとんどを取り除けます。

インポートしたテキストには、改行のような印刷できない文字が隠れていることもあります。CLEAN関数はそうした文字を削除し、TRIMはスペースを処理します。

両方を使うと、テキストを完全にクリーニングできます。まずCLEANで制御文字を削除し、次にTRIMで残ったスペースを整えます。

TRIMはExcelとGoogle Sheetsの両方で、同じ目的に使えます。ただし、1つ注意点があります。ExcelのTRIMはノーブレークスペースを無視しますが、Google SheetsのTRIMはその一部も処理します。

どちらのツールでも安全に処理するには、SUBSTITUTEとCHAR(160)を使う方法が、ウェブから貼り付けたテキストを確実に整える手段です。

TRIMの数式を使うときれいなテキストが表示されますが、元の乱れたセルは残っています。完全に置き換えるには、数式のセルをコピーしてから、元の列に形式を選択して貼り付け → 値を実行します。

これで数式の結果が保持できる通常のテキストになり、補助列を削除できます。これでデータは表示上だけでなく、恒久的にクリーニングされた状態になります。

テキスト内の乱れたスペースを整えられるようになりました。

• TRIM(text)は先頭と末尾のスペースを削除します
• 連続する内部のスペースは1つにまとまります
• 末尾のスペースは検索や照合をひそかに妨げます
• LENからLEN(TRIM())を引いて、隠れたスペースを検出できます
• ウェブから貼り付けたテキストには、CLEANやSUBSTITUTE(CHAR(160))と組み合わせて使います

これで、テキストの抽出、測定、クリーニングに使う関数を学び終えました。次はテキストを結合して形を整える方法を学びます。