Excelの数式でテキストを自動的に列に分割する3つの方法

Excelでデータを管理していると、1つのセルに入力されたテキストを複数の列に分割して整理したい場面がよくあります。「区切り位置」ウィザードやVBAマクロを使う方法もありますが、数式を使えば元データを変更せずに自動的に分割でき、データを更新しても結果が自動で反映されるという大きなメリットがあります。この記事では、Excelの数式でテキストを自動的に列に分割するための3つのシンプルな方法を、具体的な手順とともにわかりやすく解説します。

数式でテキストを列に自動分割する3つの方法

ここからは、Excelの数式を使ってテキストを列に分割する3つの方法を順番に紹介します。目的やデータの形式に応じて、最適な方法を選んで活用してください。

方法1：LEFT・MID・RIGHT関数を組み合わせて分割する

文字数が決まっているテキスト（IPアドレスやコード番号など）を分割する場合は、LEFT・MID・RIGHT関数を組み合わせるのが最もシンプルで確実な方法です。以下の手順に従って操作してください。

手順

• まず、整理されたデータセットを用意します。ここではIPアドレス列に入力されたIPアドレスを例に、先頭2桁を「都市コード」、中間3桁を「顧客番号」、末尾3桁を「部署コード」として分割します。
• 先頭の2桁（都市コード）を抽出するには、次の数式を入力します。

=LEFT(B5,2)

• Enterキーを押して結果を表示します。
• フィルハンドルを使い、残りのセルにもオートフィルで数式をコピーします。
• 続いて、中間の3桁（顧客番号）を抽出するには、次の数式を使用します。

=MID(B5,4,3)

• Enterキーを押して結果を確認したら、残りのセルもオートフィルします。
• 最後に、末尾の3桁（部署コード）を抽出するために、次の数式を入力します。

=RIGHT(B5,3)

• Enterキーを押し、フィルハンドルで残りのセルにも適用すれば、すべての抽出作業は完了です。

あわせて読みたい：Excelの区切り位置機能で改行（キャリッジリターン）を扱う方法

方法2：TRANSPOSE・FILTERXML・SUBSTITUTE関数を組み合わせて分割する

カンマなどの区切り文字で連結されたテキストを一括で分割したい場合は、TRANSPOSE・FILTERXML・SUBSTITUTE関数を組み合わせた数式が便利です。この方法なら、1つの数式で複数の項目を一度に別々の列へ展開できます。

手順

• まず、整理されたデータセットを作成します。ここでは情報列にカンマ区切りで入力された個人情報を、名（ファーストネーム）、姓（ラストネーム）、性別、都市、会社名の順に分割します。
• 次の数式を入力して、テキストを列に分割します。

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B5,",","</s><s>")&"</s></t>","//s"))

• Enterキーを押すと、各項目が横方向のセルに自動的に展開されます。
• 最後に、フィルハンドルで残りのセルにもオートフィルすれば完成です。

※FILTERXML関数はWindows版Excel専用の関数のため、Mac版では使用できない点にご注意ください。

あわせて読みたい：Excelで上書きせずにテキストを列に変換する方法

方法3：TRIM・MID・SUBSTITUTE関数を組み合わせて分割する

もうひとつの効果的な方法が、TRIM・MID・SUBSTITUTE関数を組み合わせた数式です。FILTERXML関数が使えない環境でも動作する汎用的な手法で、区切り文字で区切られたテキストを列ごとに確実に抽出できます。

手順

• まず、方法2と同様に、カンマ区切りの情報が入力されたデータセットを用意します。情報列の内容を名、姓、性別、都市、会社名の順に分割していきます。
• セルC5に次の数式を入力します。

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),COLUMNS($B:B)*LEN($B5)-(LEN($B5)-1),LEN($B5)))

• Enterキーを押して、セルに値が表示されることを確認します。
• 数式を右方向にオートフィルすると、テキストが各列に順番に分割されていきます。
• 最後に、フィルハンドルを使って下方向の残りのセルにも数式を適用すれば、すべてのデータの分割が完了します。

あわせて読みたい：Excelで複数の区切り文字を使ってテキストを列に変換する方法

練習用データで試してみよう

理解をさらに深めるために、実際にサンプルデータを使って各数式を試してみましょう。自分のデータに置き換えて練習することで、数式の仕組みがより明確に把握できます。

まとめ

この記事では、Excelの数式でテキストを自動的に列に分割する3つの方法を解説しました。固定長のテキストならLEFT・MID・RIGHT関数の組み合わせ、区切り文字を含むテキストならTRANSPOSE＋FILTERXML＋SUBSTITUTE関数、またはTRIM＋MID＋SUBSTITUTE関数の組み合わせが有効です。データの形式や使用環境に応じて最適な方法を選べば、面倒な分割作業を大幅に効率化できます。この記事がExcelユーザーのお役に立てば幸いです。ご不明な点があれば、ぜひコメント欄でお知らせください。当サイトでは、他にもExcelの活用に関する記事を多数公開していますので、あわせてご覧ください。

関連記事

• Excelの区切り位置で改行を区切り文字として使う方法
• 【解決済み】Excelの区切り位置を実行するとデータが消える問題の対処法
• Excelの区切り位置を日付の分割・変換に活用する方法（簡単な手順付き）