VBA Split関数 – 文字列を配列に分割する

VBAのSplit関数を使用する

VBAのSplit関数は、特定の区切り文字（カンマ、スペース、コロンなど）に基づいて、テキスト文字列を複数の部分文字列に分割します。文字列の中から区切り文字を探し、その値を抽出するコードを書くよりも簡単に使うことができます。

カンマ区切り値（CSVファイル）から1行ずつ読み込む場合や、住所が1行にまとまっているが、複数行として見たい場合などに便利です。

構文は次のとおりです。

Split Expression, Delimiter[オプション], Limit[オプション], Compare[オプション]。

VBAのSplit関数には4つのパラメータがあります。

• Expression
  分割したいテキストの文字列。
• Delimiter(オプション) – 文字列または印字不可能な文字
  分割に使用される区切り文字（デリミタ）を指定します。区切り文字が指定されない場合は、デフォルトのスペースが使用されます。
• Limit (オプション) – 数値
  分割される数を定義します。空白の場合、文字列の中で利用可能なすべての分割が行われます。1に設定された場合は、分割は行われません。 基本的には、文字列の先頭から特定の数の値を分離したい場合に使用します。たとえば、文字列が非常に長く、最初の3つだけを分割する必要がある場合などです。
• Compare (オプション)
  区切り文字がテキスト文字の場合、大文字と小文字を区別するかどうかを指定します。値は、vbBinaryCompare（大文字小文字を区別する）とvbTextCompare（大文字小文字を区別しない）です。

Split関数は、常に配列を返します。

Split関数の簡単な例

Sub SplitExample()
'変数の定義
Dim MyArray() As String, MyString As String, I As Variant
'スペースで区切られたサンプル文字列
MyString = "One Two Three Four"
'文字列の構成部分を分割するためにSplit関数を使用する
MyArray = Split(MyString)
'作成された配列を繰り返し、各値を表示する
For Each I In MyArray
    MsgBox I
Next I
End Sub

この例では、すべての単語の間にスペースがあるため、区切り文字は指定されていませんが、デフォルトの区切り文字であるスペースが使用されます。 配列は次元を持たず、文字列として設定されています。For…Nextループで使用する変数Iは、Variant型として宣言する必要があります。 このコードが実行されると、メッセージボックスが4回表示されます。

文字列の単語の間にダブルスペースがある場合、何も入っていないにもかかわらず、分割されてしまう点に注意してください。あなたが意図した結果ではない可能性があります。

なお、この問題は、Replace関数を使ってダブルスペースをシングルスペースに置き換えることで解決できます。

MyString = Replace(MyString, "  ", " ")

末尾や先頭のスペースも、空のスプリットを生成して問題を引き起こす可能性があります。 このようなスペースは、非常に見づらい場合があります。このような余計なスペースは、Trim関数を使って取り除くことができます。

MyString = Trim(MyString)

区切り文字を指定してSplit関数を使用する

区切り文字として、セミコロン（;）を使うこともできます。これは、電子メールのアドレス文字列で、アドレスを区切るためによく使われます。あなた宛に送られた電子メールを多くの同僚と共有し、ワークシートで誰に送られたかを一覧で確認したい場合、メールのToまたはCCボックスからメールアドレスをコピーして、コードに含めることができます。

Sub SplitBySemicolonExample()
'変数の定義
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'セミコロンで区切られた文字列のサンプル
MyString = "john@myco.com;jane@myco.com;bill@myco.com;james@myco.com"
'文字列の構成部分を分割するためにSplit関数を使用する
MyArray = Split(MyString, ";")
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'配列を繰り返し処理する
For N = 0 To UBound(MyArray)
'各メールアドレスをワークシートの1列目に配置する
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

For…Nextループは、配列を繰り返し処理するために使用されることに注意してください。配列の最初の要素は常に0から始まり、要素の最大数を得るためにUpper Bound Functionが使用されます。 このコードを実行すると、ワークシートは次のようになります。

Split関数でLimitパラメータを使用する

Limit パラメータは、文字列の先頭から特定の回数だけ分割することを可能にします。 残念ながら、開始位置や分割する範囲を指定することはできないので、かなり基本的なものとなっています。これを行うためのVBAコードを自作することもできますが、それについてはこの記事の後半で説明します。

Sub SplitWithLimitExample()
'変数の作成
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'カンマ区切りのサンプル文字列
MyString = "One,Two,Three,Four,Five,Six"
'文字列の構成部分を分割するためにSplit関数を使用する
MyArray = Split(MyString, ",", 4)
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'配列を繰り返し処理する
For N = 0 To UBound(MyArray)
    '各分割をワークシートの1列目に配置する
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

このコードを実行すると、ワークシートは次のようになります。

最初の3つの分割された値だけが別々に表示されます。後の3つの値は1つの長い文字列として表示され、分割はされません。 文字列内の区切り文字の数よりも大きな制限値を選択しても、エラーにはなりません。 文字列は、制限値が指定されていない場合と同様に、すべての構成要素に分割されます。

Split関数でCompareパラメータを使用する

Compare パラメータは、区切り文字が大文字と小文字を区別するかどうかを決定します。区切り文字がカンマやセミコロン、コロンのような場合には適用されません。

注意：モジュール全体の大文字と小文字の区別をなくすために、常にモジュールの先頭にOption Compare Textを配置することができます。

Sub SplitByCompareExample()
'変数の作成
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'Xで区切られたサンプル文字列
MyString = "OneXTwoXThreexFourXFivexSix"
'文字列の構成部分を分割するためにSplit関数を使用する
MyArray = Split(MyString, "X", , vbBinaryCompare)
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'配列を繰り返し処理する
For N = 0 To UBound(MyArray)
    '各分割をワークシートの1列目に配置する
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

この例では、分割する文字列は X を区切り文字として使用しています。しかし、この文字列には、大文字と小文字の X が混在しています。Split関数のCompareパラメータでは、大文字のXが使用されています。 CompareパラメータをvbBinaryCompareに設定すると、小文字の x は無視され、ワークシートは次のように表示されます。

Compare パラメータを vbTextCompare に設定すると、小文字の x も分割に使用され、ワークシートは以下のようになります。

セル A6 の値は、小文字の ‘x’ を含んでいるため、切り捨てられてしまうことに注意してください。分割は大文字と小文字を区別しないので、部分文字列の一部を構成する区切り記号はすべて分割の原因となります。

これは、テキストを区切り文字として使用する際に留意すべき重要なポイントで、意図と異なる間違った結果になってしまう可能性があります。

印字不可能な文字を区切り文字として使用する

キャリッジリターン（改行）のような印刷不可能な文字を区切り文字として使用することもできます。 ここでは、vbCr としてキャリッジリターンを指定しています。

Sub SplitByNonPrintableExample()
'変数の作成
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'キャリッジリターンを区切り文字としたサンプル文字列
MyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"
'Split関数を使って、文字列の構成部分を分割する
MyArray = Split(MyString, vbCr, , vbTextCompare)
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'配列を繰り返し処理する
For N = 0 To UBound(MyArray)
    '各分割をワークシートの1列目に配置する
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

この例では、vbCr（キャリッジリターン文字）をデリミタとして文字列を構築しています。 このコードを実行すると、ワークシートは次のような表示になります。

Join関数で分割した文字列を再結合する

Join関数は、配列のすべての要素を、指定された区切り文字を使用して再結合します。区切り文字が指定されない場合は、スペースが使用されます。

Sub JoinExample()
'変数の作成
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
Dim Target As String
'カンマ区切りのサンプル文字列
MyString = "One,Two,Three,Four,Five,Six"
'セルA1にMyStringを配置
Range("A1").Value = MyString
'Split関数で文字列の構成部分を分割する
MyArray = Split(MyString, ",")
'Join関数を使って、セミコロン区切りで元の文字列を再作成する
Target = Join(MyArray,";")
'結果の文字列をセルA2に配置
Range("A2").Value = Target
End Sub

このコードは、カンマ区切りの文字列を配列に分割し、セミコロン区切りで再び結合するものです。 このコードを実行すると、ワークシートは次のようになります。

セル A1 にはカンマで区切られた元の文字列が、セル A2 にはセミコロンで区切られた新しい文字列が表示されます。

Split関数でワードカウントを行う

Excel VBAの文字列変数は最大2Gbの長さになることを念頭に置いて、分割関数を使ってテキストのワードカウントを行うことができます。 もちろん、Microsoft Wordは自動的にこれを行いますが、これは単純なテキストファイルや他のアプリケーションからコピーしたテキストに便利です。

Sub NumberOfWordsExample()
'変数を作成する
Dim MyArray() As String, MyString As String
'スペースで区切られたサンプル文字列
MyString = "One Two Three Four Five Six"
'ダブルスペースを削除
MyString = Replace(MyString, "  ", " ")
'先頭または末尾のスペースを削除する
MyString = Trim(MyString)
'文字列の構成部分を分割するためにSplit関数を使用する
MyArray = Split(MyString)
UBound関数を使って単語数を表示する
MsgBox "単語数" & UBound(MyArray) + 1
End Sub

このワードカウントコードの危険な点は、ダブルスペースや文頭、文末のスペースによって分割されることです。 もしこれらが存在すれば、余分な単語としてカウントされ、単語数は不正確なものになります。 このコードでは、Replace関数とTrim関数を使って、これらの余分なスペースを削除しています。 最後のコードでは、UBound関数を使って配列の最大要素数を取得し、それを1つ増やすことで、見つかった単語数を表示しています。

住所をワークシートセルに分割する

メールアドレスは、カンマで区切られた長い文字列になっていることがよくあります。住所の各パートを別々のセルに分割したい場合もあるでしょう。

Sub AddressExample()
'変数の作成
Dim MyArray() As String, MyString As String, N As Integer
'マイクロソフト株式会社の住所を文字列で設定
MyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'カンマ区切りで文字列を分割するためにsplit関数を使用する
MyArray = Split(MyString, ",")
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'配列を繰り返し処理する
For N = 0 To UBound(MyArray)
    '分割したそれぞれの文字列をワークシートの最初の列に配置する
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

このコードを実行すると、カンマ区切りで住所の各行を別々のセルに入れることができます。

もし郵便番号 (配列の最後の要素) だけを返したいのなら、次のようなコードを使用します。

Sub AddressZipCodeExample()
'変数の作成
Dim MyArray() As String, MyString As String, N As Integer, Temp As String
'マイクロソフト株式会社の住所を文字列で設定
MyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'カンマ区切りで文字列を分割するためにsplit関数を使用する
MyArray = Split(MyString, ",")
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'セルA1に郵便番号を入れる
Range("A1").Value = MyArray(UBound(MyArray))
End Sub

これは、UBound関数で求めた配列の最後の要素だけを使用します。 一方、住所ラベルに印刷できるように、1つのセル内のすべての行を表示したい場合があります。

Sub AddressExample()
'変数の作成
Dim MyArray() As String, MyString As String, N As Integer, Temp As String
'マイクロソフト株式会社のアドレスを文字列で設定
MyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'カンマ区切りで文字列を分割するためにsplit関数を使用する
MyArray = Split(MyString, ",")
'ワークシートのクリア
ActiveSheet.UsedRange.Clear
'配列を繰り返し処理する
For N = 0 To UBound(MyArray)
    '配列の各要素と改行文字を文字列に配置する
    Temp = Temp & MyArray(N) & vbLf
Next N
'文字列をワークシートに配置
Range("A1") = Temp
End Sub

この例は、配列のすべての要素から一時的な文字列を作成する以外は、先の例と同じように動作しますが、各要素の後に改行文字が挿入されます。 このコードを実行すると、ワークシートは次のような表示になります。

分割した文字列をワークシートセルに配置する

たった1つのコマンドで、分割された配列をワークシートセルにコピーすることができます。

Sub CopyToRange()
'変数の作成
Dim MyArray() As String, MyString As String
'スペースで区切られたサンプル文字列
MyString = "One,Two,Three,Four,Five,Six"
'文字列の構成部分を分割するためにSplit関数を使用する
MyArray = Split(MyString, ",")
'配列をワークシートにコピー
Range("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)
End Sub

このコードが実行されると、ワークシートは次のようになります。

指定した点から分割できる新しい関数を作成する

Split関数のLimitパラメータでは、分割を停止する上限を指定することしかできず、分割は常に文字列の先頭から開始されます。

同様の機能で、文字列内の分割開始位置と、その位置から何回分割するかを指定できると非常に便利です。また、配列の最後の要素として巨大な文字列の値を持つのではなく、指定した分割数だけを配列に抽出することができます。

これを行うための関数（SplitSlicerと呼びましょう）をVBAで簡単に作ることができます。

Function SplitSlicer(Target As String, Del As String, Start As Integer, N As Integer)
'配列変数を作成
Dim MyArray() As String
'区切り文字を使用してStart変数を使用して分割をキャプチャする
MyArray = Split(Target, Del, Start)
'startパラメータが分割数より大きいかどうかをチェック - これは問題を引き起こす可能性があります
If Start > UBound(MyArray) + 1 Then
    'エラーを表示し、関数を終了します
    MsgBox "開始パラメータが分割可能な数よりも大きい値です"
    SplitSlicer = MyArray
    Exit Function
End If
'配列の最後の要素を文字列に入れる
Target = MyArray(UBound(MyArray))
'NをLimitとして文字列を分割する
MyArray = Split(Target, Del, N)
'コードが最後の要素を削除するので、上限がゼロより大きいことを確認します。
If UBound(MyArray) > 0 Then
    'ReDim を使用して配列の最後の要素を削除する
    ReDim Preserve MyArray(UBound(MyArray) - 1)
End If
'新しい配列を返す
SplitSlicer = MyArray
End Function

この関数は4つのパラメータで構成されています。

• Target – 文字列
  これは分割したい入力文字列です。
• Del – 文字列または印字不可能な文字
  これは、カンマやコロンなど、使用する区切り文字です。
• Start – 数値
  スライスの分割の開始点です。
• N–  数値
  スライス内で実行する分割の数です。

これらのパラメータはいずれもオプションではありませんし、デフォルト値もありませんが、さらに拡張したい場合は、関数のコードにそれを組み込むことができます。

この関数は Split 関数を使用して、Start パラメータを Limit とした配列を作成します。 つまり、配列の要素には start パラメータまでの分割結果が格納されますが、 残りの文字列は最後の要素となり、分割は行われないということです。

配列の最後の要素は、UBound 関数を使って文字列に戻し、これがどの要素であるかを判断します。

その後、Nをリミット変数として、文字列が再び配列に分割されます。 つまり、文字列の分割は位置Nまで行われ、それ以降は文字列の残りの部分が配列の最後の要素となります。

配列に特定の要素だけを残したいので、最後の要素を削除するためにReDim文が使われます。 Preserveパラメータを使用しないと、配列のすべてのデータが失われることに注意してください。

新しい配列は、それが呼び出されたコードに返されます。

このコードは「エラー対策済み」であることに注意してください。 ユーザーはしばしば、あなたが考えもしなかったような奇妙なことをするものです。 たとえば、Start や N のパラメータを文字列の分割可能な数より大きくしてこの関数を使おうとすると、失敗する可能性が高くなります。 そのため、Start 値をチェックするコードと、配列に対して ReDim 文を使用する際に削除できる要素があることを確認するコードが含まれています。

以下は、この関数をテストするコードです。

Sub TestSplitSlicer()
'変数の作成
Dim MyArray() As String, MyString As String
'カンマ区切りでサンプル文字列を定義する
MyString = "One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten"
'Splitslicer関数を使用して新しい配列を定義する
MyArray = SplitSlicer(MyString, ",", 4, 3)
'アクティブシートをクリアする
ActiveSheet.UsedRange.Clear
'配列をワークシートにコピーする
Range("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)
End Sub

このコードを実行すると、ワークシートはこのようになります。