【エクセル】スペースや改行を一括で削除（置換）する方法～SUBSTITUTE関数が便利～

データベースでは、スペースがご法度とされることが多いです。

「ス　ペ　ー　ス」とは、この空白のことを指しています。

なぜスペースがご法度かというと、検索でヒットされなくなったり、データが分断されたりと不都合があるからです。

など、エクセル資料を扱う際にもスペース問題にぶち当たることはよくあります。

と愚痴りたくもなりますが、愚痴っているだけではスペースは消えてくれません。

そこで今回は、愚痴っている間にスペースや改行を簡単に削除（置換）してくれる関数「SUBSTITUTE」を紹介します。

”うわっ！なぞの関数出てきた…”と思った人も安心してください。

一年に数回しかエクセルを使わない僕でもすぐに使いこなせた簡単な関数です。

ぜひ参考にしてください。

SUBSTITUTE関数とは？→文字列を置換する関数

SUBSTITUTE関数を使うことで

指定した文字列を他の文字列に置換（変更）する

ことができます。

※データベース上では値の変更を「置き換える」と使うので、「置換」という言葉が何度も登場します。

例えば、

=SUBSTITUTE（A1,”あ”,”ア”）

という関数はA1のセルにある「あ」を「ア」に置換します。

A1のセルを変えるだけなら手動でもいいのですが、”A列をすべて置き換えたい”というときは手動だとシンドイです。

その場合は計算式をオートフィルするか、以下のような式に変更します。

=SUBSTITUTE（A:A,”あ”,”ア”）

A列にある「あ」の文字をすべて「ア」に置換する計算式です。

「A1」を「A:A」に変更しました。

計算式を入力しなくても、専用のフォーマットに入力していけば自然と式が立てられます。

空白にしていた「置換対象」とはなんぞ？

文字列をすべて変更する場合は空白にしておくので、基本的にはあまり触りません。

置換対象は検索文字列の何文字目を対象にするか？をしている項目です。

上記の場合ですと、置換対象が「2」となっているので、２番目の「あ」が「ア」に置き換えられています。

このように一部だけ置換したいときに使うのですが、あまり出番はありません。（個人的には…）

SUBSTITUTE関数を使う理由とは？→置換をしたい文字列を探す必要がない

SUBSTITUTE関数を使うメリットは一括で変更できる手軽さもありますが、

個人的に一番気に入っている特徴が、チェック漏れがないところです。

例えば、手動でA列の「〇〇を△△置き換えたい」という場合は、上から順番にチェックをしていかなければなりません。

A列すべてのセルに〇〇が含まれているのならまだいいのですが、

あったり、なかったりするとチェック漏れする可能性が高まります。数が多ければなおさらです。

SUBSTITUTE関数を使うことで、チェック漏れの心配はなくなり、文字列の置換も同時に行えます。

手動でチェックしていくよりも楽ですし、なにより確実です。

SUBSTITUTE関数を使ってスペースや改行を削除する方法

では本題に入ります。

スペースの削除も改行の削除もやり方的には同じです。

ただし、どちらもコツある。というか知らないとできないことなので紹介します。

スペースの削除は”　”を””に置換する

上の表：A列にあるスペースを削除していきましょう。

スペースを削除するときは

”　”を””に置換をすればOKです。

=SUBSTITUTE（A:A,”　”,””）

「A列」にある「全角スペース」を「スペース無し」で置換

という式になります。

スペースがなくなりましたがまだ違和感が残りますね。

実はまだ半角スペースが削除できていません。

スペースを削除するときは、全角と半角どちらのスペースも指定する必要があります。

全角・半角、どちらも含まれている場合は、「全角を削除したあとに、改めて半角も削除する」

あるいは、全角と半角どちらも対象となるような式を立てなければなりません。

=SUBSTITUTE(SUBSTITUTE（A:A,”　”,””）,” “,””）

上記の式を使えば、A列にある全角、半角すべてのスペースが削除されます。

と思うでしょうが、コピペすればいいですよ。

（A:A,”　”,””）,” “,””）

の部分だけお好みに変更してください。

一応解説です。

それでも消えないノンブレークスペース

SUBSITITUTEを使えばチェック漏れの心配はなくなるとお伝えはしたものの、少し厄介な存在があります。

“　”と” “を””で置き換えたのにも関わらず、まだ消えないスペースです。

ノンブレークスペースと呼ぶそうですね。

そんな消えないスペースを削除するには、キャラクター関数（CHAR関数）を使います。

また、新しい関数が…

と思われそうですが、ここで新たに覚えるのは「CHAR(160)」だけです。

CHAR(160)というのはノンブレークスペースの文字列を指していますので

=SUBSTITUTE（A:A,”CHAR(160)”,””）

と式を立てれば”　”や” ”では消せなかったスペースも削除できます。

改行の削除もCHAR関数を利用する

と感じたと思いますが、改行を削除（置換）するときはCHAR(10)を使用します。

=SUBSTITUTE（A:A,”CHAR(10)”,””）

A列にある”目には見えない改行を指示している文字列”を削除

という式となります。

文字列の前後にあるスペースを消したいだけならTRIM関数を使うのもあり

↑のエクセル表はセル内に全角、半角のスペースがあると背景色が変わる設定にしてあります。
TRIM関数でちゃんと削除できたか確認するためです。

今回は、文字列の途中にあるスペースを消す目的だったのでSUBSTITUTE関数を紹介しましたが

文字列の前後「　ああ　」こんな感じスペースならTRIM関数で一発。

文字列の間にあるスペースは削除できないので注意してください。「　あ　あ　」の場合は「あ　あ」となります。

このようなとき、間のスペースも消したい場合はSUBSTITUTE関数を使いましょう。

ゾンビ…ノンブレークスペースが出現した場合もSUBSTITUTE関数で対処しなければなりません。

まとめ～データにスペースがないに越したことはない～

データベースにおいてはスペースが含まれていると、思うような検索結果が出なかったり、データが分断されてしまったりと不都合が生じます。

そこで今回はエクセルでスペースや改行を削除する方法を紹介しました。

スペースの削除にはSUBSTITUTE関数を使います。

”　”全角スペースや” ”半角スペースを””スペース無しに置き換える関数です。

スペースだけでなく他の文字列で使用可能です。

“　”や” “では消えないスペース、ノンブレークスペースはCHAR関数を使います。

ノンブレークスペースはCHAR(160)です。

改行についてもCHAR関数を使います。改行はCHAR(10)

前後にあるスペースを消すだけならTRIM関数が便利です。

スペースのないエクセル資料が好ましいですが、もらった資料にスペースがついてしまっているなら直すしかありません。

とはいっても、関数を使えばほとんど手間なく簡単に処理できます。

時短にもつながるのでぜひ使ってみてくださいね。