Introduction
Ver. all 2013 2016 2019 2021 365
エクセルに入力されたデータを、関数を使用して改行する方法です。
指定した文字数の場所で改行するやり方を主に紹介します。
例えば、下の画像のように左から８文字目から常に改行したかった場合です。
また、FIND関数を使用して特定の文字が出てきた時に改行したり、IF関数を使用して10文字を超えたら自動で改行するようにしたりもできますよ。
CHAR関数を使うにゃ
決められた文字数の場所で改行する方法
改行にはCHAR関数を使用します。
CHAR関数・・・数値で指定した文字を返す
CHAR（数値）
- 数値・・・変換する文字を表す数字を指定
CHAR関数は、コンピュータに保存されている文字セットの中から、必要な文字を数字（文字コード）で指定することができます。
改行の文字コードは「10」
他にもタブや改ページなどの文字コードもあります。
とりあえず使用機会がそれなりに多い、「改行＝10」とだけ覚えておくのがおすすめです。
そのCHAR関数を使用して、文字列の途中で改行を挿入するのですが、その例として２つ紹介します。
- REPLACE関数を使う方法
- RIGHT関数、LEFT関数、LEN関数を組み合わせる方法
REPLACE関数を使用する方が簡単なのですが、もう一つのやり方の方が応用が効くかもしれません。
REPLACE関数を使用して改行する方法
今回は、下のデータの郵便番号の次からを改行して二行目にしてみます。
郵便番号は８文字なので、その次の９文字目に改行を挿入します。
使用するのはREPLACE関数。
特定の位置の文字を、別の文字に置き換えることができる関数です。
置き換える文字数の場所に「0」を指定することで、置き換えではなく挿入として扱います。
抜き出し先のセルを選択してから［関数の挿入］ボタンをクリック。
［関数の分類］を［文字列操作］か［すべて表示］に切り替えて、［REPLACE］を選択してOK。
REPLACE関数・・・指定した文字列を、他の文字列に置き換える。
REPLACE（文字列 , 開始位置 , 文字数 , 置換文字列）
- 文字列・・・置き換える文字列（対象セル）を指定
- 開始位置・・・置き換えたい文字の先頭の位置を数字で指定
- 文字数・・・置き換える文字列の文字数を指定
- 置換文字列・・・置き換える文字列を指定
今回は、下のように設定します。
- ［文字列］・・・変換対象のセルを指定
- ［開始位置］・・・8文字目の次から処理したいので「9」を入力
- ［文字数］・・・削除は必要ないので「0」を入力
- ［置換文字列］・・・削除位置を改行に置き換えたいので「CHAR(10)」を入力
少し指定が多いですね。左側の説明を確認しながら入力しましょう。
結果の画像が下なのですが、改行されているようには見えませんね。
これはセル表示の設定の問題です。
とりあえず、下にオートフィル機能を使用してコピーしておいて、
［ホーム］タブ → ［折り返して全体を表示する］をクリック。
これで改行が目に見えるようになります。
しかし、列幅は自動で広がってくれないので恰好悪いですね。
列幅を少し広げてあげましょう。
これで２行になりましたが、広がった行の高さがそのままです。行高が無駄に広いですね。
狭くしましょう。
行番号を引っ張って行選択して、
どこでも良いので、行番号の間にマウスを移動。
マウスポインタが下のように上下矢印になったらダブルクリックです。
これで文字サイズに合った行高に自動調整されます。
作業終了です。
LEFT関数やRIGHT関数を使用して改行する方法
前の説明と同じデータを使用して、郵便番号の次からを改行して二行目にしてみます。
郵便番号は、全て８文字なので、８文字で改行することになります。
流れは下のようになります。
- 左から８文字を抜き出す
- 改行を入れる
- 全体の文字数から８文字引いたものを抜き出す
完成する式は、下のようになります。
その中でも大切なのは緑下線の部分。
この CHAR(10) が改行の部分になります。その改行を間に入れるように、「&」演算子を使用して前後のデータを結合します。
式が長いので順番に見ていきましょう。
では、まず左から８文字抜き出しましょう。LEFT関数を使用します。
抜き出し先のセルを選択してから［関数の挿入］ボタンをクリック。
［関数の分類］を［文字列操作］か［すべて表示］に切り替えて、［LEFT］を選択してOK。
LEFT関数・・・文字列の先頭（左）から指定された数の文字を返す
LEFT（文字列,文字数［省略可］）
- 文字列・・・取り出す文字を含むセルを指定
- 文字数・・・取り出す文字数を指定
引数は下のように設定します。［文字列］の場所は対象となる「A1」をクリックして指定。［文字数］は８文字抜き出したいので「8」と入力。
［OK］をクリックすると、郵便番号部分が抜き出されます。
では、この後に改行を入れます。使用するのはCHAR関数。
まず、改行を繋ぐために「&」演算子を入力しておきます。
続いて［関数の挿入］ボタンをクリック。
［関数の分類］を［文字列操作］か［すべて表示］に切り替えて、［CHAR］を選択してOK。
CHAR関数・・・数値で指定した文字を返す
CHAR（数値）
- 数値・・・変換する文字を表す数字を指定
CHAR関数は、コンピュータに保存されている文字セットの中から、欲しい文字を数字で指定することができます。
改行の文字コードは「10」
ということで、CHAR関数の引数には改行を指示する「10」を入力して［OK］をクリック。
これで、先ほどの郵便番号の後に改行がくっついた事になります。
見た目で改行されていない気がしますが、そのまま続けていきます。
最後に郵便番号より後ろの文字を追加する必要がありますね。
使用するのはRIGHT関数とLEN関数。
RIGHT関数は右側から文字を抜き出す関数です。また、LEN関数は対象セルの全体の文字数を数えてくれます。
では、「&」を入力してから［関数の挿入］ボタンをクリック。
［関数の分類］を［文字列操作］か［すべて表示］に切り替えて、［RIGHT］を選択してOK。
RIGHT関数・・・文字列の最後（右）から指定された数の文字を返す
RIGHT（文字列,文字数［省略可］）
- 文字列・・・取り出す文字を含むセルを指定
- 文字数・・・取り出す文字数を指定
引数は、下のように設定します。
ポイントは、［文字数］の場所ですね。
ここでLEN関数を使用して全体の文字数を算出してから、郵便番号分の８文字を引き算します。
LEN関数・・・文字列の文字数を返す
LEN（文字列）
- 文字列・・・文字数を調べるセルを指定
この場所全体の式をまとめると、
全体の文字数から郵便番号を引き算した文字数を、セルの右側から取り出す
ということになります。
最終的に完成した式は下のようになります。
=LEFT(A1,8)&CHAR(10)&RIGHT(A1,LEN(A1)-8)
結果の画像が下なのですが、改行されているようには見えませんが実際には改行されています。
［ホーム］タブ → ［折り返して全体を表示する］を押してから、列幅と行の高さを適切に設定しましょう。
前述したREPLACE関数の後半で説明しています。
〇文字以上の場合や、特定の文字で改行する方法
決められた文字以上で改行したい場合と、特定の文字が出現したら改行したい場合を簡単に紹介します。
〇文字以上の場合にしたい時はIF関数を使いましょう。
下の画像は、15文字までを1行目にして、それ以降16文字目から2行目にする例です。
「C1」に入っている関数は下のようになります。
=IF(RIGHT(A1)>=16,REPLACE(A1,16,0,CHAR(10)),A1)
「もし、左から数えた文字が16文字以上だった場合は改行したデータを表示しなさい。そうでなければ、そのままA1のデータを表示しなさい」
という意味です。
「改行したデータを表示しなさい」の場所は、最初に説明したREPLACE関数のパターンを使用しています。
特定の文字が出てきたら改行したい場合は、FIND関数を使用します。
下の画像は、「都」の文字の次から改行するように設定しています。
「C1」に入っている関数は下のようになります。
=IF(FIND("都",A1,1),REPLACE(A1,FIND("都",A1,1)+1,0,CHAR(10)),A1)
「もし、「都」の文字が見つかったら、次の場所で改行しなさい。見つからなかったら、そのままA1を表示しなさい」
という意味になります。
ポイントは、赤文字にした「+1」です。
「都」を発見した次の位置で改行したいので、「+1」する必要があります。
FIND関数は下のリンク先で説明していますので参考にしてください。
まとめ
２つの方法を比べると、REPLACE関数を使用した方がかなり楽ですね。
とりあえず改行が「CHAR(10)」で可能なことを押さえておけば、他の関数と組み合わせて様々な状況に対応できます。
改行以外の文字コードは、マイクロソフトのサポートページで紹介されています。
このページの最後、「ASCII 非印字制御文字表」に改行も載っていますよ。
行の高さの調整が面倒だにゃ