「コピぺ de 関数」FILE_05

今回はセル内に入っている余計な空白を消す方法、改行を解除する方法を解説していきます。

こんな時に使える方法です。

  • 空白が2ことか3こ入ってるから1つにしたい
  • セル内で文字が改行されてるのを解除して1行にしたい

それでは、セル内の余分な空白を削除する方法から見ていきます。

余計な空白を削除する

ではまずこちらのデータを、自身のExcelを開いてA1に貼り付けてください。（パソコンで見ると横に伸びてますが、問題ありません）

田中 ユタカ
　中田 サトシ
山田　 ノボル
東京都
荒川区
南千住
東京都
荒川区
　町屋

次にB1セルに下記の数式をコピペしてください。

=TRIM(A1)

すると田中とユタカの間の余分な空間が削除されました。

TRIM関数はお利口なので全部の空白を消すのではなく、2個以上空白が続いていた場合、1個だけ残すという働きをしてくれます。

これで連続して空白がいくつか入っていてもキレイに1個だけ空白が残ります。

また、B1セルの数式をずずずいーっとB3までコピーすると、A2、A3の文字も余計な空白が削除されます。

A2のセルは中田の前に空白が入っていたので、削除されています。

このように、文字列より前に入っている空白も削除してくれます。

また、A3セルは先頭の空白が削除され、山田とノボルの間の空白が削除されています。

しかし、B2セルの半角空白とは違い、全角の空白が残っています。

これではデコボコになってしまうので、別の関数も使って半角にしてしまいましょう。

過去の記事でも書いたASC関数（全角を半角にする関数）を使います。

B3セルに下記の数式を入れてみてください。

=ASC(TRIM(A3))

これで全角の空白が半角に変換してから、余計な空白の削除を行っているので、半角スペース1個分だけ残ります。

しかし！！

このように、「ノボル」というカタカナまでもが半角になってしまいました。

漢字だけでしたら半角にならないので先ほどの数式で解決できますが、セル内にカタカナがあるとそのカタカナも半角になってしまいます。

では、余計な空白は消しつつ、カタカナは全角のままにするにはどうしたら良いのでしょうか？

それには、前回記事でお伝えしたSUBSTITUTE関数を合わせて使っていきます。

こちらの数式をB3セルにコピーしてみてください。

=TRIM(SUBSTITUTE(A3,”　”,””))

この数式では、「全角スペースを無くし、余計な空白を削除する」というアクションとなり、画像のような結果となります。

これで、カタカナは全角に保ちつつ、全角と半角が混在する余計なスペースが削除できました。

余計な改行を削除する方法

次に、セル内の改行を削除する方法をお伝えしていきます。

ホームページからコピーしてきたり、Wordから文章などをコピーしてくると改行が入ってたりしますよね。

早速B4セルに下記の数式をコピペして改行を消してみましょう！

=CLEAN(A4)

すると、A3セルの東京都荒川区南千住が横に並びましたね。

これで改行もスッキリ消すことができます。

では応用として、A5セルを見るみてください。

改行が入っていて、さらにいらない空白まで入っていますね。

関数を2つ合わせて使うことで解決することができるので、下記のような数式を使って処理することができます。B5セルに入れてみましょう。

=CLEAN(TRIM(A4))

CLEANとTRIMは逆になっても問題ありません。

ここでこの記事を書いていて気になることが。。。

実はA5セルの2行目には「 荒川区」と文字の前に２つ半角の空白を入れていました。

TRIM関数は余計な空白を削除すると覚えてい他ので、B5の数式の結果としては「東京都 荒川区南千住」と荒川の前にスペースが残る想定でしたが、残らないという事態に。

試しに、A1セルの田中ユカタの名前の間に改行を入れて、C1セルに「=CLEAN(TRIM(A1))」と入れてみたら、これは半角スペースが１つ分残る！！

Microsoftの公式ページをみてみましたが、詳しい挙動について触れられていません。

各単語間のスペースは 1 つ残し、不要なスペースをすべて削除します。 TRIM 関数は、他のアプリケーションから読み込んだテキストに不要なスペースが含まれているときなどに使用します。

Microsoftより引用

どんな時に「不要なスペース」と判断しているかちょっとわからないですが、文字列によっては１つでもスペースが入っていると不要と判断しているようですね。

お節介な感じの挙動ではないので、とりあえず良しとしましょう！！

今回使った関数

TRIM関数・・・不要なスペースを取り除く

CLEAN関数・・・改行を取り除く

ASC関数・・・全角を半角に変換する

SUBSTITUTE関数・・・指定した文字列を別の文字列に置き換える

次回は、今日の日付を自動入力（TODAY関数）する方法をお伝えします。