【マクロ・VBA】InStr関数を用いてセルを分割する方法

セルの文字列を指定した区切り文字で分割する

例えば、文字列内の「,」の位置を検索するなど、InStr関数は、検索する文字列、検索を開始する位置、検索する文字列を指定します。セルの文字列が一定の形式に従っている場合、その特定の文字列を指定して分割することができます。しかし、セルの文字列が複雑な形式である場合や、区切り文字が複数存在する場合には、正規表現などのより高度な方法を検討する必要があります。

InStr関数を使用してセルを分割する

Sub 文字列の分割()
    '変数の宣言
    Dim a As Long 'ループ用変数
    Dim b As Long 'ループ用変数
    Dim str As String 'セルの文字列を格納する変数
    Dim pos As Long '区切り文字の位置を格納する変数
    Dim i As Long 'ループ用変数
    
    '開始行を指定
    a = 2
    
    '無限ループ
    Do
        'セルの文字列を取得
        str = Cells(a, 2)
        
        '区切り文字の位置を取得
        pos = InStr(str, "　")
        
        '区切り文字が見つかるまで繰り返し
        i = 0
        Do While pos > 0
            '区切り文字より前の文字列を出力
            Cells(a, 3 + i) = Left(str, pos - 1)
            
            'セルの文字列から区切り文字までを削除
            str = Mid(str, pos + 1)
            
            '次の区切り文字の位置を取得
            pos = InStr(str, "　")
            
            '出力する列を1つずつずらす
            i = i + 1
        Loop
        
        '残りの文字列を出力
        Cells(a, 3 + i) = str
        
        '次の行に移動
        a = a + 1
        
        '次のセルが空欄の場合はループを終了
        If Cells(a, 2) = "" Then
            Exit Do
        End If
    Loop
    
    '処理が完了したことを知らせるメッセージボックスを表示
    MsgBox "完了"
End Sub

解説

最初に、2番目の行から処理を開始するように変数aに値2が代入されます。

その後、Doループが開始されます。このループでは、まず、セルB2の文字列をstr変数に格納します。その後、InStr関数を使用して、区切り文字の位置を取得します。InStr関数は、指定された文字列内で指定された文字列が最初に現れた位置を返します。この場合、”　”で区切られた位置が取得されます。

Left, Mid, Right関数を使用してセルを分割する

Sub 文字列の分割()
    '変数の宣言
    Dim a As Long 'ループ用変数
    Dim b As Long 'ループ用変数
    Dim str As String 'セルの文字列を格納する変数
    Dim pos As Long '区切り文字の位置を格納する変数
    Dim i As Long 'ループ用変数
    
    '開始行を指定
    a = 2
    
    '無限ループ
    Do
        'セルの文字列を取得
        str = Cells(a, 2)
        
        '区切り文字の位置を取得
        pos = InStr(str, "　")
        
        '区切り文字が見つかるまで繰り返し
        i = 0
        Do While pos > 0
            '区切り文字より前の文字列を出力
            Cells(a, 3 + i) = Left(str, pos - 1)
            
            'セルの文字列から区切り文字までを削除
            str = Mid(str, pos + 1)
            
            '次の区切り文字の位置を取得
            pos = InStr(str, "　")
            
            '出力する列を1つずつずらす
            i = i + 1
        Loop
        
        '残りの文字列を出力
        Cells(a, 3 + i) = str
        
        '次の行に移動
        a = a + 1
        
        '次のセルが空欄の場合はループを終了
        If Cells(a, 2) = "" Then
            Exit Do
        End If
    Loop
    
    '処理が完了したことを知らせるメッセージボックスを表示
    MsgBox "完了"
End Sub

解説

まず、変数aに2が代入され、Doループが開始されます。

ループ内で、まず、Cells関数を使用して、指定された行番号と列番号からセルの値を取得します。この値はstr変数に格納されます。次に、InStr関数を使用して、指定された区切り文字が最初に現れた位置を取得します。この位置はpos変数に格納されます。

次に、Do Whileループが開始され、区切り文字が見つかるまで繰り返します。最初に、区切り文字より前の文字列を出力するために、Left関数を使用して、str変数から区切り文字の直前までの文字列を取得し、Cells関数を使用して、適切な列に出力します。次に、Mid関数を使用して、str変数から区切り文字の次の文字列を取得し、次の区切り文字の位置をpos変数に格納します。