Excelの数式で区切り文字を基準にセルを分割する8つの方法

このチュートリアルでは、Excelの数式を使って、区切り文字（デリミタ）を基準にセルを分割する方法を詳しく解説します。区切り文字とは、テキスト文字列内のデータのかたまりを区切る役割を持つ記号や文字のことです。本記事では、状況に応じて使い分けられるさまざまな数式による分割手法を紹介します。

解説を始める前に、今回使用するサンプルデータについて確認しておきましょう。

サンプルデータは学生情報（名前・ID・コース・都市）を使用します。このデータをもとに、異なる状況で活用できる複数の方法をご紹介していきます。

各手法の実例は、それぞれ別のシートに保存されています。

練習用ワークブックのダウンロード

以下のリンクから、練習用ワークブックをダウンロードできます。

数式で区切り文字によりセルを分割する8つの方法

Excelでセルを分割する必要が生じる場面は少なくありません。たとえば、インターネットやデータベースからコピーしたデータ、同僚から受け取ったファイルなどが該当します。氏名がフルネームで入力されており、姓と名に分けたい場合などは、セル分割が必要になる典型的な例といえるでしょう。

1. 文字列関数とSEARCH関数を組み合わせてハイフン区切りのテキストを分割する

区切り文字で分割する際に最初に行うべきことは、区切り文字そのものの位置を特定することです。位置さえわかれば、その左右どちらからでも簡単に値を取り出せます。ここではSEARCH関数で区切り文字の位置を見つけ、LEFT・MID・RIGHT関数を使ってテキストから値を抽出します。

1.1. LEFT関数とSEARCH関数を組み合わせる

まず最初の値を取り出す方法です。LEFT関数には「対象のテキスト」と「抽出する文字数」の2つの引数があります。テキストはそのまま指定し、文字数の部分にはSEARCH関数を使って区切り文字の位置を求めます。

手順：

• まず、結果を表示するセルを選択し、次の数式を入力します。

=LEFT(B5, SEARCH("-",B5,1)-1)

• キーボードのEnterキーを押します。

• フィルハンドルを下方向へドラッグして、範囲全体に数式をコピーします。または、プラス（+）マークをダブルクリックしてオートフィルでも可能です。

• これで結果が表示されます。

🔎 数式の仕組み：

この例では、区切り文字としてハイフン「–」を使用しています。SEARCH関数によってハイフンの位置が返されます。ハイフン自体は不要なので、「ハイフンより前の部分」だけを抽出するというわけです。

1.2. MID関数とSEARCH関数を組み合わせる

次に、中間の値を取り出します。ここではMID関数とSEARCH関数を組み合わせます。以下の手順に従ってください。

手順：

• まずセルを選択し、次の数式を入力します。

=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)

• Enterキーを押します。

• フィルハンドルを下にドラッグして数式をコピーします。あるいはプラス（+）記号をダブルクリックしてオートフィルしても構いません。

• これですべての中間の値が分割されました。

🔎 数式の仕組み：

SEARCH関数は、あるテキスト文字列が別の文字列内のどの位置にあるかを返します。ここでは、ハイフンの次の文字から検索を開始するようにしています。MID関数は、指定した開始位置から、指定した文字数分の文字を取り出します。

1.3. RIGHT関数・LEN関数・SEARCH関数を組み合わせる

最後の値を取り出すには、RIGHT・LEN・SEARCHの3つの関数を組み合わせます。以下の手順でセルを分割してみましょう。

手順：

• まずセルを選択し、次の数式を入力します。

=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1))

• Enterキーを押します。

• 続いて、フィルハンドルをドラッグして数式を範囲にコピーします。または、プラス（+）記号をダブルクリックしても同じ結果が得られます。

• これで最後の値も区切り文字によって分割されました。

🔎 数式の仕組み：

LEN関数は文字列全体の長さを返します。そこから最後のハイフンの位置を引くことで、「最後のハイフン以降の文字数」が算出されます。RIGHT関数はその文字数分だけ右端から文字を取り出します。

注意： 同様の手順で、他の任意の文字でも列を分割できます。「–」の部分を目的の区切り文字に置き換えるだけでOKです。

関連記事： カンマで文字列を分割するExcel数式（5つの例）

2. 数式を組み合わせて改行コードでテキストを分割する

改行で区切られた文字列を分割する場合も、前章とほぼ同じ数式が使えます。ただし、ひとつ追加する関数があります。それがCHAR関数です。

2.1. LEFT・SEARCH・CHAR関数を組み合わせる

CHAR関数は改行コードを表す文字を返します。先頭の値を取り出すために、LEFT・SEARCH・CHAR関数を組み合わせましょう。

手順：

• 前の方法と同様に、まずセルを選択し、先頭の値を抽出するための次の数式を入力します。

=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

• Enterキーを押して結果を確認します。

• さらに、プラス記号をドラッグすれば、数式をコピーして範囲全体の結果を取得できます。

🔎 数式の仕組み：

改行のASCIIコードは「10」です。CHAR関数に10を渡すことで改行文字を生成し、それを検索しています。CHAR関数は数値に対応する文字を返すため、これで改行の位置が特定でき、先頭の値を取り出せるのです。

2.2. MID・SEARCH・CHAR関数を組み合わせる

中間の値を取り出す場合は、以下の手順に従います。

手順：

• 他の方法と同じく、まずセルを選択し、次の数式を入力します。

=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)

• Enterキーを押して結果を表示します。

• さらに、プラス記号をドラッグして数式をコピーすれば、指定した範囲すべての結果が得られます。

2.3. RIGHT・LEN・CHAR・SEARCH関数を組み合わせる

末尾の値については、RIGHT・LEN・CHAR・SEARCH関数を組み合わせた数式を使用します。最下部の値を取り出すには、以下の手順に従ってください。

手順：

• 前の手法と同様にセルを選択し、最下部の値を抽出するための次の数式を入力します。

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))

• キーボードのEnterキーを押します。

• 最後に、プラス記号をドラッグして数式を複製すれば、指定範囲の結果がすべて取得できます。

関連記事： Excelでセルを分割する5つの簡単なテクニック

3. 「文字＋数字」パターンのセルを分割する

ここでは、「文字列＋数字」という形式のデータを分割する方法を見ていきます。説明をシンプルにするために、シートのデータを少し変更しました（ご安心ください。元のシートもすべてワークブック内に残っています）。サンプルでは学生の名前とIDが同じ列に入っている状態を、2つの別々の列に分割します。

3.1. RIGHT・SUM・LEN・SUBSTITUTE関数を組み合わせる

SUBSTITUTE関数で数字を空欄に置き換え、LEN関数で文字数をカウントすることで、数字部分の桁数を特定します。「文字＋数字」形式の文字列を分割するには、まず数字部分を取り出し、その情報を利用してテキスト部分を抽出します。

手順：

• まず、結果を表示したいセルを選択します。ここではセルC5を選びます。
• 次に、そのセルに次の数式を入力します。

=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))

• Enterキーを押します。

• プラス記号をドラッグすれば、数式を複製して範囲全体の結果を得られます。

🔎 数式の仕組み：

数字を抽出するには、文字列内の「0」から「9」までの各数字の出現回数を調べます。そして合計を求めることで、文字列の末尾から何文字分が数字なのかが判明し、RIGHT関数でそれらを取り出せるのです。

3.2. LEFT関数とLEN関数を組み合わせる

テキスト部分を取り出すには、LEFT関数を使用し、抽出する文字数の引数に「セル全体の長さ－数字の桁数」を指定します。前の方法でIDをセルD5に分割済みなので、そこから桁数を参照できます。

手順：

• まず、対象のセルを選択し、次の数式を入力します。

=LEFT(B5,LEN(B5)-LEN(D5))

• Enterキーを押します。

• プラス記号をドラッグすれば、数式をコピーして複数セルの結果を一括取得できます。

関連記事： Excel VBA：文字数を指定して文字列を分割する2つの簡単な方法

4. 「数字＋文字」パターンのセルを数式で分割する

「文字＋数字」の分割方法を理解できたら、「数字＋文字」形式の分割もイメージできるでしょう。アプローチは基本的に同じですが、ひとつだけ違いがあります。今度は数字が左側にあるため、数字の抽出にはLEFT関数を、文字の抽出にはRIGHT関数を使用します。

4.1. LEFT・SUM・LEN・SUBSTITUTE関数を組み合わせる

「数字＋文字」パターンで先頭（数字）の値を取り出すには、LEFT・SUM・LEN・SUBSTITUTE関数を組み合わせます。

手順：

• まず対象のセルを選択し、次の数式を入力します。

=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

• Enterキーを押します。

• さらに、プラス記号をドラッグして数式を複製すれば、複数セルの結果をまとめて取得できます。

4.2. RIGHT関数とLEN関数を組み合わせる

最後の値（文字部分）を取り出すには、RIGHT関数とLEN関数を組み合わせます。

手順：

• まず、対象のセルを選択し、次の数式を入力します。

=RIGHT(B5,LEN(B5)-LEN(C5))

• Enterキーを押します。

• さらに、プラス記号をドラッグして数式を複製し、セル範囲の結果を取得します。

5. RIGHT・LEN・FIND・SUBSTITUTE関数の組み合わせで日付をセルから抽出する

テキストから日付部分だけを取り出したいときは、RIGHT・LEN・FIND・SUBSTITUTE関数の組み合わせが有効です。

手順：

• 目的のセルを選択し、次の数式を入力します。

=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))

• Enterキーを押します。

• プラス記号をドラッグして数式を複製すれば、複数セルの結果も取得できます。

🔎 数式の仕組み：

日付は文字列の末尾にあるため、スペースの位置を数段階さかのぼって特定することで、月・日・年をまとめて抽出しています。対象の値がより多くのテキストを含む場合は、インスタンス数を調整すれば対応可能です。

注意： この数式が使えるのは、日付がテキスト文字列の末尾に配置されている場合のみです。

関連記事： Excel VBA：文字列をセルに分割分割する4つの便利な活用例

6. FILTERXML関数とSUBSTITUTE関数を組み合わせてセルを分割する

FILTERXML関数は、指定したXPathに基づいてXMLドキュメントから特定のデータを抽出する関数です。FILTERXMLとSUBSTITUTE関数を組み合わせることで、セルを区切り文字で分割できます。

手順：

• まず、目的のセルを選択し、次の数式を入力します。

=FILTERXML(""&SUBSTITUTE(B5,",","")&"","//s[2]")

• Enterキーを押して結果を確認します。

• 最後に、プラス記号をドラッグして数式を複製すれば、セル範囲全体の結果が得られます。

🔎 数式の仕組み：

SUBSTITUTE関数は、文字列内の特定のテキストを置き換えます。ここでは区切り文字をXMLタグに変換し、FILTERXML関数でXMLデータとして扱うことで、目的の要素だけを取り出しているのです。

7. TEXTSPLIT関数で区切り文字によりセルを分割する

TEXTSPLIT関数は、行または列の区切り文字を指定してテキストを分割できる関数です。行方向にも列方向にも分割でき、区切り文字によるセル分割としては最もシンプルで短い数式で済みます。以下の手順に従ってみましょう。

手順：

• 結果を表示したいセルを選択し、次の数式を入力します。

=TEXTSPLIT(B5,",")

• Enterキーを押します。

• さらに、プラス記号をドラッグして数式を複製し、セル範囲の結果を取得します。

注意： 隣接する列に十分な空きセルがないと、#SPILL!エラーが発生することがあります。事前に空き列を確保しておきましょう。

8. TRIM・MID・SUBSTITUTE・REPT・LEN関数を組み合わせてセルを分割する

もうひとつの強力な組み合わせが、TRIM・MID・SUBSTITUTE・REPT・LEN関数です。この組み合わせでも、区切り文字を基準にセルを分割できます。

手順：

• 結果を表示したいセルを選択し、次の数式を入力します。

=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))

• Enterキーを押します。

• プラス記号をスライドするようにドラッグすれば、数式を複製して複数セルの結果を取得できます。

🔎 数式の仕組み：

LEN関数は文字列の長さ（文字数）を返します。SUBSTITUTE関数は、区切り文字を大量のスペースに置き換える役割を担います。MID関数は、指定した開始位置から一定の文字数を取り出します。最後にTRIM関数が余分な空白を削除し、きれいな値だけを残すのです。

コマンド機能を使って区切り文字でセルを分割する方法

Excelには、数式を使わずにセルを分割できる標準機能が備わっています。[データ]タブの中にあるので、そちらを使いたい場合は以下の手順に従ってください。

手順：

• まず、分割したいセルまたは列を選択します（通常は列全体を選択します）。
• 次に、[データ]タブを開きます。[データツール]グループ内に[区切り位置]というオプションがあるので見つけてください。
• それをクリックします。

• ダイアログボックスが表示されます。今回は区切り文字で分割するのが一般的なケースなので、[コンマやタブなどの区切り文字によってフィールドごとに列を分割する]を選択して[次へ]をクリックします。

• 次に、いくつかの区切り文字を選択できる画面が現れます。
• 候補から希望のものを選ぶか、独自の区切り文字を入力することも可能です。
• 区切り文字を選択すると、ウィザード下部にプレビュー結果が表示されます。
• 確認したら[次へ]をクリックしましょう。

• この例では、値がカンマ区切りだったためカンマを選択しています。
• [次へ]をクリックすると、各列のデータ型を指定する画面が出るので設定して[完了]を押します。これで値が別々の列に分割されます。

• 今回はデフォルトのまま[標準]を選択しています。下図は書式設定を行った後に表示された結果です。

まとめ

以上の方法を使えば、Excelで区切り文字を基準にセルを分割できます。状況に応じて、SEARCH系の数式、TEXTSPLIT関数、FILTERXML関数、あるいは標準の[区切り位置]機能などを使い分けるとよいでしょう。この記事が皆さんの作業効率化に役立てば幸いです。内容でわかりにくい点があれば、お気軽にコメントしてください。また、ここで紹介しきれていない別の方法があれば、ぜひ教えてください。

さらに学びたい方へ

• Excelで1つのセルを2つに分割する5つの実用的な方法
• Excelでセルを半分に分割する方法（斜め＆水平）
• VBAで文字列を複数列に分割する2つの方法
• Excel VBA：文字で文字列を分割する6つの便利な例
• Excelで1つのセルに2行を入力する4つの方法