Office TANAKA - Excel VBA Tips[Split関数で文字列を区切る]

Split関数は、ある区切り文字で区切られた文字列を、その区切り文字で区切り、区切られた各文字列を、一次元配列として返します。言葉で表すとややこしいですが、要するに次のような処理をしてくれます。

Split関数の書式は次の通りです。

Split(元の文字列 , 区切り文字)

上の例なら、次のようになります。

Split("123,田中,tanaka" , ",")

Split関数は配列を返します。したがって、それを受け取る変数は、バリアント型変数か、動的配列でなければいけません。一般的にはバリアント型変数に受けます。Split関数の返り値(配列)を受け取ったバリアント型変数は、それ以降、配列として操作できます。

このとき注意しなければいけないのは、VBAの一般的な設定では、Split関数が返す配列の要素は0から始まるということです。上の例をVBAのコードで書くと次のようになります。

Sub Sample1()
    Dim tmp As Variant
    tmp = Split("123,田中,tanaka", ",")
    MsgBox tmp(0)
    MsgBox tmp(1)
    MsgBox tmp(2)
End Sub

配列の要素数を調べる

上の例では、元の文字列が「"123,田中,tanaka"」と分かっていたので、Split関数で配列に分解した結果が、3つの要素になることも分かっていました。でも、元の文字列に、いくつの要素が含まれているか分からないときもあります。そんなときは、配列の要素数を調べる関数を使います。

LBound関数…引数に指定した配列の、最も小さい要素番号を返す
UBound関数…引数に指定した配列の、最も大きい要素番号を返す

これらの関数を使うと、先のコードは次のように書けます。

Sub Sample2()
    Dim tmp As Variant, i As Long
    tmp = Split("123,田中,tanaka", ",")
    For i = LBound(tmp) To UBound(tmp)
        MsgBox tmp(i)
    Next i
End Sub

もっとも、一般的なVBAでは、Split関数が返す配列の最小要素番号は0と決まっていますから、LBound関数の代わりに0を指定することが多いです。

Sub Sample2()
    Dim tmp As Variant, i As Long
    tmp = Split("123,田中,tanaka", ",")
    For i = 0 To UBound(tmp)
        MsgBox tmp(i)
    Next i
End Sub

Split関数の使用例

上記のように、Split関数はCSVデータやTSVデータ(Tab区切りデータ)などをワークシートに読み込むときに活躍します。ほかにも、Split関数はさまざま場面で役立ちます。

下図は、A列に氏名が入力されています。苗字と名前の間には半角スペースが入力されているとします。Split関数の区切り文字に半角スペースを指定すれば、こうしたデータを簡単に分割できます。

Sub Sample3()
    Dim i As Long, tmp As Variant
    For i = 2 To 22
        tmp = Split(Cells(i, 1), " ")
        Cells(i, 2) = tmp(0)
        Cells(i, 3) = tmp(1)
    Next i
End Sub

区切り文字には日本語も指定できますので、上と同じ考え方で住所を分割することも可能です。下図は、A列に住所が入力されています。Split関数を使って、県名を抜き出します。

Sub Sample4()
    Dim i As Long, tmp As Variant
    For i = 2 To 13
        tmp = Split(Cells(i, 1), "県")
        Cells(i, 2) = tmp(0) & "県"
        Cells(i, 3) = tmp(1)
    Next i
End Sub

あるいは、こんなことも。

Sub Sample5()
    Dim i As Long, tmp As Variant
    For i = 2 To 11
        tmp = Split(Cells(i, 1), "(")
        Cells(i, 2) = tmp(0) - Replace(tmp(1), ")", "")
    Next i
End Sub

こんなことも。

Sub Sample6()
    Dim i As Long, tmp As Variant
    For i = 2 To 6
        tmp = Split(Cells(i, 1), "\")
        Cells(i, 2) = tmp(UBound(tmp) - 1)
        Cells(i, 3) = tmp(UBound(tmp))
    Next i
End Sub

こんなことまで。

Sub Sample7()
    Dim i As Long, tmp As Variant
    For i = 2 To 6
        tmp = Split(Cells(i, 1), vbLf)
        Cells(i, 2) = tmp(0)
        tmp = Split(tmp(1), "：")
        Cells(i, 3) = tmp(1)
    Next i
End Sub