Excelで複数の区切り記号を含むテキストを列に分割する方法6選

テキストを列に変換する作業は、日々の業務で非常によく発生します。さまざまな場面でテキストを複数の列に分割する必要があり、その際に活躍するのがMicrosoft Excelです。
本記事では、Excelで複数の区切り記号を使ってテキストを列に分割する方法について、詳細な手順を交えてわかりやすく解説します。

Excelで複数の区切り記号を使ってテキストを列に分割する6つの方法

今回は、セルB4:B13に入力されたベストセラーリストというデータセットを例に説明します。このデータには書籍名・著者名・ジャンルが1つのセルにまとめられており、それぞれを別々の列に分割することを目標とします。それでは、各方法を順番に見ていきましょう。

ここではMicrosoft Excel 365を使用していますが、お使いのバージョンに合わせて操作してください。

方法1：「区切り位置」機能を使用する

まず最初にご紹介するのは、Excel標準搭載の「区切り位置」機能です。複数の区切り記号を含むテキストを列に分割できる便利なツールです。以下の手順に沿って操作方法を見ていきましょう。

📌 手順：

• まず、セルB5:B13を選択し、データタブを開いて区切り位置をクリックします。

すると、「テキストファイル ウィザード」が表示されます。

• コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ（D）を選択して次へをクリックします。

• 区切り文字としてコンマにチェックを入れ、次へボタンを押します。

• 次に、出力先となる表示先のセルを指定します。ここではC5セルとし、完了をクリックします。

• 警告メッセージが表示された場合は、OKをクリックします。

以上の手順で、下記のスクリーンショットのような結果になります。

方法2：TRIM・MID・SUBSTITUTE・REPT・LEN関数を組み合わせる

続いては、関数を使った方法です。TRIM、MID、SUBSTITUTE、REPT、LENの各関数を組み合わせることで、複数の区切り記号を含むテキストを列に分割できます。各関数の役割は以下の通りです。LEN関数は文字列の長さを返し、REPT関数は指定回数だけテキストを繰り返します。SUBSTITUTE関数は古いテキストを新しいテキストに置き換え、MID関数は文字列の中から指定した文字を抽出します。最後のTRIM関数は余分なスペースを削除します。

📌 手順：

• まず、C6セルに以下の数式を入力します。

=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))

ここで、B6セルは元データのセル、C5セルは列番号を示す値（1）です。

数式の解説：

• LEN($B6) → セルB6内の文字数を返します。結果は43です。
  • 出力 → 43
• REPT(" ",LEN($B6)) → 空白スペースを43回繰り返します。" "は繰り返すテキスト（空白）、43は繰り返し回数です。
  • 出力 → 空白43個分の文字列
• SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) → 元データ内のコンマを大量の空白スペースに置き換えます。$B6が対象テキスト、","が置換前、REPT(...)が置換後のテキストです。
  • 出力 → 「Poor Economics　（空白）　Abhijit Banarjee　（空白）　Economics」
• MID(SUBSTITUTE(...),(C$5-1)*LEN($B6)+1,LEN($B6)) → 置換後の文字列から指定位置以降の文字を抽出します。第1引数が対象テキスト、(C$5-1)*LEN($B6)+1が開始位置、LEN($B6)が抽出する文字数です。
  • 出力 → 「Poor Economics　（余分な空白付き）」
• TRIM(MID(...)) → 最後に余分なスペースを削除して整えます。
  • 出力 → Poor Economics

• 次に、フィルハンドルを使って数式を行方向にコピーします。

• さらに、C6:E6セルを選択した状態で、フィルハンドルを下方向にドラッグして数式を適用します。

最終的に、下図のような結果になります。

方法3：LEFT・RIGHT・MID・LEN・FIND関数を組み合わせる

別のアプローチとして、LEFT、RIGHT、MID、LEN、FIND関数の組み合わせでもテキストを分割できます。FIND関数は指定した文字の位置を検索し、LEN関数は文字列の長さを返します。MID関数は文字列の中間部分を抽出し、LEFT関数とRIGHT関数はそれぞれ左端・右端から文字を取り出します。
ここでは、セルB4:B12にある顧客リスト（氏名・国名・都市がセミコロンで区切られたデータ）を例に、氏名・国名・都市を別々の列に分割してみましょう。

📌 手順：

まず、C5セルに以下の数式を入力します。

=LEFT(B5,FIND(";",B5)-1)

この数式では、B5セルが元データを指しています。

数式の解説：

• FIND(";",B5) → セミコロン（;）が文字列内の何番目にあるかを返します。";"が検索対象、B5が検索先の文字列です。
  • 出力 → 7
• LEFT(B5,FIND(";",B5)-1) → 文字列の左端から指定文字数分を抽出します。セミコロンの直前までを取り出すため、FINDの結果から1を引いています。
  • 出力 → Trevor

• 続いて、D5セルに以下の数式を入力します。

=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)

数式の解説：

• FIND("@",B5)-FIND(";",B5)-1 → 「@」と「;」の位置差から抽出する文字数を算出します。
  • 16 − 7 − 1 → 8
• FIND(";",B5)+1 → セミコロンの次の文字が開始位置になります。
  • 7 + 1 → 8
• MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1) → 8番目の文字から8文字分を抽出します。
  • 出力 → Iceland

• 最後に、E5セルに以下の数式を入力します。

=RIGHT(B5,LEN(B5)-FIND("@",B5))

数式の解説：

• LEN(B5)-FIND("@",B5) → 文字列全体の長さから「@」の位置を引くことで、右側の残りの文字数を求めます。
  • 26 − 16 → 10
• RIGHT(B5,LEN(B5)-FIND("@",B5)) → 右端から10文字分を抽出します。
  • 出力 → Reykjavik

すべて入力すると、下記のスクリーンショットのような結果になります。

方法4：フラッシュフィルを利用する

複雑な数式を使いたくない方には、フラッシュフィル機能がおすすめです。Excelの学習機能を利用して、入力パターンから自動的に残りのデータを補完してくれます。

📌 手順：

• まず、C5セルに「Trevor」と手動で入力します。次に、ホームタブのフィルのドロップダウンをクリックし、フラッシュフィルを選択します。

すると、Excelが残りのセルを自動入力してくれます。

同様の操作を国名と都市の列にも適用すると、下図のような仕上がりになります。

方法5：Power Queryを使用する

Power Queryは、データを素早く整理・分析できるにもかかわらず、意外と見落とされがちなExcelの強力な機能です。数クリックだけでテキストを列に分割できますので、実際に試してみましょう。

📌 手順：

• まず、B4セルを選択し、ショートカットCtrl + Tでテーブルを作成してOKを押します。

• 次に、データタブからテーブルまたは範囲からをクリックします。

すると、Power Queryエディターが起動します。

• 列の分割のドロップダウンから区切り記号によるを選択します。

• セミコロンを選択し、区切り記号の各出現箇所にチェックを入れてOKをクリックします。

• 列ヘッダーをダブルクリックして名前を変更したら、閉じて読み込むを選択してPower Queryウィンドウを終了します。

これらの手順を完了すると、以下のような結果が得られます。

方法6：VBAコードを活用する

頻繁に同じ作業を繰り返す場合は、以下のVBAコードの活用を検討してみてください。シンプルで扱いやすいコードです。

📌 手順：

• まず、開発タブを開いてVisual Basicボタンをクリックします。

新しいウィンドウでVisual Basic Editorが開きます。

• 次に、挿入タブから標準モジュールを選択します。

以下のコードをコピーして、モジュールウィンドウに貼り付けてください。

Sub Separate_Text_String()

Dim Arr() As String, _
cnt As Long, _
j As Variant

For k = 5 To 13
 Arr = Split(Cells(k, 2), ";")
 cnt = 3
 
 For Each j In Arr
 Cells(k, cnt) = j
 cnt = cnt + 1
 
 Next j
Next k

End Sub

⚡ コードの解説：

ここでは、複数の区切り記号でテキストを分割するVBAコードの仕組みを説明します。コードは大きく2つのパートに分かれています。

• 前半部分では、サブルーチンの名前（ここではSeparate_Text_String()）を定義し、変数Arr、cnt、jをそれぞれString型、Long型、Variant型として宣言しています。
• 後半部分では、Forループを使って各行を処理し、セミコロンで区切られたテキストを分割しています。
• 「For k = 5 To 13」はデータの開始行と終了行を示しており、この例では5〜13行目が対象です。
• 「Arr = Split(Cells(k, 2), ";")」内の";"が区切り記号です。用途に応じてコンマやパイプなどに変更できます。
• 最後の「cnt = 3」は出力開始列（C列＝3列目）を意味します。

• コードを貼り付けたら、実行ボタンを押すか、キーボードのF5キーを押します。

実行すると、下記のスクリーンショットのように結果が表示されます。

練習セクション

各ワークシートの右側には練習用セクションをご用意しています。ぜひご自身で操作を試してみてください。

まとめ

本記事では、Excelで複数の区切り記号を含むテキストを列に分割する6つの効果的な方法を紹介しました。記事をじっくり読み、無料ワークブックをダウンロードして実際に練習してみてください。この記事が皆様のお役に立てば幸いです。ご不明点やご要望があれば、お気軽にコメント欄でお知らせください。ほかにも役立つExcel関連の記事を多数掲載していますので、ぜひご覧ください。

関連記事

• Excelの区切り位置機能で日付を分割する方法（初心者向け手順）
• 【解決済み】Excelの区切り位置でデータが消える問題の対処法
• Excelで既存データを上書きせずにテキストを列に変換する方法