おつかれさまです。経営企画室のゆりえです。
エクセルの話をいたします。
私は、自分の目よりも自分が打ち込んだ計算機よりも、なによりエクセルの関数を信じていまして、「できることは全て関数で処理してしまいたい、改行さえも」と思っています。
そこで今回、「セル内で文章を改行させたい！ 逆に改行を取ったりしたい！ 関数で」というときの方法を、おもに３つの関数を使ってご紹介させていただきます。
※記事内の画像はExcel2016で作成していますが、Googleスプレッドシートにも対応しています。
セルの中で「改行」する方法
お題です。
画像のように、別々のセルに入った「りんご」と「みかん」というテキストを、ひとつのセルの中で改行して表示させたいときはどうすればよいでしょう。
関数を使わない場合
まず、関数を使わない場合も、セルの中で改行することができます。
「りんご」と入力したあとに、カーソルがセルの中で点滅している状態で、キーボードのAlt＋Enterを押すと、カーソルが１段下がり、改行ができるようになります。
※Macの場合、command＋Enter、もしくはcontrol＋Enter。
そのまま「みかん」を入力し、Enterで、無事改行されます。
このとき、エクセルでは、Enterしてもうまく改行されない場合がありますが、その際は、メニューの中の「折り返して全体を表示する」をオンにしてみてください。改行が反映されます。
改行の関数：CHAR(10)
たくさんデータがある場合や、「りんご」や「みかん」が「大根」と「ピーマン」に変わった場合にも、いちいち修正することなく改行したいときは、関数を使いましょう。
改行するには、CHAR(10)という関数を使うことで解決できます。
- 関数
- =CHAR(文字コード)
※Excel 2010 以降のWindows、Mac、タブレット等に対応
キャラクター関数と読みます。括弧()の中で指定した文字コードによって、該当する文字を表示する関数です。
文字コードは、「10」以外にもかなりたくさんのコードがあるそうなのですが、その中で「10」は「改行」を表す文字コードです。私のように非エンジニアで文字コードに無縁の場合は、CHAR()関数では「10」以外はほぼ使うことはありませんので、「10」だけ覚えておけばバッチリだと思っています！
使ってみましょう。結果を表示させたいセルに、=C2&CHAR(10)&C3 という数式を入力します。
エンターー！
成功。やった！
関数を使っていない場合と見た目は同じく、改行されたりんごとみかんが表示されました。とてもシンプルです。
これは、C2セルとCHAR(10)とC3セルを「&」でつなぐことで、「りんご 改行 みかん」を意味する数式となっています。
「りんご」や「みかん」というテキストを入力せずに同じ結果が得られました！
これで、「りんご」が「大根」に、「みかん」が「ピーマン」になった場合も、関数自体は何も変更することなく反映できるので、対象のデータが変動する場合は、関数を使ったほうが効率的です。
【応用】つなげる関数：TEXTJOIN()
今度は、改行を入れてつなげたいセルが2つではなくもっとたくさんある場合はどうでしょう。
セルが4個の場合、前述のやり方に従って、すべてをCHAR(10)でつないでみます。
入力する数式は、=C2&CHAR(10)&C3&CHAR(10)&C4&CHAR(10)&C5 となります。
セルをひとつずつ指定するのは大変ですが、4個くらいならまぁいけます。
でも、もし対象のセルが10個20個となったら・・・もーーっと数式が長くなり、わけがわからなくなります。私の眼は節穴なので、途中どこかで間違えていても判別がつかないですね。
そんなときは、テキストをつなげる関数その名も「TEXTJOIN」の出番です！
- 関数
- =TEXTJOIN(区切り文字 , 空白を無視するかどうか , つなげたいセルの範囲)
※適用対象：Excel 2016 以降のWindows、Mac、タブレット等に対応
※スプレッドシートの場合、下記のJOIN関数でも適用できます。
=JOIN(区切り文字 , つなげたいセルの範囲)
指定した範囲のセルとセルの値を、指定した区切り文字で１つにつなげることができます。セルの範囲はタテだけでなくヨコ方向の範囲にも対応していて、範囲が空白だったときは「無視をする」という離れ業もやってのけてくれます。
※空白を無視する場合は「TRUE」、無視しない場合は「FALSE」と入力します。
「複数のデータを関数を使って１つにつなげたい！」ということは多々あると思いますので、今回の「改行」に限らずさまざまな場面で活用できる関数です。
一般的な使いかた
「区切り文字」がわかりづらいと思いますので、一般的な使いかたをひとつご紹介します。
たとえば、区切り文字に「と」を指定します。
数式は、=TEXTJOIN(“と” , TRUE , AC2:C5) と入力します。
すると、「りんごとみかんといちごとバナナ」という結果が返されます。
４つのセルが区切り文字の「と」でつながれ、ひとつのテキストになりました。
このように、この関数の素敵なところは、ただセルとセルをつなげるだけでなく、「区切り文字」という、接続詞的なテキストを好きに入れられるところにあり、今回はまさにそれを使って改行をします！
改行をする
今度は「と」の代わりに、区切り文字として改行を示す「CHAR(10)」を指定します。
数式は、=TEXTJOIN(CHAR(10) , TRUE , C2:C5) となります。
すると、きちんと改行ができました！
魔法！！！
前述で１つひとつのセルを指定した、「=C2&CHAR(10)&C3&CHAR(10)&C4&CHAR(10)&C5」と同じ結果が返されました。
この方法でしたら、つなげる範囲が10個20個と大きくなっても、シンプルな数式で解決することができます。
セルの中の改行を削除したい
置換の関数：SUBSTITUTE()
反対に、改行が入っているセルの文字列を改行なしで表示させたいケースも多いと思います。
そんなときは、置換の関数「SUBSTITUTE」の出番です。
- 関数
- =SUBSTITUTE(対象のセル , 置換前の文字列A , 置換後の文字列B , 置換する位置) ※Excel 2010 以降のWindows、Mac、タブレット等に対応
セルの中のAをBに置き換えるということができ、改行以外にも色々な場面で使えて便利な関数です。今回は改行を削除したいので、
=SUBSTITUTE(改行をなくしたい対象のセル , CHAR(10) , “”) と入力しましょう。
ちなみに「””」とは、エクセルでは何もない状態のことを示します。この数式は、『改行を何もない状態に置換する』という意味になります。
（全ての改行を削除するので、４つ目のパラメータは省略します）
改行が消えて、１行の文字列に変わりました。
この関数のすごいと思うところは、削除と置換は同じという概念です。
何もないことを「””」という目に見える形で表現し、さらにこれまた目に見えない改行を「CHAR(10)」という文字コードで表すことで、本来目には見えない改行を何もないものへ置き換えるという、なんとなく哲学的な理系的なエモい感じを実感できます。
また、４つ目のパラメータに数字を入れると、複数ある対象の文字列のうち、その番号の順番に出てきた文字列のみを置換してくれます。
=SUBSTITUTE(改行をなくしたい対象のセル,CHAR(10),””,1)
・・・１つ目のCHAR(10)のみを削除してくれます。２つ目以降はそのまま改行が残ります。
これはちょっとわかりづらいですが、「〇番目のAだけをBにしたい！」というときには便利な機能です。
さいごに
関数を使って改行の追加や削除ができました。
今回ご紹介した他にももっとシンプルで効率的なやり方があるかもしれませんので、今後も日々勉強していきます。
