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

Excelを使っていると、1つのセルに複数行のテキストが入力されていて「これを行ごとに分けたい！」と思ったことはないでしょうか。

たとえば、住所や商品名、アンケートの回答など、改行で区切られたデータを縦方向に分割したい場面は意外と多いものです。

しかし、手作業でコピー＆ペーストを繰り返すのは非常に手間がかかり、データ量が多いほど現実的ではありません。

そこで今回は、【Excel】エクセルで改行ごとにセルを分ける方法（縦に分割・セルを分ける関数）について詳しく解説していきます。

関数を使った方法や「区切り位置」機能を活用した方法など、複数のアプローチを紹介しますので、ご自身の状況に合った方法を見つけてみてください。

改行ごとにセルを分けるには「区切り位置」または関数を使うのが最速

それではまず、改行ごとにセルを分ける方法の全体像について解説していきます。

Excelでセル内の改行（Alt＋Enterで入力された改行）を基準にデータを縦に分割する方法は、大きく分けて2つのアプローチがあります。

1つ目は「区切り位置」機能を使う方法で、Excelの標準機能として備わっており、関数の知識がなくても操作できるのが特徴です。

2つ目は、TEXTSPLIT関数やFILTERXML関数などの関数を組み合わせる方法で、より柔軟に自動化・効率化できる点が魅力です。

どちらの方法が向いているかは、データ量や用途によって異なります。

また、セル内改行は文字コードとしてCHAR(10)という値で表されています。

関数で改行を扱う際には、このCHAR(10)を区切り文字として指定することがポイントになります。

以下の表で、2つの方法の特徴を比較してみましょう。

方法	難易度	自動更新	向いているケース
区切り位置機能	低い	×（手動）	一度限りの分割作業
TEXTSPLIT関数	中程度	○（自動）	Excel 365・2021以降のユーザー
FILTERXML関数	高い	○（自動）	旧バージョンのExcelを使っている方

まずは自分が使っているExcelのバージョンを確認した上で、最適な方法を選ぶようにしましょう。

「区切り位置」機能を使って改行ごとにセルを分ける手順

続いては、「区切り位置」機能を使った改行分割の方法を確認していきます。

この方法は関数の知識が不要で、Excelの標準機能だけで完結するため、初心者の方にもおすすめです。

ただし、注意点として「区切り位置」機能は横方向（列方向）への分割が基本となっています。

改行ごとに縦（行方向）に分けたい場合は、いったん横に分割してから行列を入れ替えるひと手間が必要です。

手順① 対象のセルを選択してデータタブを開く

まず、分割したいセルを選択した状態で、Excelのリボンから「データ」タブをクリックします。

次に「データツール」グループの中にある「区切り位置」ボタンをクリックしましょう。

ウィザード形式のダイアログが表示されますので、「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択して「次へ」を押します。

手順② 区切り文字に「その他」でCHAR(10)を指定する

区切り文字の選択画面では、「その他」にチェックを入れます。

その入力欄にCtrl＋Jを入力してください。

これはCHAR(10)、すなわちセル内改行を意味するキーボードショートカットです。

入力しても見た目上は何も表示されませんが、正しく認識されているので安心してください。

手順③ 完了後に行列を入れ替えて縦に並べる

ウィザードを完了させると、改行で区切られたデータが横方向（列ごと）に分割されて出力されます。

縦に並べたい場合は、分割後のセル範囲をコピーし、別のセルで右クリックして「形式を選択して貼り付け」→「行列を入れ替える」を選択しましょう。

これにより、横に並んでいたデータが縦一列に並び替わります。

TEXTSPLIT関数で改行ごとにセルを縦に分割する方法

続いては、TEXTSPLIT関数を使ってセルを縦に分割する方法を確認していきます。

TEXTSPLIT関数はExcel 365およびExcel 2021以降で使用できる比較的新しい関数です。

関数ひとつで分割ができるため、作業の効率化と自動化に非常に役立ちます。

TEXTSPLIT関数の基本構文

TEXTSPLIT関数の基本的な構文は以下のとおりです。

改行ごとに縦（行方向）に分割したい場合は、第3引数「行の区切り文字」にCHAR(10)を指定します。

第2引数（列の区切り文字）は省略するか、空文字列を入力してください。

この数式を入力すると、A1セルの内容が改行ごとに自動的に縦方向へ展開されます。

空白セルが生じる場合の対処法

データによっては、改行が連続していたり末尾に余分な改行があったりして、空白セルが発生してしまうことがあります。

その場合は、第4引数に「TRUE」を指定することで空白を無視した分割が可能です。

これにより、余分な空白セルを排除したきれいな分割結果を得ることができます。

TEXTSPLIT関数が使えないバージョンへの代替策

Excel 2019以前やExcel 2016などの旧バージョンでは、TEXTSPLIT関数は使えません。

その場合は、FILTERXML関数とSUBSTITUTE関数を組み合わせる方法が有効です。

こちらはやや複雑な数式ですが、ROW関数と組み合わせて行番号に応じた値を順番に取り出す仕組みになっています。

数式を縦方向にコピーすることで、改行ごとに分割されたデータを取得できます。

FILTERXML関数を使って改行ごとに分割する応用的な方法

続いては、FILTERXML関数を活用した応用的な分割方法を確認していきます。

FILTERXML関数は本来XMLデータを扱うための関数ですが、配列として複数の値を一度に返せるという特性を活かして、テキスト分割に応用することができます。

Excel 2013以降であれば利用可能なため、TEXTSPLIT関数が使えない環境でも動作します。

FILTERXML関数の基本的な使い方

FILTERXML関数を使ってセル内改行でテキストを分割する数式は以下のとおりです。

この数式の仕組みは、セル内のテキストをXML形式の文字列に変換し、そこから各要素を取り出すというものです。

SUBSTITUTE関数でCHAR(10)をXMLタグに置換することで、改行がXMLの要素区切りとして機能するようになります。

スピル機能との組み合わせで縦に展開する

Excel 365やExcel 2019以降では、スピル機能によってFILTERXML関数の結果が自動的に縦方向へ展開されます。

数式を1つのセルに入力するだけで、改行の数だけ下のセルに結果が広がるため、とても便利です。

スピル機能が使えない旧バージョンでは、INDEX関数と組み合わせて1行ずつ取り出す方法を使いましょう。

FILTERXML関数使用時の注意点

FILTERXML関数を使う際には、いくつかの注意点があります。

まず、テキスト内に「<」「>」「&」などのXML特殊文字が含まれていると正しく動作しないことがあります。

そのような場合は、事前にSUBSTITUTE関数でそれらの文字を別の文字に置換しておく処理が必要です。

関数	対応バージョン	記述の簡単さ	スピル対応
TEXTSPLIT	Excel 365・2021以降	◎（シンプル）	○
FILTERXML	Excel 2013以降	△（やや複雑）	○（365・2019以降）
MID・FIND組み合わせ	全バージョン	×（複雑）	×

使用しているExcelのバージョンと、データの特性を踏まえた上で最適な関数を選ぶことが大切です。

まとめ

今回は、【Excel】エクセルで改行ごとにセルを分ける方法（縦に分割・セルを分ける関数）について解説しました。

Excelのセル内改行（CHAR(10)）を基準にデータを分割する方法には、大きく「区切り位置機能」「TEXTSPLIT関数」「FILTERXML関数」の3つがあります。

Excel 365・2021以降を使っている方はTEXTSPLIT関数がもっともシンプルでおすすめです。

旧バージョンの場合は、FILTERXML関数やMID・FIND・SUBSTITUTE関数の組み合わせで対応できます。

どの方法も、セル内改行がCHAR(10)で表されているという基本を理解しておくことが重要なポイントです。

今回紹介した方法を活用して、データ整理の効率をぜひ上げてみてください。