Excelでテキストを分割する8つの便利な方法｜区切り位置・フラッシュフィル・関数・VBAまで徹底解説

Excelでは、さまざまな種類のテキストデータを扱う機会が非常に多くあります。しかし、データの可読性や分析のしやすさを高めるために、テキストを分割したい場面もあるでしょう。本記事では、Excelでテキストを分割するための8つの実用的な方法を、具体的な手順とともにわかりやすく解説します。

サンプルファイルをダウンロードして、ぜひご自身でも試してみてください。

Excelでテキストを分割する8つの便利な方法

説明のために、異なる国に住む5名の氏名と住所の情報を含んだデータセットを用意しました。

それでは、以下の例に沿って、このデータセット内のテキストを分割していきましょう。

1. 区切り位置ウィザードを使ってテキストを分割する

最初の方法では、区切り位置ウィザードを使い、区切り文字を指定してテキストを分割する手順をご紹介します。以下の手順を確認してください。

• まず、データタブを開き、データツールグループにある区切り位置を選択します。

• 次に、区切り位置ウィザードの最初の画面でコンマやタブなどの区切り文字によってフィールドごとに列を分割するを選択し、次へをクリックします。

• 第2ステップでは、区切り文字としてコンマにチェックを入れ、次へを押します。

• 最後のステップでは、表示先としてセルC5を指定し、完了をクリックします。

• これで、以下の画像のようにテキストが分割されました。

2. フラッシュフィルでテキストを分割する

フラッシュフィルは、区切り位置ウィザードを補完する優れた機能です。以下の手順で操作します。

• まず、セルC5に元テキストの最初の部分(名前)を入力します。

• 続いて、データタブを開き、データツールグループのフラッシュフィルを選択します。

• すると、セル範囲C6:C9が自動的に入力され、それぞれの元データから対応する部分が抽出されているのがわかります。

• 同じ手順を繰り返せば、最終的な出力結果が得られます。

3. LEFT・RIGHT・FIND・LEN関数を組み合わせてテキストを分割する

テキストを分割するもう一つの有効な方法として、LEFT関数、RIGHT関数、FIND関数、LEN関数を組み合わせる方法があります。実際の動作を見てみましょう。

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

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

• 次に、Enterキーを押します。
• その後、オートフィル機能を使えば、下記のような結果が得られます。

ここでは、セルの左端からデータを取り出すためにLEFT関数を使用しました。続いて、セルB5内のカンマの位置を検索するためにFIND関数を使っています。

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

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

• さらに、Enterキーを押します。
• 同様にオートフィル機能を使えば、セル範囲D5:D9に分割後のテキストが表示されます。

ここでは、RIGHT関数でセルB5の右側からテキストを抽出し、LEN関数で文字列の長さを求め、最後にFIND関数で特定の文字列の位置を検索しています。

4. TRANSPOSE関数を挿入してテキストを分割する

TRANSPOSE関数も、テキスト分割にとても役立ちます。以下の手順を確認しましょう。

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

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

• 次に、Enterキーを押します。
• すると、テキストが自動的にセルC5、D5、E5に分割されます。

ここでは、TRANSPOSE関数で配列を作成してテキストを分割し、FILTERXML関数でセルB5から個々の項目を抽出しています。また、SUBSTITUTE関数を使って、データセットに合わせた置換処理を行っています。

• 最後に、フィルハンドルを使って下のセルにも数式をコピーします。

補足: 次の数式を使っても同じ結果が得られます。

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

5. Excel VBAを使ってテキストを分割する

Excel VBAは、テキスト分割に対する優れたソリューションです。以下の簡単な手順を確認してみましょう。

• まず、開発タブを開き、コードグループのVisual Basicを選択します。

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

• その後、空白のページに以下のコードを挿入します。

Sub SplitCellText()
Dim MyAry() As String, Cnt As Long, j As Variant
For n = 5 To 9
    MyAry = Split(Cells(n, 2), ",")
    Cnt = 3
    For Each j In MyAry
        Cells(n, Cnt) = j
        Cnt = Cnt + 1
    Next j
Next n
End Sub

• 続いて、Sub/ユーザーフォームの実行をクリックするか、キーボードのF5キーを押します。

• 次に、マクロウィンドウで実行をクリックします。

• 以上で、Excelでのテキスト分割は完了です。

6. Power Queryを使ってExcelのテキストを分割する

テキストを分割するもう一つの便利な方法が、ExcelのPower Query(パワークエリ)の活用です。このツールを使うと、外部のブックからデータを読み込み、好みの形式に整形できます。以下の手順に注意深く従ってください。

• まず、データタブを開き、データの取得を選択します。

• 次に、コンテキストメニューからファイルから、続けてExcel ブックからを選択します。

• 次に、ナビゲーターの一覧からPower Queryシートを選択します。
• その後、データの変換をクリックします。

• 開いたウィンドウで、変換タブに移動し、列の分割をクリックします。

• ドロップダウンから区切り記号ごとを選択します。

• すると、区切り記号による列の分割ダイアログボックスが表示されます。
• ここで、区切り記号としてコンマを選択します。
• あわせて、分割の位置として区切り記号の各出現箇所を選択します。

• 次に、OKをクリックします。
• 最後に、閉じて読み込むをクリックします。

• すると、テキストが分割された新しいワークシートが表示されます。

• このテーブルは、後から自由に編集することもできます。

7. TEXTBEFORE・TEXTAFTER関数でテキストを分割する

最後の方法として、新しいデータセットを使ってTEXTBEFORE関数とTEXTAFTER関数による分割プロセスを解説します。ここでは、5名の氏名情報がセル範囲B5:B9に入力されています。以下の手順を確認しましょう。

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

=TEXTBEFORE(B5," ")

ここでは、TEXTBEFORE関数を使って、セルB5内の区切り文字より前のテキストを返しています。

• 次に、セルD5に以下の数式を入力し、Enterキーを押します。

=IFERROR(TEXTBEFORE(TEXTAFTER(B5," ")," ", -1),"")

ここでは、エラー時に指定した値を返すためにIFERROR関数を使用しています。さらに、TEXTBEFORE関数とTEXTAFTER関数により、セルB5内の区切り文字の前後のテキストをそれぞれ返します。

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

=TEXTAFTER(B5," ",-1)

ここでは、TEXTAFTER関数を使って、セルB5内の区切り文字より後ろのテキストを返しています。

• これで、セルB5に対する出力結果が確認できます。

• 最後に、フィルハンドルツールを使えば、最終的な出力結果が得られます。

8. 改行コードを基準にテキストを分割する

このセクションでは、テキスト分割の新たな視点として、改行を基準にテキストを分割する方法を解説します。以下の手順を見ていきましょう。

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

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

ここでは、まずLEFT関数で左側の値を抽出し、次にSEARCH関数で文字列内の位置を数値として返しています。最後に、CHAR関数で数値に対応する文字(改行コード)をセルB5から取得しています。

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

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

ここでは、MID関数でセルB5の中間行の値を取得し、SEARCH関数で文字列内の位置を返しています。最後にCHAR関数で、数値に対応する文字を取得しています。

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

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

ここでは、まずRIGHT関数で右側の値を抽出し、次にSEARCH関数で文字列内の位置を数値として返し、LEN関数で文字列の長さを求めています。最後にCHAR関数で、セルB5内の数値に対応する文字を取得しています。

• ここまでの、セルB5に対する出力結果は以下の通りです。

• 同じ手順を繰り返せば、分割されたテキストの最終結果が確認できます。

Excelで数値だけをテキストから抽出する方法

次の画像は、1つの列に数値とテキストが混在したワークシートです。ここでは、各セルから数値部分のみを取り出し、別の列に抽出することを目標とします。フラッシュフィルツールを使えば簡単に実現できます。

• まず、セルB5の数値をセルC5に入力します。

• 次に、隣接する次のセルをクリックします。
• その後、データタブに移動し、フラッシュフィルを選択します。

• これだけで、すべての数値が一度に抽出されます。

まとめ

今回は、Excelでテキストを分割するための8つの実用的な方法について解説しました。ぜひ実際に試してみて、感想をお聞かせください。Excelに関するさらなる情報は、今後の記事にもご期待ください。