【EXCEL関数】セル内の余分なスペースやセル内改行を一瞬で取り去りたい時の関数

先に、結論から言うと、

CLEAN(SUBSTITUTE(SUBSTITUTE(A1,"　","")," ",""))

なぜこれが必要か？

自分だけでExcelに入力する分には全く必要がない代物である。

ただ、案件によって、他人に、それも組版作業を知らない人に、必要項目の入力をしてもらうことがママあって、このような場合、Excel上での見栄えを気にして、スペースを使ったインデントやセル内改行などをしてこられるのである。

こちらからとしたら、見栄えは組版でするので無駄になると言ってもしてこられるので、ナンとも。。。

スペースはまだしも、特にセル内改行は、ExcelからInDesignにコピペする時に非常に厄介。セル内改行のせいで、表が崩れまくるのである。事故の元である。

これを手作業で削除しても埒が明かないし、余計なものを消してしまう恐れもあるので、上記の関数を捻り出した次第。

本来なら、スペースやセル内改行以外の制御文字なども考慮に入れるべきかもしれないが、今までの傾向からしてスペースやセル内改行のみなのと、関数が今以上に煩雑になるのもなぁと思い、今回はこんな感じで落ち着いたのである。

関数の解説

CLEAN 関数

CLEAN 関数は、ASCIIコードの印刷できない先頭の32文字（10進法表記で0～31）を削除するために設計された。

Excelのセル内改行コードは、

• Windowsでは、LF（ASCIIコードの10進法表記で10）
• Macでは、CR（ASCIIコードの10進法表記で13）

であり、ASCIIコードの印刷できない先頭の32文字に含まれている。

故に、単純に、セル内改行を削除したいセルがA1とすると、CLEAN(A1)とすればよいだけ。
（詳細は、CLEAN 関数 - Office サポート - Office Support）

SUBSTITUTE関数

SUBSTITUTE関数は、あるセル内の特定の文字列を別の文字列に置き換えるもの。

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

（詳細は、SUBSTITUTE 関数 - Office サポート - Office Support）

今回は、スペースを削除したいのだが、スペースには、全角と半角の2種類が主に使われて混在することもよくあるので、この2つを1つの関数で一気に削除したいので、

セルA1の全角スペースを削除する場合：
SUBSTITUTE(A1,"　","")

文字列にはセルA1を、検索文字列には""の間に全角スペースを、置換文字列には何も指定せず単に""を各々入力。
次に、

セルA1の半角スペースを削除する場合：
SUBSTITUTE(A1," ","")

文字列にはセルA1を、検索文字列には""の間に半角スペースを、置換文字列には何も指定せず単に""を各々入力。

全角スペースと半角スペースを一度に削除したいので、2つのSUBSTITUTE関数を合体させる。
どうするか。
どちらかの関数を、もう一つの関数のA1と差し替える。

SUBSTITUTE(SUBSTITUTE(A1,"　","")," ","")

この場合、半角スペースを削除するSUBSTITUTE(A1," ","")のA1を、全角スペースを削除するSUBSTITUTE(A1,"　","")に差し替えた。

これで、全角スペースと半角スペースを一度に削除できる。


で、最後に、SUBSTITUTE(SUBSTITUTE(A1,"　","")," ","")を、CLEAN(A1)のA1と差し替えると、冒頭の関数の出来上がり。
これで、セル内の余分なスペースやセル内改行を一瞬で取り去ることができる。

---