【Excel】エクセルで改行ごとにセルを分ける方法（関数・縦に分割）

Excelの1つのセルに氏名、住所、商品名などが改行区切りで入力されていると、並べ替えや集計、別システムへの貼り付けがしにくくなります。

このようなデータは、TEXTSPLIT関数を使うと改行ごとに縦方向へ分割でき、関数を使えない環境では区切り位置や置換を利用する方法も選べます。

サンプルでは、1行目を見出し行とし、A列のA2以降に改行を含む元データがあるものとして解説します。

改行は見た目では改行ですが、Excel内部では通常CHAR(10)として扱われる点も大切です。

 

Excelで改行ごとにセルを分ける方法【TEXTSPLIT関数】

行	A列 元データ	B列 分割結果
1	メモ	項目
2	りんご みかん ぶどう	ここから縦に展開

それではまず、TEXTSPLIT関数で改行ごとにセルを縦分割する方法について解説していきます。

最も手早い方法は、空いているセルに数式を1つ入力し、結果を下方向へスピル表示させる方法です。

TEXTSPLIT関数の基本数式

分割結果を表示したい先頭セルとしてB2を選択します。

次の数式をB2に入力してください。

TEXTSPLIT関数の第1引数は分割したい文字列であり、ここではA2です。

第2引数は列方向の区切り文字、第3引数は行方向の区切り文字です。

今回は横ではなく下へ分けたいため、第2引数を空欄にし、第3引数へCHAR(10)を指定します。

カンマを2つ続けている部分は、列方向には分割しないという指定になります。

入力を確定すると、B2にはりんご、B3にはみかん、B4にはぶどうが自動で表示されます。

数式をコピーしていないのに複数セルへ結果が広がる動きは、スピルと呼ばれます。

改行コードCHAR(10)の指定

セル内でAltキーを押しながらEnterキーを押して入れた改行は、Windows版Excelでは多くの場合CHAR(10)です。

そのため、改行文字を直接数式内に入力するよりもCHAR(10)で指定するほうが数式を確認しやすくなります。

改行の前後に余計な空白が含まれるデータでは、分割後の各セルに空白が残ることがあります。

その場合はTRIM関数を組み合わせるか、元データの空白を確認しましょう。

見えない改行コードを意識することで、改行ごとに分けられない原因も判断しやすくなります。

スピル範囲の確認と値への変換

TEXTSPLIT関数の結果が表示される範囲には、個別のセルごとに数式が入っているわけではありません。

先頭のB2だけに数式があり、B3以降はB2の結果として表示されています。

スピル先に文字や数式があると、結果が広がらずスピルエラーになります。

結果の下側にある不要な値を削除してから、数式を再入力してください。

分割結果を固定したいときは、B2から必要な範囲をコピーし、形式を選択して貼り付けから値を選びます。

値に変換すると元のA2を変更してもB列は変わらないため、提出用データや他のブックへ渡すデータに向きます。

 

複数行データを縦に分割する数式

行	A列 商品リスト	B列以降
1	商品リスト	分割結果
2	ノート ペン 消しゴム	縦方向へ展開
3	定規 はさみ	縦方向へ展開

続いては、複数行のデータへ数式を適用するときの考え方を確認していきます。

1件ずつ分割した結果を同じ列に連続表示したい場合と、元データの行ごとに別の列へ表示したい場合では、表の設計が変わります。

元データごとに別列へ分割する方法

A2の内容をB列へ、A3の内容をC列へというように、元データごとに分割結果の列を変える方法があります。

B2には =TEXTSPLIT(A2,,CHAR(10)) を入力し、C2には =TEXTSPLIT(A3,,CHAR(10)) を入力します。

この配置なら、元のデータ単位を保ったまま、各リストを縦に確認できます。

分割結果の長さが異なっても、各列ごとに独立してスピル表示されることが利点です。

ただし、後で一覧として集計する予定があるなら、項目を1列にまとめる方法のほうが扱いやすい場合があります。

FILTERXML関数を使う場合の注意点

TEXTSPLIT関数がないExcelでは、FILTERXML関数を応用して改行ごとに分ける例も見られます。

しかし、XMLで使えない文字が元データに含まれるとエラーになることがあり、数式も長くなります。

たとえばXML形式へ置き換えて抽出する式は、データの内容によって調整が必要です。

特に共有ファイルでは、利用者のExcelバージョンを確認しておくことが重要です。

TEXTSPLITが使える環境なら、短く読みやすい数式を優先しましょう。

空白行と重複データの扱い

セル内に連続した改行があると、分割後に空白のセルが含まれることがあります。

空白を無視したい場合は、TEXTSPLIT関数の第6引数にTRUEを指定します。

このTRUEは空の値を無視する指定であり、改行が2回続いた部分を結果に含めません。

空白行にも意味がある帳票データでは、TRUEを付ける前に元の構造を確認してください。

重複を除去したい場合は、TEXTSPLITの結果をUNIQUE関数で囲む方法もあります。

 

区切り位置による改行データの分割

行	A列 元データ	B列	C列
1	氏名と所属	氏名	所属
2	田中花子 営業部	田中花子	営業部

続いては、関数を使わずに区切り位置の機能で改行データを分割する方法を確認していきます。

区切り位置は元のセルを直接分割する操作なので、元データを残したい場合は事前に別の列へコピーしてください。

区切り位置を開く操作

まず、改行で分けたいセル範囲を選択します。

Excelのデータタブを開き、データツールにある区切り位置をクリックします。

区切り位置指定ウィザードが表示されたら、最初の画面で区切り文字によってフィールドを区切るを選択し、次へ進みます。

選択範囲が複数行でも、各セル内の改行を同じ区切り文字として処理できます。

その他の区切り文字への改行入力

次の画面では、区切り文字の一覧からその他にチェックを入れます。

その他の入力欄にカーソルを置き、Ctrlキーを押しながらJキーを押してください。

入力欄は空白に見えることがありますが、内部には改行文字が入力されています。

Ctrl＋Jはセル内改行を区切り文字として指定する代表的な操作です。

プレビュー画面に縦線が現れ、氏名と所属が別の列に分かれて見えれば設定は成功です。

文字列が数値や日付に見える場合は、最後の画面で列のデータ形式も確認しましょう。

横分割した結果を縦に並べ替える操作

区切り位置でB列、C列、D列に分割された結果を縦1列にしたいときは、結果範囲をコピーします。

空いているセルを右クリックし、形式を選択して貼り付けを選びます。

表示された画面で行列を入れ替えるにチェックを入れて貼り付けると、横のデータが縦方向に変わります。

元のデータを残すなら、貼り付け先は十分に空いた場所を選ぶことが必要です。

1セルだけを縦に分割する用途ならTEXTSPLIT関数のほうが短い手順ですが、古いExcelではこの操作が役立ちます。

 

Power Queryによる改行区切りの展開

行	A列 注文内容	B列 展開後
1	注文内容	品目
2	コーヒー 紅茶 緑茶	1品目ずつの行

続いては、大量データを繰り返し処理するときに便利なPower Queryによる展開を確認していきます。

Power Queryは取り込み手順を記録できるため、毎月届く同じ形式のデータを整形する場面に向きます。

テーブルからPower Queryを起動する操作

まず元データの範囲を選択し、挿入タブのテーブル、またはCtrlキーとTキーでテーブル化します。

先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認します。

次にデータタブからテーブルまたは範囲からを選択すると、Power Queryエディターが開きます。

Power Queryでは元のワークシートを直接壊さず、変換結果を別表として出力できます。

一度作った手順は更新で再実行できるので、定型処理の時間短縮につながります。

列の分割と行への展開

Power Queryエディターで改行を含む列を選択します。

変換タブまたはホームタブから列の分割を選び、区切り記号による分割をクリックします。

区切り記号の選択でカスタムを選び、改行文字を指定します。

詳細オプションでは、列への分割ではなく行への分割を選べる環境があります。

同じ注文番号や顧客名の列は、展開された各行へ引き継がれるため、明細形式の表を作るときに便利です。

読み込み後の更新と確認

変換が終わったら、ホームタブの閉じて読み込むを選択します。

新しいワークシートへ結果を出力すれば、元データと加工済みデータを分けて管理できます。

次回は元テーブルの内容を差し替え、クエリの更新を実行するだけで同じ分割処理を繰り返せます。

Power Queryの更新前には、出力先の表を手で編集しないことが大切です。

単発の小規模データなら関数、継続的な大量データならPower Queryという使い分けが現実的です。

 

改行分割で発生しやすいエラーと対処

現象	主な原因	確認方法
分割されない	改行コードが異なる	CHAR(10)の指定を確認
スピルエラー	展開先にデータがある	下方向のセルを空ける
空白が残る	改行前後にスペース	TRIM関数を検討

続いては、改行ごとのセル分割でつまずきやすい問題と、その対処を確認していきます。

データの作成元や貼り付け元によって改行の種類が異なるため、同じ数式でも期待どおりにならないことがあります。

TEXTSPLIT関数が表示されない場合

TEXTSPLITと入力しても候補が出ない場合は、利用しているExcelのバージョンが対応していない可能性があります。

Microsoft 365でも更新チャネルや組織の設定によって、利用できる関数に差が出る場合があります。

関数名が認識されないときは、数式のミスより先にバージョンを確認しましょう。

対応していない場合は、区切り位置、Power Query、または別の新しいExcel環境での処理を検討します。

共有先が古いExcelを使う場合には、結果を値として貼り付けてから渡すと安心です。

改行ではなく空白や記号で分かれる場合

WebページやPDFからコピーしたデータには、通常の改行ではなく別の制御文字が混ざることがあります。

数式が反応しないときは、セルを編集モードにして改行位置を確認します。

必要に応じてSUBSTITUTE関数で不要な文字を置き換えてから、TEXTSPLIT関数へ渡します。

この式はCHAR(13)を空文字に置き換えた後、CHAR(10)で縦方向に分割します。

Windowsと他のシステム間で受け渡したデータでは、改行コードが組み合わさるケースがあるため有効です。

分割後の前後空白を整える方法

各項目の先頭や末尾に半角スペースが付いていると、見た目が同じでも検索や照合で一致しない場合があります。

分割結果を整えるには、TRIM関数やCLEAN関数を組み合わせる方法があります。

TRIM関数は連続した半角スペースを整理し、文字列の前後にある不要な半角スペースを取り除きます。

全角スペースはTRIM関数だけでは処理しきれない場合があるため、必要ならSUBSTITUTE関数で置換します。

 

まとめ エクセルで改行ごとにセルを分ける方法

Excelで改行ごとにセルを分けるなら、新しいExcelではTEXTSPLIT関数を使う方法が分かりやすく、縦に分割する数式は =TEXTSPLIT(A2,,CHAR(10)) です。

この数式では、A2のセル内改行をCHAR(10)として指定し、結果を下方向へ自動展開できます。

分割先に値があるとスピルエラーになるため、結果が広がる範囲を空けておくことが基本です。

TEXTSPLIT関数が使えないExcelでは、データタブの区切り位置でCtrlキーとJキーを使い、横に分割した後で行列を入れ替える方法が役立ちます。

定期的に届く大量データを処理するなら、Power Queryで改行区切りを行へ展開し、更新で同じ変換を繰り返す方法も便利です。

空白行を除きたい場合はTEXTSPLIT関数の第4引数へTRUEを指定し、不要なスペースがある場合はTRIM関数やSUBSTITUTE関数を組み合わせましょう。

元データを残す必要があるか、分割後に集計するか、Excelのバージョンは何かを確認して、目的に合う方法を選ぶことが大切です。