Excelで元データを上書きせずにテキストを列に分割する5つの方法

日々の業務の中で、Excelのセルに入力されたテキストを複数の列に分割したい場面は頻繁にあります。Microsoft Excelにはそのための便利な機能が数多く用意されていますが、多くの解説記事では「区切り位置」機能を使う際に元のデータが上書きされてしまうという問題点があります。そこで本記事では、元のデータを一切変更せずにテキストを別の列へ分割する方法を、5つのアプローチから詳しく解説します。ぜひ参考にして、業務効率化にお役立てください。

理解を深めるために、サンプルExcelファイルをダウンロードして実際に操作しながら学習することもできます。

Excelでテキストを上書きせずに列へ分割する5つの方法

説明をわかりやすくするため、ある学校の生徒の氏名と年齢リストをサンプルデータとして使用します。このデータセットでは、B列に氏名と年齢がカンマ区切りで入力されています。

ここから、B列のテキストを元データを保持したまま別の列へ分割していきます。使用するのはMicrosoft Excel 365ですが、お使いのバージョンに合わせて同様の手順を実行できます。それでは、各方法を順番に見ていきましょう。

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

数式を使いこなしたい方にぴったりの方法です。複数の関数を組み合わせて、柔軟にテキストを分割する数式を作成します。

📌 手順：

• まず、C列とD列にそれぞれ「氏名」と「年齢」という見出しを作成します。
• セルC5とD5に、それぞれ1と2を入力します。これは新しい列の番号であり、後続の手順で重要な役割を果たします。

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

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

ここで、B6は最初の「氏名，年齢」データ、C5は列番号「1」を参照しています。

数式の解説：

• LEN($B6) → LEN関数は文字列の文字数を返します。セルB6の文字数は11です。
  • 結果 → 11
• REPT(" ",LEN($B6)) → REPT関数は指定した回数だけテキストを繰り返します。空白スペースを11回繰り返す指定になります。
  • 結果 → " "（11個の空白）
• SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) → SUBSTITUTE関数は文字列内の特定のテキストを別のテキストに置き換えます。ここではカンマを大量の空白に置き換えることで、各要素の間隔を大きく広げます。
  • 結果 → John 18 yr
• MID(...) → MID関数は開始位置と文字数を指定して、文字列の途中から文字を取り出します。(C$5-1)*LEN($B6)+1で計算される開始位置により、目的の要素だけを抽出できます。
  • 結果 → John
• TRIM(...) → TRIM関数は文字列から余分な空白を削除し、単語間のスペースを1つだけ残します。これにより余分な空白が取り除かれます。
  • 結果 → John

• 入力後、Enterキーを押します。

• 次に、フィルハンドル（セル右下の小さな四角）を行方向にドラッグして、数式をD6にもコピーします。

すると、D6セルにテキストの右側部分（年齢）が表示されます。

• 続いて、C6とD6の2つのセルを選択し、D6の右下隅にカーソルを合わせるとフィルハンドルが表示されます。
• それをダブルクリックすると、下の行すべてに数式が自動適用されます。

以下のように、すべての行でテキストが正しく分割されました。

方法2：LEFT・LEN・RIGHT・SEARCH関数を挿入する

複雑な数式を書くのが苦手な方には、こちらの方法がおすすめです。同じく数式を使いますが、よりシンプルで理解しやすい構成になっています。

📌 手順：

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

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

ここで、B5は最初の「氏名，年齢」データを参照しています。

数式の解説：

• SEARCH(",",B5) → SEARCH関数は、ある文字列が別の文字列の中で最初に現れる位置を返します。ここではカンマ(,)の位置を取得します。
  • 結果 → 5
• LEFT(B5,SEARCH(",",B5)-1) → LEFT関数は文字列の先頭から指定した文字数分の文字を返します。カンマの位置から1を引いた「5」文字を取得することで、氏名部分だけを取り出せます。
• 結果 → John

• 続いて、Enterキーを押します。

次に、セルD5に年齢部分を抽出するための類似の数式を入力します。先ほどはLEFT関数で文字列の前半を取得しましたが、今度は後半を取り出します。

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

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

数式の解説：

• LEN(B5)-SEARCH(",",B5) → 文字列全体の長さからカンマの位置を引くことで、カンマ以降の文字数を算出します。
  • 結果 → 11 − 5 → 6
• RIGHT(B5,LEN(B5)-SEARCH(",",B5)) → RIGHT関数は文字列の末尾から指定した文字数分の文字を返します。ここでは末尾の6文字を取得します。
• 結果 → 18 yr

• 最後に、Enterキーを押して完了です。

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

この方法では、Excel標準の「区切り位置」機能を使います。ポイントは、出力先（保存先）を別のセルに指定することで、元のデータを上書きせずに済ませることです。

📌 手順：

• まず、範囲B5:B14のセルを選択します。
• 次に、「データ」タブに移動します。
• 「データツール」グループ内の「区切り位置」をクリックします。

すぐに「テキストファイルウィザード」（区切り位置指定ウィザード）のダイアログボックスが開きます。

• ステップ1で、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択します。
• その後、「次へ」ボタンをクリックします。

• ステップ2で、区切り文字として「コンマ」にチェックを入れます。
• さらに「次へ」をクリックします。

• ステップ3（最終ステップ）で、「表示先」（出力先）をセルC5に設定します。これが元データを保護する重要なポイントです。
• 最後に「完了」ボタンをクリックします。

手順を完了すると、Excelから警告メッセージが表示されることがありますが、心配いりません。

• そのまま「OK」をクリックしてください。

これで、元データを上書きすることなくテキストを列に分割できました。

方法4：「フラッシュフィル」機能を活用する

複雑な数式を使いたくない場合に最適なのが、Excelのフラッシュフィル機能です。入力例をもとに、Excelが自動的にパターンを認識して残りのセルを埋めてくれます。

📌 手順：

• まず、セルC5に「John」と手動で入力します。これがこのセルの望ましい結果となります。初回のみ手入力が必要です。
• 次に、「ホーム」タブに移動します。
• 「編集」グループの「フィル」ドロップダウンアイコンをクリックします。
• 表示された一覧から「フラッシュフィル」を選択します。

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

同様に、D列の「年齢」列についても同じ手順を実行します。

なお、ショートカットキーCtrl + Eでもフラッシュフィルを実行できるので、覚えておくと便利です。

方法5：VBAコードを適用する

数式は素早くデータを編集できる反面、内容が読み取りにくいという欠点があります。また、この作業を定期的に自動化したい場合は、以下のVBAコードの利用を検討してみてください。

📌 手順：

• まず、「開発」タブに移動します。
• 次に、「コード」グループの「Visual Basic」を選択します。
• または、キーボードショートカットAlt + F11でも同じ操作が可能です。

「Microsoft Visual Basic for Applications」ウィンドウが開きます。

• プロジェクトエクスプローラーで対象のシート（例：Sheet6(VBA)）をダブルクリックします。

右側にコードモジュールが開きます。

• 以下のコードをコピーして、モジュールに貼り付けます。

Sub Text_to_Columns_without_Overwriting()
Dim Arr() As String, cnt As Long, j As Variant
For k = 5 To 14
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つのパートに分かれています。

• 最初のパートでは、サブルーチンにText_to_Columns_without_Overwriting()という名前を付けます。
• 続いて、変数Arr、cnt、jをそれぞれString型、Long型、Variant型として宣言します。
• 2つ目のパートでは、Forループを使って各行のセルを処理し、カンマで区切られたテキストを分割します。
• コード内のFor k = 5 To 14は、データの開始行と終了行を表しており、ここでは5行目から14行目を対象としています。
• Arr = Split(Cells(k, 2), ",")の","は区切り文字です。必要に応じてセミコロンや縦棒などに変更できます。
• 最後に、cnt = 3は出力開始列が3列目（C列）であることを示しています。

• コードを貼り付けたら、リボンの緑色の再生ボタン（実行ボタン）をクリックします。キーボードのF5キーでも実行できます。

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

練習セクション

ご自身で練習できるよう、各シートの右側に練習用セクションを用意しています。ぜひ実際に手を動かして試してみてください。

まとめ

本記事では、Excelで元データを上書きせずにテキストを列へ分割する方法を、シンプルかつ簡潔に解説しました。関数を使った方法から、区切り位置機能、フラッシュフィル、VBAまで、スキルレベルや用途に応じて最適な手法を選べます。練習ファイルのダウンロードもお忘れなく。本記事がお役に立てば幸いです。ご質問やご提案があれば、コメント欄でお気軽にお知らせください。Excelに関するさまざまなソリューションを提供する当サイトも、ぜひご覧ください。

関連記事

• Excelの区切り位置機能で日付データを分割する方法（簡単な手順付き）
• 【解決済み】Excelの区切り位置機能でデータが消えてしまう問題の対処法
• Excelで複数の区切り文字を使ってテキストを列に変換する方法