=TRIM(A2)は、A2の文字列の前後にあるスペースをすべて取り除き、単語の間に続くスペースをそれぞれ1つにします。" Ana Silva "はAna Silvaになります。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | TRIM | Length before | Length after |
| 2 | Ana Silva | Ana Silva | 14 | 9 |
| 3 | Ben Okafor | Ben Okafor | 13 | 10 |
| 4 | Chen Wu | Chen Wu | 10 | 7 |
A列ではスペースが見えないので、長さの列を用意しています。LENはスペースも数えます。余分なスペースはたいてい取り込んだデータや貼り付けたデータと一緒に入り込み、見た目には何の印もないまま検索や比較を壊します。
余分なスペースが数式を壊す理由
Excelにとって、末尾にスペースのあるAna とAnaは違う文字列です。比較はFALSEを返し、COUNTIFはそのセルを数えず、検索は#N/Aを返します：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Score | Is it Ana? | Ana's score |
| 2 | Ana | 90 | FALSE | not found |
| 3 | Ben | 85 | TRUE | 90 |
| 4 | Chen | 78 |
A2にはスペース付きのAna が入っているので、C2はFALSEになり、D2は何も見つけません(4つ目の引数がなければXLOOKUPは#N/Aを表示します)。D3は数式の中で検索列全体にTRIMをかけ、90を見つけます。これはMicrosoft 365とExcel 2021で動きます。それより古いバージョンでは、先に列をきれいにしてください。スペースは検索の#N/Aの最もよくある原因の1つです。
すべてのスペースを取り除く
TRIMは単語の間にスペースを必ず1つ残します。電話番号や商品コードなどからすべてのスペースを取り除くには、スペースを空の文字列に置き換えます：
| A | B | C | |
|---|---|---|---|
| 1 | Code | TRIM | No spaces |
| 2 | AB 12 34 | AB 12 34 | AB1234 |
| 3 | 555 201 3344 | 555 201 3344 | 5552013344 |
| A | B | |
|---|---|---|
| 1 | Customer | Clean name |
| 2 | Eli Novak | |
| 3 | Fay Ruiz | |
| 4 | Gus Lee |
やってみよう: A列の名前には余分なスペースがあります。B2で、A2の名前を、前後にスペースがなく単語の間のスペースが1つの形で返してください。数式はB4まで下にコピーされます。
TRIMが効かない: ノーブレークスペース
WebページやPDFからコピーした文字列には、通常のスペース(文字コード32)の代わりにノーブレークスペース(文字コード160)がよく含まれています。見た目は同じですが、TRIMが取り除くのは文字コード32だけなので、Excelでは何も変わりません：
A2: Ana Silva (a non-breaking space between the names and one at the end)
=LEN(A2) 10
=LEN(TRIM(A2)) 10, TRIM removed nothing
直すには、まずSUBSTITUTEで文字コード160を通常のスペースに変え、残りはTRIMに任せます。下のA2の文字列はCHAR(160)で作っているので、Webページから貼り付けられる文字を確かめられます：
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Pasted text | Length | Code of 4th character | Fixed | Fixed length |
| 2 | Ana Silva | 10 | 160 | Ana Silva | 9 |
C2は、正体のわからない文字を調べる方法です。CODE(MID(A2,4,1))は4文字目の文字コードを返し、ここでは160です。D2は9文字で、A2の10文字から末尾のスペースを除いた長さです。
CLEAN: 改行やほかの見えない文字を取り除く
CLEANは文字コード0から31の印刷されない文字を取り除きます。改行(10)、復帰(13)、タブ(9)などです。スペースには手を付けないので、=TRIM(CLEAN(A2))の組み合わせがよく使われます：
| A | B | C | |
|---|---|---|---|
| 1 | Imported | CLEAN | TRIM(CLEAN) |
| 2 | Ana Silva | AnaSilva | AnaSilva |
CLEANは改行をそのまま削除するので、2つの名前がくっついてAnaSilva になります。改行が単語を区切っているときは、代わりにスペースに置き換えます：=TRIM(SUBSTITUTE(A2,CHAR(10)," "))。このケースはセル内の改行のページで紹介しています。
元の列をスペースを取り除いた文字列に置き換える
TRIMは結果を別のセルに書きます。データそのものを直すには：
- データの隣の空いている列に
=TRIM(A2)を入れて下にコピーします。 - その列をコピーします(Ctrl+C、MacではCmd+C)。
- 元の列を選択して ホーム > 貼り付け > 値の貼り付け を使い、セルに数式ではなく文字列を入れます。
- 作業列を削除します。
「検索と置換」でも数式なしでスペースを取り除けますが、先頭のスペースと単語の間のスペースを区別できません。スペースを空に置き換えると、Ana SilvaがAnaSilvaになってしまいます。スペース2つを1つに置き換え、Excelが置き換えるものを見つけなくなるまで繰り返すのが、単語の間についてのTRIMの規則を手作業で行う方法です。先頭や末尾の1つのスペースは残ります。
よくある質問
Excelでスペースを削除するには？
=TRIM(A2)は先頭と末尾のスペースを取り除き、単語の間にはスペースを1つ残します。単語の間も含めてすべてのスペースを取り除くには=SUBSTITUTE(A2," ","")を使います。
TRIMでスペースが消えないのはなぜですか？
そのスペースはおそらくノーブレークスペース(文字コード160)で、Webページからコピーした文字列によく含まれています。TRIMが取り除くのは通常のスペース、文字コード32だけです。先に変換します：=TRIM(SUBSTITUTE(A2,CHAR(160)," "))。
TRIMとCLEANの違いは何ですか？
TRIMは余分なスペースを取り除きます。CLEANは改行やタブなど、文字コード0から31の印刷されない文字を取り除きます。=TRIM(CLEAN(A2))で両方を行えます。
元の列をスペースを取り除いた文字列に置き換えるには？
空いている列に=TRIM(A2)を書いて下にコピーし、その列をコピーして、元の列を選択してから ホーム > 貼り付け > 値の貼り付け を使います。その後、作業列を削除します。
