ExcelでVBAを操作する場合、区切り文字に基づいて文字列を異なる部分に分割する必要がある

たとえば、アドレスがある場合は、VBA Split関数を使用して、アドレスのさまざまな部分をコンマで区切って取得できます（この場合は区切り文字です）。

SPLITは、excel VBAの組み込み文字列関数で、区切り文字に基づいてテキスト文字列を分割するために使用できます。,

このチュートリアルでは、

Excel VBA分割関数–構文

Split ( Expression, , , )
  • 式：これは、区切り文字に基づいて分割する文字列です。 たとえば、アドレスの例の場合、アドレス全体が”式”になります。 これが長さがゼロの文字列（””）の場合、分割関数は空の配列を返します。
  • 区切り文字:これはオプションの引数です。 これは、’Expression’引数を分割するために使用される区切り文字です。, アドレスの例の場合、コンマはアドレスを異なる部分に分割するために使用される区切り文字です。 この引数を指定しない場合、スペース文字がデフォルトの区切り文字と見なされます。 長さがゼロの文字列（””）を指定すると、関数によって’Expression’文字列全体が返されます。
  • Limit:これはオプションの引数です。 ここでは、返す部分文字列の合計数を指定します。 たとえば、’Expression’引数から最初の三つの部分文字列のみを返したい場合、これは3になります。, この引数を指定しない場合、デフォルトは-1で、すべての部分文字列が返されます。
  • Compare:これはオプションの引数です。 ここでは、部分文字列を評価するときにSPLIT関数で実行する比較のタイプを指定します。 次のオプションを使用できます。
    • Compareが0の場合:これはバイナリ比較です。 これは、区切り文字がテキスト文字列（ABCとしましょう）の場合、大文字と小文字が区別されることを意味します。 ‘ABC’は’abc’と等しくありません。
    • Compareが1の場合：これはテキスト比較です。, これは、区切り文字がテキスト文字列（ABCとしましょう）の場合、’Expression’文字列に’abc’があっても、区切り文字とみなされることを意味します。

分割関数の基本について説明したので、いくつかの実用的な例を見てみましょう。

例1-文中の単語を分割します

“The Quick Brown Fox Jumps Over The Lazy Dog”というテキストがあるとします。

SPLIT関数を使用して、この文の各単語を配列内の別々の項目として取得できます。,

以下のコードは次のようになります。

Sub SplitWords()Dim TextStrng As StringDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result() = Split(TextStrng)End Sub

コードは何も役に立ちませんが、VBAの分割関数が何をするかを理解するのに役立ちます。

分割関数は、テキスト文字列を分割し、結果の配列に各単語を割り当てます。

この場合、

  • Result(0)は値”The”を格納します
  • Result(1)は値”Quick”を格納します
  • Result(2)は値”Brown”などを格納します。

この例では、分割するテキストである最初の引数のみを指定しました。, 区切り文字が指定されていないため、デフォルトの区切り文字としてスペース文字を使用します。

重要な注意：

  1. VBA分割関数は、基数0から始まる配列を返します。
  2. SPLIT関数の結果が配列に割り当てられるとき、その配列は文字列データ型として宣言されなければなりません。 Variantデータ型として宣言すると、型の不一致エラーが表示されます）。 上記の例では、Result（）を文字列データ型として宣言していることに注意してください。,

例2-文中の単語数を数える

SPLIT関数を使用して、文中の単語数の総数を取得できます。 ここでのトリックは、テキストを分割するときに取得する配列内の要素の数を数えることです。

以下のコードは、ワード数を含むメッセージボックスを表示します。

Sub WordCount()Dim TextStrng As StringDim WordCount As IntegerDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result = Split(TextStrng)WordCount = UBound(Result()) + 1MsgBox "The Word Count is " & WordCountEnd Sub

この場合、UBound関数は配列の上限（つまり、配列が持つ要素の最大数）を, 配列の底辺は0なので、合計ワード数を取得するために1が加算されます。同様のコードを使用して、テキストを入力として受け取り、単語数を返すVBAでカスタム関数を作成できます。

以下のコードでこの関数が作成されます。

作成されると、WordCount関数を他の通常の関数と同じように使用できます。

この関数は、単語の間の先頭、末尾、および二重スペースも処理します。 これは、VBAコードのTRIM関数を使用することで可能になりました。,

この式が文中の単語数を数えるためにどのように機能するか、またはVBA以外の式の単語数を取得する方法について詳しく知りたい場合は、this tutorialをチェックしてください。

例3-スペース文字以外の区切り文字の使用

前の二つの例では、SPLIT関数で一つの引数しか使用しておらず、残りはデフォルトの引数でした。

他の区切り文字を使用する場合は、分割式でそれを指定する必要があります。,

以下のコードでは、SPLIT関数は区切り文字としてカンマに基づいて配列を返し、各単語を含むメッセージを別々の行に表示します。

上記のコードでは、For Nextループを使用して、’Result’配列の各要素を通過し、それを’DisplayText’変数に割り当てます。

例4-アドレスを三つの部分に分割する

SPLIT関数を使用すると、取得する分割の数を指定できます。 たとえば、何も指定しない場合、区切り文字のすべてのインスタンスが文字列を分割するために使用されます。,

しかし、制限として3を指定すると、文字列は三つの部分にのみ分割されます。

たとえば、次のアドレスがある場合：

2703 Winifred Way, Indianapolis, Indiana, 46204

VBAのSplit関数を使用して、このアドレスを三つの部分に分割できます。

これは、コンマ区切り文字に基づいて最初の二つを分割し、残りの部分は、配列の第三の要素になります。,

以下のコードは、メッセージボックス内の三つの異なる行でアドレスを表示します：

これの実用的な用途の一つは、単一の行のアドレスをメッセージボックス 次に、（新しい行の各部分で）三つの部分に分割されたアドレスを返すカスタム関数を作成することができます。

次のコードはこれを行います。

このコードをモジュールに入れたら、他のExcel関数と同じようにブック内の関数(ThreePartAddress)を使用できます。,

この関数は、アドレスを持つセル参照を一つの引数として受け取ります。

結果のアドレスを三つの異なる行に表示するには、セルに折り返しテキスト形式を適用する必要があります（AlignmentグループのHomeタブにあります）。 ‘Wrap Text’フォーマットが有効になっていない場合、アドレス全体が単一の行として表示されます。

例5–アドレスから都市名を取得します

VBAの分割関数を使用すると、結果の配列のどの部分を使用するかを指定できます。,

たとえば、区切り文字としてカンマに基づいて次のアドレスを分割するとします。

2703 Winifred Way, Indianapolis, Indiana, 46204

結果の配列は次のようになります。

{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}

これは配列であるため、この配列の特定の部分を表示または返すことを選択できます。

以下はカスタム関数のコードで、数値を指定することができ、配列からその要素を返します。 たとえば、状態名が必要な場合は、3を指定できます（配列内の第三の要素なので）。,

上記の関数は二つの引数、アドレスとあなたが返したい要素番号を持つセル参照を取ります。 Split関数は、住所要素を分割し、それを結果変数に割り当てます。

次に、第二引数として指定した要素番号を返します。 ベースが0であるため、ElementNumber-1はアドレスの正しい部分を返すために使用されることに注意してください。

このカスタム式は、すべてのアドレスで一貫した形式を持っている場合に最も適しています。, データが一貫していない場合、目的の結果は得られません。

都市名が必要な場合は、2番目の引数として使用できます。 要素の総数よりも大きい数値を使用する場合は、#VALUEを返します！ エラー。

以下に示すように、コードをさらに単純化することができます。

上記のコードでは、結果変数を使用する代わりに、指定された要素番号のみを返します。したがって、Split(“Good Morning”)(0)がある場合、最初の要素のみが返され、これは”Good”です。,

同様に、上記のコードでは、指定された要素番号のみを返します。

あなたはまた、次のExcelのチュートリアルが好きかもしれません：

  • Excel VBA InStr関数-例で説明しました。
  • VBAを使用してExcelでデータをソートする方法（ステップバイステップガイド）。
  • 7つの驚くべきことExcel Text to Columnsあなたのために行うことができます。
  • Excelで単語数を取得する方法。
  • VBAトリム関数。