マクロを利用してセルの文字列を区切り文字で分割する - VBA Split 関数の利用 - Excel

マクロを利用してセルの文字列を区切り文字で分割するコードを紹介します。

概要

こちらの記事では数式を利用して、セルの文字列を区切り文字で分割する方法を紹介しました。 この記事では、マクロを利用してVBAでセルの文字列を区切り文字で分割するコードを紹介します。

Split関数の書式

文字列を区切り文字で分割するには、Split関数を利用します。Split関数の書式は次の通りです。

区切り文字列を省略した場合は " " (空白)で文字区切りします。

部分文字列の返される個数を指定する場合は、第三引数を与えます。-1を与えるとすべての部分文字列が返されます。

比較オプションを設定する書式もあります。

定数名	値	説明
bUseCompareOption	-1	Option Compare を利用した比較をします。
vbBinaryCompare	0	バイナリ比較をします。
vbTextCompare	1	テキスト比較をします。
vbDatabaseCompare	2	データベース内の情報に基づいて比較をします。(Microsoft Access のみ。)

手順

Excelを起動し、D列に以下の文字列を入力します。

101:ぺんぎんクッキー:90

102:らくだキャラメル:40

103:しろくまアイス:220

104:ぱんだカステラ:320

105:かるがもサブレ:80

ツールリボンで[開発]タブをクリックして選択し、[挿入]ボタンをクリックして、フォームコントロールのボタンを配置してマクロを作成します。 手順の詳細はこちらの記事を参照してください。


Visual Basic for Applications のコードエディタで以下のコードを記述します。

Sub ボタン1_Click()

For i = 1 To 10
  Dim input_value As String
  Dim result_value() As String
  
  input_value = Cells(i, 4).Value
  result_value = Split(input_value, ":")
  For j = LBound(result_value) To UBound(result_value)
    Cells(i, 5 + j).Value = result_value(j)
  Next
Next

End Sub

コードの解説

セルから文字列を読み込むための変数と、区切り文字で分割後の結果を保存する文字列配列変数を宣言します。

  Dim input_value As String
  Dim result_value() As String

4列目のセルの値を input_value 変数に代入します。

  input_value = Cells(i, 4).Value

input_valueの文字列を ":" で区切ります。結果を result_value 変数に代入します。

  result_value = Split(input_value, ":")

結果の文字列の配列をループして5列目からセルに値を設定します。

  For j = LBound(result_value) To UBound(result_value)
    Cells(i, 5 + j).Value = result_value(j)
  Next

Forループで全体の処理を10回繰り返し、10行目まで同じ処理を繰り返します。 Forループの書式についてはこちらの記事を参照してください。

実行結果

コード記述後に、ボタンをクリックします。


クリックすると、D列の文字列を":" で分割し、分割したそれぞれの値を E列からセルに設定します。


VBAでSplit関数を利用して、文字列を区切り文字で分割できました。

補足:マクロを利用するメリット

スピルが導入されたExcelでは、今回紹介した方法と同じ処理をTEXTSPLIT関数で実現できます。

TEXTSPLIT関数がない古いExcelでは同様の処理をする場合、それぞれのセルに数式を設定する必要があるため、 作業量が多くなることや、区切り文字数が不定の場合、十分大きい列まで数式を設定する必要があり設定が面倒なため、 VBAでマクロを記述したほうがシンプルに実現できます。

また、分割後の値をさらに処理する場合や、順番を入れ替えたり、間の列を空けて別の列のセルに設定したい場合も、TEXTSPLIT関数では対応が難しいため、 VBAマクロを記述したほうが簡単に実現できます。