区切り文字でテキスト文字列を複数行に分割 - 3つの簡単な方法

通常、特定の区切り文字（カンマ、ピリオド、セミコロン、スラッシュなど）を使用してセルの内容を複数の列に分割するには、「テキストを列に分割」機能が使用できます。しかし、時には区切られたセルの内容を複数行に分割し、他の列のデータを繰り返す必要がある場合があります（下のスクリーンショット参照）。このタスクをExcelで処理するための良い方法はありますか？このチュートリアルでは、Excelでこの作業を完了するための効果的な方法をいくつか紹介します。

ビデオ: 区切り文字でテキスト文字列を複数行に分割

方法A: VBAコードで区切りテキストを複数行に分割

このセクションでは、特定の区切り文字で区切られたセルの内容を分割するのに役立つ2つのVBAコードを紹介します。

カンマ、スペース、セミコロンなどで区切られたテキストを分割

カンマ、スペース、セミコロン、スラッシュなどの一般的な区切り文字で区切られたテキスト文字列を分割するには、次のコードが役立ちます。以下の手順に従ってください：

 注意: このコードは「元に戻す」をサポートしていないため、このコードを適用する前にデータをバックアップすることをお勧めします。

ステップ1: VBAモジュールエディターを開き、コードをコピーする

1. 使用したいシートをアクティブにします。次に、「Alt」+「F11」キーを押して「Microsoft Visual Basic for Applications」ウィンドウを開きます。

2. 開いたウィンドウで、「挿入」>「モジュール」をクリックして新しい空白モジュールを作成します。

3. 次に、以下のコードを空白モジュールにコピーして貼り付けます。

VBAコード: 特定の区切り文字（カンマ、ピリオド、スペースなど）でテキストを分割

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
    If xSplitChar = "" Then Exit Sub
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub   

ステップ2: コードを実行して結果を得る

1. コードを貼り付けた後、「F5」キーを押してこのコードを実行します。その後、分割したい区切りテキストが含まれているセルを選択するように促すポップアップボックスが表示されます（スクリーンショット参照）：

2. 「OK」をクリックすると、別のプロンプトボックスが表示され、データを分割する基準となる区切り文字を入力するよう求められます。ここでは、カンマとスペース（, ）を入力しました（スクリーンショット参照）：

3. 最後に、「OK」ボタンをクリックします。これで、選択したテキスト文字列がカンマに基づいて行に分割され、他の関連する列のデータが以下のように繰り返されます（スクリーンショット参照）：

改行で区切られたテキストを分割

セルの内容が改行で区切られている場合、それらを複数行に分割するには、これを行うために役立つ別のVBAコードがあります。

 注意: このコードは「元に戻す」をサポートしていないため、このコードを適用する前にデータをバックアップすることをお勧めします。

ステップ1: VBAモジュールエディターを開き、コードをコピーする

1. 「Alt」+「F11」キーを押して「Microsoft Visual Basic for Applications」ウィンドウを開きます。

2. 開いたウィンドウで、「挿入」>「モジュール」をクリックして新しい空白モジュールを作成します。

3. 次に、以下のコードを空白モジュールにコピーして貼り付けます。

VBAコード: 改行でテキストを分割

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Chr(10)
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub 

ステップ2: コードを実行して結果を得る

1. コードを貼り付けた後、「F5」キーを押してこのコードを実行します。ポップアップされたボックスで、分割したいセルを選択してください（スクリーンショット参照）：

2. 「OK」ボタンをクリックすると、選択したセルのデータが以下のように行に分割されます（スクリーンショット参照）：

方法B: Kutools for Excelを使って10秒で区切りテキストを複数行に分割

「Kutools for Excel」をインストールしている場合、「Split Data to Rows」機能を使用して、指定した任意の区切り文字でテキスト文字列を複数行に分割できます。以下の手順に従ってください：

ステップ1: Split Data to Rows機能を選択する

「Kutools」>「結合と分割」>「Split Data to Rows」をクリックします（スクリーンショット参照）：

ステップ2: 分割するデータセルと区切り文字を指定する

ポップアップされたダイアログボックスで、次のオプションを使用します：

• 1). 「範囲（単一列）」テキストボックスから分割したいセルのリストを選択します；
• 2). 次に、データを区切る区切り文字を選択します。ここでは、「その他」を選択し、カンマとスペース（, ）をテキストボックスに入力します；
• 3). 最後に、「OK」ボタンをクリックします。

これで、選択した区切りテキスト文字列が特定の区切り文字に基づいて複数行に変換されました（スクリーンショット参照）：

 ヒント: 元のデータを復元したい場合は、Ctrl + Zを押して元に戻すだけです。

使いやすいですか？この機能に興味がある方は、クリックして無料試用版を30日間お試しください。

方法C: Power Queryで区切りテキストを複数行に分割

Office 365またはExcel 2016以降のバージョンを使用している場合、「Power Query」は強力なツールであり、区切りテキストを複数行や列に分割するのに役立ちます。元のデータが変更されたときに分割データを更新したい場合に便利です。以下の手順に従って完了してください：

ステップ1: データテーブルをPower Queryに取り込む

1. 使用したいデータ範囲を選択し、「データ」>「表/範囲から」をクリックします（スクリーンショット参照）：

 ヒント: Excel 2019およびOffice 365では、「データ」>「表/範囲から」をクリックします。

2. ポップアップされた「表の作成」ダイアログボックスで、「OK」ボタンをクリックして表を作成します（スクリーンショット参照）：

3. これで、「Power Query エディター」ウィンドウにデータが表示されます（スクリーンショット参照）：

ステップ2: Power Queryで変換を行う

1. 分割したい列を選択します。次に、「ホーム」>「列の分割」>「区切り文字で」をクリックします（スクリーンショット参照）：

2. 「区切り文字で列を分割」ダイアログボックスで：

カンマ、スペース、セミコロンなどでテキスト文字列を分割するには、次のようにします：

• 1). 「区切り文字の選択または入力」ドロップダウンリストからデータが区切られている区切り文字を選択します；
• 2). 「分割位置」セクションから「区切り文字の各出現箇所」を選択します；
• 3). 次に、「詳細オプション」をクリックしてセクションを展開し、「分割先」で「行」を選択します；
• 4). 最後に、「OK」をクリックしてこのダイアログボックスを閉じます。

改行でテキスト文字列を複数行に分割するには、次のようにします：

• 1). 「区切り文字の選択または入力」ドロップダウンリストから「カスタム」を選択します；
• 2). 「特殊文字の挿入」ドロップダウンから「改行」をクリックすると、文字列「#(lf)」が「カスタム」の下のテキストボックスに自動的に挿入されます；
• 3). 「分割位置」セクションから「区切り文字の各出現箇所」を選択します；
• 4). 次に、「詳細オプション」をクリックしてセクションを展開し、「分割先」で「行」を選択します；
• 5). 最後に、「OK」をクリックしてこのダイアログボックスを閉じます。

3. これで、選択したデータが以下のように複数行に分割されました（スクリーンショット参照）：

ステップ3: Power QueryをExcelテーブルに出力する

1. 次に、データをワークシートに出力する必要があります。「ホーム」>「閉じて読み込む」>「閉じて読み込む / 閉じて読み込む先」をクリックします（ここでは「閉じて読み込む」をクリックします）（スクリーンショット参照）：

 ヒント: 「閉じて読み込む」をクリックすると、データは新しいワークシートに出力されます。「閉じて読み込む先」オプションをクリックすると、データは必要な他のシートに出力されます。

2. 最終的に、データは新しいワークシートに読み込まれます（スクリーンショット参照）：

 ヒント: 元のテーブルのデータを頻繁に更新する必要がある場合でも、心配しないでください。結果テーブルを右クリックして「更新」をクリックするだけで、新しい結果を動的に取得できます。

関連記事：