Excelでデータを区切り文字で分割し、列に並べ替える方法は？

Excelで作業する際、セル内にカンマやセミコロンなどの区切り文字で複数の値が入力されていることはよくあります。このようなセル範囲を特定の区切り文字で分割し、すべての抽出された値を下記スクリーンショットのように1つの列にまとめたい場合、より効率的にデータ整理や分析ができる方法があります。

データを区切り文字で分割し、1列に並べ替える方法

• 数式を使う方法（Excel365）
• Kutools for Excelを使う方法
• VBAコードを使う方法

数式（Excel365）で区切り文字でデータを分割し、列に並べ替える方法

Excel365では、TEXTSPLIT関数やTEXTJOIN関数といった最新の関数を活用し、データを動的に変換できます。

結果を表示したいセルをクリックし、以下の数式を入力してEnterキーを押します。カンマ区切りのテキストが一度にリストに変換されます。スクリーンショットをご参照ください:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A2:A5), ", "))

Kutools for Excelを使って区切り文字でデータを分割し、列に並べ替える方法

古いバージョンのExcel（2016/2019などTEXTSPLIT関数がない場合）を使用している方には、Kutools for Excelの「複数行に分割する」機能がおすすめです。この機能を利用することで、特定の区切り文字を基準にセル内容をすぐに1列に分割でき、作業時間と労力を大幅に削減できます。

Kutools for Excelをインストールした後、次の手順で操作してください。

1. クリック Kutools > 結合・分割 > 複数行に分割するスクリーンショット参照:
   
2. 「 複数行に分割する 」ダイアログボックスで、以下の操作を指定します：
   • 分割したいセルを選択します；
   • 「区切り文字」セクションで「その他」を選び、希望する区切り文字（例: カンマ+スペース）を入力してセルの内容をどのように分割するか設定します；
   • 最後に、クリック OK ボタン。
     

結果：選択したセルが指定した区切り文字で1列に分割されます。

VBAコードで区切り文字ごとにデータを分割し、1列に並べ替える方法

ここでは、VBAを使用してセル内容を区切り文字で分割し、すべての結果を1列に並べ替える手順をご説明します。

1. Alt + F11キーを押してMicrosoft Visual Basic for Applicationsウィンドウを表示します。

2. 挿入 > モジュール をクリックし、以下のコードを新しい モジュールウィンドウに貼り付けます。

VBA: 分割と変換

Sub Vertical()
'Updateby Extendoffice
    Dim i As Long, strTxt As String
    Dim startP As Range
    Dim xRg As Range, yRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox _
    (Prompt:="Range Selection...", _
    Title:="Kutools For Excel", Type:=8)
    i = 1
    Application.ScreenUpdating = False
    For Each yRg In xRg
        If i = 1 Then
            strTxt = yRg.Text
            i = 2
        Else
            strTxt = strTxt & "," & yRg.Text
        End If
    Next
    Application.ScreenUpdating = True
    Set startP = Application.InputBox _
    (Prompt:="paste range...", _
    Title:="Kutools For Excel", Type:=8)
    ary = Split(strTxt, ",")
    i = 1
    Application.ScreenUpdating = False
    For Each a In ary
        startP(i, 1).Value = a
        i = i + 1
    Next a
    Application.ScreenUpdating = True
End Sub

3. F5 キーでコードを実行すると、分割・変換したい範囲を選択するダイアログが表示されます。

4.クリック OK。さらに、データを配置する場所を選択するダイアログが表示されます。

5.クリック OKで、選択範囲がカンマで分割・変換されます。

🔚まとめ

どの方法でも区切り文字付きデータの分割という課題を効果的に解決できますが、最適な方法はご自身の技術レベルや要件によって異なります。

• 多くのExcel365ユーザーには、数式による方法がパワーとメンテナンス性のバランスに優れています。
• プログラミング経験のない方が頻繁かつ複雑な変換を行う場合は、Kutoolsの利用が最適です。
• 企業環境でのカスタマイズや自動化ソリューションには、VBAが最大限のコントロールを提供します。

ぜひご自身のニーズにあった方法を選択してください。より多くのExcelのコツやテクニックにご興味があれば、当ウェブサイトではExcelをマスターするための数千ものチュートリアルを提供しています。