はじめに
Excelのセル内で改行するために、スペースがいくつも連打されていることがあります。
これは、入力中の行を右端までスペースで埋めて、次の行へ押し出すことで改行「っぽく」見せるために行われます。
その特性上、行の幅が変わると形が崩れてしまう、コピー&ペーストした際に大量のスペースが入ってしまうといったことが起こります。
そのようなことを起こさないためには、スペースをきちんと改行に置き換えれば良いのです。
この記事ではSUBSTITUTE関数、TRIM関数、CHAR関数を組み合わせてスペースを改行に置き換える数式について解説します！
後半は単体のスペースは残して、複数のスペースのみを改行に置換する数式について解説しています。
ちょっと難解ですが、興味のある方は読んでみて下さい！
文字列内のスペースを改行に変換する数式
ぬあ～～っ！？ スペース連打で改行が入力されているっ…！！
これ全部手作業で直すの…？本当に…？？
ああ、これはセル内で改行する方法が分からなくてスペース連打で改行っぽく見せてるやつですね。
私もよくやってたっけ。
そりゃ私もやったことはありますけど…！
懐かしんでいる場合ではないんですよ！
これって今の列幅だから改行して見えますけど、列の幅を広げたら崩壊しちゃうじゃないですか！
その場の見た目を整えているだけですからね。
これって数式でなんとかなったりしませんか？
できますよ！
以前対応したときに書いた数式を教えますね！
スペースをかたまり毎に改行に変換する
まずはスペースがあれば、かたまり毎に改行に変換する数式を紹介します！
この数式ではSUBSTITUTE関数とTRIM関数が活躍します！
SUBSTITUTEちゃんといえば、文字列の置換が得意な関数ちゃんでしたよね！
その通りです！
そして複数連続した空白を空白1つに整えてくれる、TRIM関数と協力してもらいます！
数式に私が入れば、もう大丈夫！ 安心して良いよ！
<例で使用している数式>
=SUBSTITUTE(TRIM(B2)," ",CHAR(10))
難しそうな数式だけど、SUBSTITUTEちゃんを知ってるから少し安心できるかも…。
このままではちょっと分かりにくいので、分けて考えましょう。
まず変換一段回目、ここではTRIM関数を使って複数連続したスペースを1つのスペースに整形します
どうしてスペースを1つに整形する必要があるんですか？
それはね、二段階目の変換で私がスペース1個を改行1個に置換しちゃうからだよ！ もしも連続したスペースを全て改行に変換しちゃったら…？
はｯｯ！！大変なことになります！
そういうことです。
SUBSTITUTEちゃんがもう説明してくれましたけど、変換二段階目ではスペース1個を改行1個に置換します。
これで、スペースのかたまりごとに改行に置換することができました。
改行…って数式のどこにあるんですか？
あ…説明し忘れていましたね。
数式中の『CHAR(10）』が改行です。
CHAR関数は引数の文字コードに対応した文字列を返すExcel関数です。
引数に10を設定することで、文字コード10の「改行」を返すということです。
改行は目に見えないから、てっきりどこかに潜んでいるのかと…。
私は半角スペースとCHAR(10)が返す改行を置換してるんだよ！
これで今回の作業は何とかなりそうです！
シノさん！SUBSTITUTEちゃん！ありがとう！
それは良かったです！
TRIM関数について少し補足しておくと、全角と半角のスペースが混ざっている場合、整形後に残るのが先頭のスペースという性質があるため、整形後の文字列に全角と半角のスペースが混在してしまうというケースが考えられます。
たしかにスペース連打の文字列なので、全部半角に統一されているとは言い切れないです…。
そんな場合は、ASC関数をネストに加えてSUBSTITUTEちゃんで置換する前の文字列に含まれるスペースを全て半角に統一してしまいましょう！
ASC関数は全角の文字列を半角に変換するExcel関数です。
<例で使用している数式>
=SUBSTITUTE(ASC(TRIM(B3))," ",CHAR(10))改行に変換されなかった全角スペースが改行になって消えていますね！
ASC関数は置換前ならどこへ入れても良いんですか？？
今回のケースだと、TRIMの前でも後でも結果は同じにるのでどちらでも大丈夫ですよ。
目に見えない分、注意が必要なんですね…気をつけます！
文字列内の複数連続したスペースのみ改行に変換する数式
ここからは完全に余談！！
スペースが2つ以上の場合だけ改行にしたいケースの数式を紹介します。
スペースが1つだけの場合は、そのスペースが残るということでしょうか？
その通りです！
スペース1個のときは改行せずに残したい区切りだったりしますよね
…ということで考えてみました。
必要は発明の母、だね！！
次の画像をご覧ください！
置換の過程を分解したのが下の画像です。
たしかに1つだけのスペースは残したまま2つ以上のスペースが改行に置き換えられていますね！
これは役に立ちそうです。
でしょう？？
では、セルに入った数式を見てみましょう～！
ドンッ！
<例で使用している数式>
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C$3," ","★")," ","☆"),"★☆","★"),"★"," "))," ",CHAR(10)),"☆"," ")で、でた…私もう帰りたいです。
まぁまぁ、そう言わずに聞いてみて下さいな。
まず前提として数式中の★と☆は元の文字列に含まれない文字で、同一の文字でなければ何でも良いです
元の文字列に★や☆を含む場合は別の記号などを使うと良いでしょう。
この★と☆はどんな役割があるんですか？
スペースに属性をつけて一旦かわしておく役割があります。
表の左側の数字、3の状態を見てみましょう！
1つ以上の★は2つ以上連続したスペースを表し、★☆は2つ以上のスペースの後にくっついた単体のスペース、つまり消しても良い単体のスペースを表しています。
この状態にした理由が、よく分からないです…！
単体のスペースを意味する☆ですが、★☆となっているものは2つ以上のスペースに連なっているスペースなので、改行に置換する対象としても良いという風に区別することができます。
そのために、このような置換を行っています。
分かったような分からないような…
まあまあ、とにかく見ていきましょう！
4の置換では★☆を★に置き換えることで、複数のスペースに連なった単体のスペースを削除して、置換後の☆は削除してはいけない単体のスペースという状態に持っていきます！
あとは★のひとかたまりを1つの改行にすれば良い…ということですか？
その通りです！
★のままではTRIM関数で綺麗にできないので、★は半角スペースに置換してしまいます！
5の状態は、さっきスペースを改行に変換する前の状態と同じですね！
はい、あとは6でTRIM関数を使って連続したスペースを単体のスペースに変換、7で半角スペースを改行(CHAR(10))に置換、最後に消さないよう☆に置換していた単体のスペースをスペースに戻してあげます！
うわ～っ、長かったですねえ～～。
私も1つの数式で6回も出番があると、目が回りそうです…。
これはもっと上手な数式がある気がするんですけどね。
今の私では、これが精一杯です。
置換機能を使って前処理をすれば、数式はもっと短くて済みますよ！
Excelの数式って、本当に色々できるんですね！
色んな数式を見て、発想の引き出しを増やしていきましょう！
数式なら失敗しても元のデータは変わらないから、何度でもトライしてみてね！
おわりに
『スペース連打改行』に立ち向かう数式を紹介してみました。
複数のスペースのみを改行にする数式、解説がかなり難解になってしまいました。
本当はもうひとつ、『○個以上連続したスペースを改行に変換する』数式が書きたかったのですが、実力不足で叶わず…。
本当に無念です。
どなたかExcel2016環境で動く数式が思いついたら教えてください。
書けなかった数式を考えるのに随分な時間を使ってしまい、ブログの更新が滞ってしまいました。
更新はこれからも続けていきますので、宜しくお願いします！
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント