複数の空白スペースを改行にする−TRIM関数・SUBSTITUTE関数・CHAR関数

Excel初心者の中には、［Alt］＋［Enter］でセル内改行できることをご存じないために、スペースをたくさん入力することで文字を送って、改行されているように見せかけるということをしてしまう方がいらっしゃいます。

こんなデータを受け取ってしまった場合、その修正作業に時間が取られてしまいます。

万能というわけではありませんが、その修正作業を関数を使って楽にする方法がありますのでご紹介しておきます。

TRIM関数とSUBSTITUTE関数とCHAR関数を使います。

上記の操作で、一つ以上の全角スペースが一つのセル内改行になります。

「=SUBSTITUTE(TRIM(A1),"　",CHAR(10))」
という数式だけでは、セル内で実際に改行が行われませんから、［セルの書式設定］ダイアログ−［配置］タブ−［文字の制御］欄−［折り返して全体を表示する］チェックボックスをOnにするのを忘れないようにしてください。

スペースを使って改行っぽく見せている元データが不要ならば、「=SUBSTITUTE(TRIM(A1),"　",CHAR(10))」の入力されているセルをコピーして、値の貼り付けをしてください。

「=SUBSTITUTE(TRIM(A1),"　",CHAR(10))」
という数式の意味は以下のとおりです。

ExcelのTRIM関数は、引数に指定された先頭と最後のスペースを削除し、文字列の途中にある連続したスペースを一つにまとめてくれる関数です。

「TRIM(A1)」
という数式で、もしA1セルに
「サ　　　　ン　　　　プ　　　　ル」
と入力されていると
「サ　ン　プ　ル」
という文字列が返されます。

［Alt］＋［Enter］をご存じない方が改行をするために無駄に入力していた途中のスペースが、一つになるのです。

SUBUSTITUTE関数は、文字列を置換してくれる関数です。

第1引数に指定された文字列の中から、第2引数に指定された文字を探して、第3引数に指定された文字にしてくれます。

上記の例では
第1引数に「TRIM(A1)」
第2引数に「"　"」
第3引数に「CHAR(10)」
と指定しているので、間の余分な文字列を一つにしたA1の文字列の中から、「"　"」全角スペースを探してて、「CHAR(10)」にしてくれます。

「CHAR(10)」はExcelのセル内改行です。

つまり「"　"」全角スペースをセル内改行してくれることになるわけです。

使われているスペースが全角スペースだけなら上記の
「=SUBSTITUTE(TRIM(A1),"　",CHAR(10))」
でOKですが、［Alt］＋［Enter］をご存じなくて、スペースを大量に入力する方の中には、全角スペースと半角スペースとを混在させて大量に入力するという方もいらっしゃいます。

上記の
「=SUBSTITUTE(TRIM(A1),"　",CHAR(10))」
では全角スペースを改行にしてくれるだけですので、全角スペースと半角スペースが混在しているような場合は、もう一つ数式を入れ子にしてください。

「SUBSTITUTE(TRIM(A1)," ","　")」
はA1セルに入力された値をTRIM関数を使って余分なスペースをまとめ、「" "」半角スペースを「"　"」全角スペースにしています。

その上で、もう一度SUBSTITUTE関数を使って「"　"」全角スペースを「CHAR(10)」セル内改行にしています。

文章で読んでいるだけだとなかなかわかりにくいでしょうから、いろいろな例を入力したサンプルファイルを用意しました。

▼サンプルファイル（003761.xls 28KByte）ダウンロード

サンプルファイルの、A列が元データでD列に今回の複数のスペースをセル内改行にする数式を入力してあります。

詳細は、A列には半角スペースや全角スペースを使って改行されていうように見せかけているデータ、B列には半角スペース・全角スペースを可視化するためにSUBSTITUTE関数を使った数式、C列にはどんなスペースが入力されているのかの備考、D列にはスペースをセル内改行にするさまざまな種類の数式、E列にはD列に入力されている数式、F列には補足が入力してあります。

D2:D3セルに全角スペースをセル内改行にする
「=SUBSTITUTE(TRIM(A2),"　",CHAR(10))」
D4:D5セルに半角スペースをセル内改行にする
「=SUBSTITUTE(TRIM(A4)," ",CHAR(10))」
D6:D7セルに半角スペースでも全角スペースでもOKな
「=SUBSTITUTE(SUBSTITUTE(TRIM(A6)," ","　"),"　",CHAR(10))」
という数式がそれぞれ入力してあります。